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?

Radek Postołowicz · Accepted Answer · 2016-12-28 18:41:01Z

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

edited Dec 28, 2016 at 18:41

answered Dec 28, 2016 at 13:36

Radek Postołowicz

4,8742 gold badges32 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Over a year ago

Is there any way to make the INSERT command contain the columns as well as the values? The two tables must have their columns in different orders because I'm getting "Command was: INSERT INTO table2 VALUES (NULL, NULL, 1759000, '2016-12-10 05:07:41.857082', '2016-12-10 05:07:41.857207', 'Jenny Sosh'... pg_restore: [archiver (db)] could not execute query: ERROR: invalid input syntax for integer: "a6a40213-dcd8-4312-9ff1-ae6c23b31ade" LINE 1: ..., '7b1df57a-1fc3-4b89-a22b-6ecd33afbe63', 50, 54, 'a6a40213-..." errors despite the fact I've verified they contain the same columns.

Radek Postołowicz Over a year ago

Use --column-inserts (see postgresql.org/docs/current/static/app-pgdump.html for more info)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related