I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export
pg_dump -U username --format plain --section data --inserts --file "t1.backup" --table “table1” --table “table2” --table "addresses" “mydb”
But when I import the data using pg_restore, I get errors
pg_restore -d my_production --data-only t1.backup
Command was: INSERT INTO table2 VALUES (NULL, NULL, 1762000, '2016-12-10 05:07:41.857082', '2016-12-10 05:07:41.857207', 'Brendon Sch... pg_restore: [archiver (db)] could not execute query: ERROR: invalid input syntax for integer: "a6a40213-dcd8-4312-9ff1-ae6c23b31ade" LINE 1: ..., '8a22784f-91aa-4b1e-a3d4-ec44f5de19ff', 55, 59, 'a6a40213-...
I think this is because although table2 has the same columns on both my Mac and Linux machines, they are in different orders. How do I force INSERT statement to include the column names or barring that, how do I export/import my data successfully? Note, I don’t want to delete the data on the remote machine, just add to it.