Comments for Copying Rows Between PostgreSQL Databases

[phinjensen]

Comments for https://www.endpointdev.com/blog/2013/11/copying-rows-between-postgresql/ By Szymon Lipiński

To enter a comment:

1. Log in to GitHub
2. Leave a comment on this issue.

[phinjensen]

original author: Brad Wallace
date: 2013-11-21T11:04:21-05:00

Excellent, a very useful. I was using a file intermediate, much less elegant than this.

[phinjensen]

original author: Ivan Minčík
date: 2013-11-22T05:18:06-05:00

I recommend You to look at pg_comparator

http://pg-comparator.projects.pgfoundry.org/pg_comparator.html

[phinjensen]

original author: Ryan M. Ferris
date: 2014-09-09T22:25:27-04:00

Thanks for this. I made this work from one database to another on Windows 7. I needed the -d 'databasename' argument after all the remote args. The trick was to first recreate the table with the exact schema from the first database on the second.

psql -h localhost -p port@ -U postgres -d WC2014_08_01_2014 -c "\copy (SELECT precincts42 FROM ld42) TO STDOUT" | psql -h localhost -p port@ -U postgres -d WC2014_09_05_2014 -c "\copy ld42 FROM STDIN"

[phinjensen]

original author: krishna mohan
date: 2014-12-10T13:48:12-05:00

I succeeded in copying from one table to other in same database. I am trying to copy from database in one machine to other and getting permission denied error. Please advise.

[phinjensen]

original author: John Kaplan
date: 2015-02-09T12:53:32-05:00

Note the copy command will fail if the source/destination database tables have different columns or columns in different orders. You can fix this by putting the column names into the copy command in parentheses.

psql source_db -c "\copy source_table (column_name1, column_name2) from in.txt"

To automatically get the column names in order from the source database, do a pg_dump -t of the table, and copy/paste the column names out of the embedded COPY command output.