0

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux.

The command for the backup was:

$ pg_dump -t table1 -t table2 -t table3 -t table4 dbname > backup.sql

Restore command:

$ psql new_dbname --set ON_ERROR_STOP=1 < backup.sql

The restore would start, but then I get the following error:

ERROR: extra data after last expected column

Tables are created, but there is no data.

After a bit of searching the error normally seems to pop up when trying to import CSV or other text files, but this is obviously not the case here.

Just to add some confusion, the restore works perfectly if I split the backup into 2 separate SQL files. One of the tables is much larger, so I did that one on it's own and the other 3 into a single SQL file.

Further info:

From Daniel's suggestion I have taken a look at the line where the error is occurring. There are 2 rows where the data looks bad. The first row is completely missing data in 3 fields. The other row has one field where the data does not appear as it should (almost as if the data has partly been taken from a different field). Going back to the original database, these problems are not there, the data is fine.

What could be causing this problem in the SQL file?

  • Can you try -f backup.sql instead of < backup.sql to get the line number of the error, and inspect the backup around that line? – Daniel Vérité Apr 28 '16 at 16:19
  • Thanks for the tip @Daniel. I've had a look at the data around the line where the error is coming from, and yes there are a couple of lines that look wrong. I'll edit my question to include what I've found... – Matt Apr 29 '16 at 9:20
0

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors.

You would think that Postgres would have in-built checking to ensure integrity of exported data!

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.