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

database postgresql pgadmin
Norbertas Brazaitis picture Norbertas Brazaitis · Sep 14, 2016 · Viewed 16.4k times · Source

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

aleroot picture aleroot · Sep 14, 2016

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.

Related questions

Export Postgresql table data using pgAdmin

I am using pgAdmin version 1.14.3. PostgreSQL database version is 9.1. I got all Db script for table creation but unable to export all data inside tables. Could not find any option to export data in db script form.

Read More
PostgreSQL how to see which queries have run

I have a PostgreSQL DB at my computer and I have an application that runs queries on it. How can I see which queries has run on my DB? I use a Linux computer and pgadmin.

Read More
How to visualize database tables in postgresql using pgAdmin?

I am trying to visualize tables and their relations using pgAdmin. I have understood that there is a query visualizer tool available for pgAdmin. However, that only is useful if you are dealing with queries. My main goal is to …

Read More