Copy a table from one database to another in Postg

Question

I am trying to copy an entire table from one database to another in Postgres. Any suggestions?

Answer 1

Same as answers by user5542464 and Piyush S. Wanare but split in two steps:

pg_dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase > dump
cat dump | psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase

otherwise the pipe asks the two passwords in the same time.

Answer 2

To move a table from database A to database B at your local setup, use the following command:

pg_dump -h localhost -U owner-name -p 5432 -C -t table-name database1 | psql -U owner-name -h localhost -p 5432 database2

Answer 3

You have to use DbLink to copy one table data into another table at different database. You have to install and configure DbLink extension to execute cross database query.

I have already created detailed post on this topic. Please visit this link

Answer 4

As an alternative, you could also expose your remote tables as local tables using the foreign data wrapper extension. You can then insert into your tables by selecting from the tables in the remote database. The only downside is that it isn't very fast.

Answer 5

If you run pgAdmin (Backup: pg_dump, Restore: pg_restore) from Windows it will try to output the file by default to c:\Windows\System32 and that's why you will get Permission/Access denied error and not because the user postgres is not elevated enough. Run pgAdmin as Administrator or just choose a location for the output other than system folders of Windows.

Answer 6

Check this python script

python db_copy_table.py "host=192.168.1.1 port=5432 user=admin password=admin dbname=mydb" "host=localhost port=5432 user=admin password=admin dbname=mydb" alarmrules -w "WHERE id=19" -v
Source number of rows = 2
INSERT INTO alarmrules (id,login,notifybyemail,notifybysms) VALUES (19,'mister1',true,false);
INSERT INTO alarmrules (id,login,notifybyemail,notifybysms) VALUES (19,'mister2',true,false);