Thread: Complex database infrastructure - how to?

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.

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.

--
Jack Christensen
http://jackchristensen.com/

Hi Edson,

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.

Hi Edson,

Maybe the best option is what Jack has already suggested, to have all in 1 DB in separate schemas (if tables are not already organised in separated schemas...)... But I am not sure I understand well problems:

2012/6/30 Edson Richter <edsonrichter@hotmail.com>

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.

1)  I guess data just need to be coppied in proper order... though, Postgres have deffered constraint feature, what means, FK will not break transaction until end (all trans commited)  - untill all data copied...

2) I just wonder On what way it is possible in MS SQL Server or any other db engine? (to have FK to foreign table... ) 

Thanks,

Misa

I'm inclined to solution (1). Setup proper triggers to copy data into all related databases would be easier (and have better performance).

Working with multiple schemas seems that I'll have lot of changes either in applications and maintenance routines.

Also, I'll have different databases replicated to different servers, according to some criteria. It would be impossible to set with schemas, right?

About solution (2), it's my mistake: MS SQL does not support it. MySQL does. Oracle allows to create foreign keys by using materialized views (that, for instance, can be cross database). MS SQL and DB2 only allow cross-database queries (that, perhaphs, I really don't understand why is not supported in PgSQL).

Regarding why support it? Multi-tenant systems are the first though. If you have separate databases because security issues (like HR database, that must be completely separated - not only database, but also physical server - from other applications due security constraints). But one table or view with correct clearance would be acceptable...

Thanks,

Edson.

On 2012-06-30, Edson Richter <edsonrichter@hotmail.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.

--
⚂⚃ 100% natural

On 2012-06-30, Edson Richter <edsonrichter@hotmail.com> wrote:

>> Consider using one database with multiple schemas. You can separate
>> your applications into their own schemas, and you can have
>> cross-schema foreign keys.
>>

> But how to keep application databases independent from each other?

different schemas.

> I mean, if I would like to apply maintenance (backup/restore/vacumm)
> without interfering with the others?

pg_dump (the backup tool) can be restricted in scope by schema.
so you can take separate backups, you can drop an entire schmas
contents with the DROP SCHEMA .... CASCADE command etc.

> 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?

The username parameter either implicitly (using the default setting) or by previously issuing
ALTER USER "someone" SET SEARCH_PATH TO "something" ;

--
⚂⚃ 100% natural

Em 01/07/2012 04:50, Jasen Betts escreveu:
> On 2012-06-30, Edson Richter <edsonrichter@hotmail.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.