Re: Moving several databases into one database with several schemas

Dear list,

Scenario:

I'm using PostgreSQL 9.1 on Linux x64 running over CentOS 5. Everything is fine, but now I do have 4 separate databases running on different servers, and every server has some shared tables.

I've been working on a complex logic that is able to "replicate" these tables in a way that is almost impossible to use existing solutions (I've to merge records when replicating). My conclusion is that the logic is just too complex to maintain, and I would like to consolidate databases but keep data separate. Few days ago, I've posted this query in this list, and got the orientation to try using schemas.

That's what I want to do know: I would like to consolidate these 4 separate databases in 1 database with 5 schemas:

- Main schema: will have all shared tables, that will be read only most of time;
- Schema1 to Schema4: will have their own tables, read write.

Now the questions:

1) Is there a way to "backup" database1 and "restore" in the consolidated database, but in "schema1" (not overwriting everything)?
2) Is there a way to specify the default schema in JDBC url (or command I can issue to change the default schema at runtime, like "set path...")?

Thanks in advance,

Edson Richter

I've tried following command (on Windows platform), but command returns without any import, and "exit code 0" (output translated, because I do use PT-BR):

---------------------------------------------------------------------------------------------------------------------------------------------
pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "consolidado" --role "MyUser" --no-password  --schema main --verbose "E:\backups\maindatabase.bk"
pg_restore: connecting to database for restore

Process returned exit code 0.
---------------------------------------------------------------------------------------------------------------------------------------------

I'm sure database is running, backup file exists, everything seems to be fine - except that nothing is imported.
I could not find directions in documentation. I suspect that I'll not be able to use Custom format for backups...

Please, help!

Edson