Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

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

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

2 Answers

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
@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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.