Re: Moving several databases into one database with several schemas

Em 06/09/2012 05:12, Albe Laurenz escreveu:
> Edson Richter wrote:
>> 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)?
> There is no simple way.
> You could pg_dump in plain format (-F p) and edit the SQL file,
> but that's cumbersome and error-prone.
>
> What I would try to do is restore the dump as it is in
> a new database, rename the schema, e.g.
>
> ALTER SCHEMA public RENAME TO schema1;
>
> Then pg_dump that and restore it into the destination database.
> Adjust the schema permissions as desired.
Ok, seems the way to go. No big deal, just few hours of work to the
cicle "restore in a tempdb", "rename schema", "backup schema", "restore
in consolidated".
>
>> 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...")?
> SET search_path=schema1,schema2,public;

Problem is that my application uses JDBC and Connection Pooling. After a
connection is closed, I'll have to set search path again, and again...
Nevertheless, connection pool allows me to have one command to test is
connection is available, I'll try to put SET search_path on there, and
see results. The search path for schema1 will be

SET search_path=schema1,main,public;

>
>> 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.
> That will try to restore schema "main" from the dump.
> If there is no such schema in the dump (in the original
> database), it will do nothing.

Ok, thanks for the clarification.
I'll share my experience and results after I finish this..

Regards,

Edson.

>
> Yours,
> Laurenz Albe
>
>