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
$ 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.
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?