Re: Complex database infrastructure - how to?

On 2012-06-30, Edson Richter <edsonrichter(at)hotmail(dot)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