Re: Moving several databases into one database with several schemas

Em 05/09/2012 15:30, Edson Richter escreveu:
> 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