Re: Complex database infrastructure - how to?

Em 30/06/2012 12:38, Jack Christensen escreveu:
> On 6/30/2012 9:25 AM, Edson Richter 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.
>>
>> What I've tried so far:
>> 1) Copy tables from Administrative to Application: this approach
>> would work, but I have trouble with the foreign keys. I'll have to
>> disable (or drop) them, then copy data, then activate (or recreate
>> them again). Could lead to problems?
>> 2) dblink: I can't use foreign key to foreign tables. Also, it is
>> very hard to implement with JPA.
>> 3) odbc_fdw: along with unstability, difficult to build/deploy, it is
>> too slow (why? - don't know)
>> 4) JPA spacific multi-database approach: not really working, and
>> can't provide database integrity
>>
>> My next try will be using triggers in Administrative database to send
>> data to Application databases using dblink.
>>
>> Is there any ohter way to do that? Please, adivce!
>>
>> Edson.
>>
>>
> Consider using one database with multiple schemas. You can separate
> your applications into their own schemas, and you can have
> cross-schema foreign keys.
>
Sounds interesting.

But how to keep application databases independent from each other?
I mean, if I would like to apply maintenance (backup/restore/vacumm)
without interfering with the others?

Also, there is a connection property for JDBC that allow to specify
which schema to use, so this approach is really transparent to my
application?

Thanks,

Edson.