Postgresql – Merging multiple databases as separate schemas in PostgreSQL

postgresql

I have to combine data from 4 different databases. Each having its own design.

What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database

Then export tables from all other databases and import this data into a unique corresponding schemas schema inside my final database.

How do i export table data from Postgresql Databases.

After having one database with multiple schemas i will use cross schema queries to read and write data to target schema.

Thanks in anticipation.

Best Answer

I would assume that the other databases are Postgres as well? And all of them have only one schema? If you only have to do it once, then the easiest way is to use pg_dump -Fc to export the database and then edit the resulting SQL file by some regexp script.

In detail, you will have a 

SET search_path = public;

row near the top of the file. Rewrite 'public' to the desired schema name. Other places to check for are where a stored procedure returns a user-defined type, view or table rowtype, here you will see something similar to

CREATE OR REPLACE FUNCTION f()
RETURNS public.my_view AS
...

Rewrite 'public' in such places as well.

Related Question