0

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • I think editing isn't necessary. To dump schemas from databases use pg_dump with -n option and restore them into desired database. If you want large objects to be included in the dump take a look at -b option.
    – Wojtas
    Commented Dec 12, 2012 at 23:36
  • The most likely scenario is that the 4 separate databases all make use of the public schema, and so you will need to modify the schema names before loading them into the new database. If by chance they all use unique schema names inside the source databases, then your right, no edit will be neccessary. But it seems likely the edit would be needed.
    – xzilla
    Commented Feb 23, 2017 at 23:23
-1

To export/import data you can use COPY FROM / COPY TO. Details here.

Or you can use dblink to query data in other database. Details here.

Improve this answer
1
  • @martin, @Wojtas Hi,Many thanks for your responses. Yes all the databases are on the same PostgreSQL server, and also have only one schema. I did the following steps. 1. pg_dump -h 192.168.64.183 -p 5432 -U user -i -F t -v -f "/home/user/DB1.public" -n 'public' DB1 2. pg_restore -d DBTARGET -F t -L /home/user/DB1.public -i -n publicee2 -S user -X disable-triggers Are these the correct steps?
    – Junaid Malik
    Commented Dec 13, 2012 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.