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.

  • 1
    A plain text dump is a SQL script. You can't use pg_restore for that. You need to "run" the script using psql – a_horse_with_no_name Dec 28 '16 at 16:24
  • 1
    Although @a_horse_with_no_name is already pointing the reason why pg_restore isn't working... in case you actually want 'column inserts', you can do it by means of --column-inserts – joanolo Dec 28 '16 at 20:29
  • Yup the "--column-inserts" directive was exactly what I was looking for. – Dave Dec 28 '16 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.