Copy a table from one database to another in Postgres
You can also use the backup functionality in pgAdmin II. Just follow these steps:
- In pgAdmin, right click the table you want to move, select "Backup"
- Pick the directory for the output file and set Format to "plain"
- Click the "Dump Options #1" tab, check "Only data" or "only Schema" (depending on what you are doing)
- Under the Queries section, click "Use Column Inserts" and "User Insert Commands".
- Click the "Backup" button. This outputs to a .backup file
- Open this new file using notepad. You will see the insert scripts needed for the table/data. Copy and paste these into the new database sql page in pgAdmin. Run as pgScript - Query->Execute as pgScript F6
Works well and can do multiple tables at a time.
Using dblink would be more convenient!
truncate table tableA;
insert into tableA
select *
from dblink('hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres',
'select a,b from tableA')
as t1(a text,b text);
Extract the table and pipe it directly to the target database:
pg_dump -t table_to_copy source_db | psql target_db
Note: If the other database already has the table set up, you should use the -a
flag to import data only, else you may see weird errors like "Out of memory":
pg_dump -a -t table_to_copy source_db | psql target_db