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.

migrated from stackoverflow.com Dec 16 '12 at 13:43

This question came from our site for professional and enthusiast programmers.

1

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.

  • 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 Dec 12 '12 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 Feb 23 '17 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.

  • @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 Dec 13 '12 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy