Re: Complex database infrastructure - how to?

Em 01/07/2012 04:50, Jasen Betts escreveu:
> On 2012-06-30, Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
>> I've a plan that will need a complex database infra-structure using
>> PostgreSQL 9.1.
>> I've seen similar setups using MS SQL Server and other databases, but
>> all of them support cross database queries (also easy to implement with
>> materialized views).
>>
>> - Administrative database: have few tables, used to administer the
>> infrastructure. This database have some tables like "users", "groups",
>> "permissions", etc.
>> - Application databases: have app specific data.
>>
>> 1) One main Administrative application that will have read/write
>> permissions over the Administrative database.
>> 2) Each application will have to access the application database (for
>> read/write), and the administrative database (for read only - mainly to
>> maintain the record references to the users that created objects, and so
>> on).
>> 3) All applications are written in Java, using JPA for persistence.
>> 4) All databases are running on same server, and all of them have same
>> encoding.
>> Is there any ohter way to do that? Please, adivce!
> Schemas,
> give each application a different username and a matching schema name
> with matching ownership the default postgres schema search path will
> cause tables created by each application user accout to be segregated
> into the schema with no extra work.
>
> "Admin" schema tables can be accessed as eg: admin.tablename
> or if you put them into the schema "public" or modfy the search path
> just by tablename.
>
>
> http://www.postgresql.org/docs/9.1/static/ddl-schemas.html
> AIUI postgresql schems are almost the same thing as mysql databases.
>
Thanks.

I'll take a look.

Regards,

Edson.