How do I export some data my local Postgres db and import it to a remote without deleting all the data from the remote?

Question

I’m using Postgres 9.5 on Mac Sierra. I want to export some table data from my local machine and import that into a Postgres 9.5 database on a Linux machine. Note I don’t want to blow away the data on the Linux machine, only add the my local machine table rows to the rows that already exist on the tables in the Linux environment (and ignore duplicates). So on my local machine, I ran

pg_dump -U myusername --format custom --section data --inserts --file "t1.backup" --table "table1" --table "table2" --table "addresses" "mydb"

However on my remote machine, when I try and import the file, I get the error

myuser@remote-machine:/tmp$ psql db_production < t1.backup
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

But I don’t want to use pg_restore because I don’t want to erase the existing table data, I simply want to add to it. How can I achieve this?

Answer 1

Use --format plain instead of custom one. The latter is designed to work exclusively with pg_restore. Plain format also allows you to take a look into dumped data with text editor and verify if that's what you want.

However my quick test shows that it's also possible to append data with pg_restore and custom format data-only dump:

pg_restore -d db_production --data-only t1.backup