Copying data from a restored postgres database to the live database

The correct way to copy data is not to extract to a CSV and import with some tool, to manually create INSERT queries, or to try filtering the pg_dump output. You should use the COPY function included in postgres, as such:

psql -c "COPY (SELECT * FROM my_table WHERE created_at > '2012-05-01') TO STDOUT;" source_db | psql -c "COPY my_table FROM STDIN;" target_db

Add psql parameters as necessary.

This does not replace the target table, it simply inserts what you’ve selected. If you want to replace the target, you need to TRUNCATE it first.

Leave a Reply

Your email address will not be published.