Re: Moving several databases into one database with several schemas

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.

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

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

Yours,
Laurenz Albe