CODEWITHSUNDEEP
databasepostgresqlpgadmin
Norbertas Brazaitis
Norbertas Brazaitis

Reputation: 195

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

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.

Upvotes: 14

Views: 28458

Answers (2)

Mohammad Aarif
Mohammad Aarif

Reputation: 1705

You can try this one - 

 pg_dump -t table_name_to_copy source_db | psql target_db

Upvotes: 6

aleroot
aleroot

Reputation: 72616

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.

Upvotes: 12

Related Questions