PostgreSQL: How to copy data from one database table to another database

Question

I need simple example how to copy data from database DB1 table T1 to database DB2 table T2.

T2 has identical structure like T1 (same column names, properties. Just different data) DB2 running on same server like DB1, but on different port.

Answer 1

In the case the two databases are on two different server instances, you could export in CSV from db1 and then import the data in db2 :

COPY (SELECT * FROM t1) TO '/home/export.csv';

and then load back into db2 :

COPY t2 FROM '/home/export.csv';

Again, the two tables on the two different database instances must have the same structure.

Using the command line tools : pg_dump and psql , you could do even in this way :

pg_dump -U postgres -t t1 db1 | psql -U postgres -d db2

You can specify command line arguments to both pg_dump and psql to specify the address and/or port of the server .

Another option would be to use an external tool like : openDBcopy, to perform the migration/copy of the table.

Answer 2

You can try this one -

 pg_dump -t table_name_to_copy source_db | psql target_db