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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
