Search code examples
sqlpostgresqlpsqlpg-dump

Syntax Errors When Importing pg_dump sql file

I am trying to restore an SQL file I created from a pg_dump however trying to load the dump into a new host and I am getting many syntax errors as seen below:

 psql:subscription_dump.sql:3741: ERROR:  syntax error at or near "We"
 LINE 1: We need to see if there's anything else needing
 doing. ...
         ^

What it is showing is some of the data from the database. Why is data present in the database causing syntax errors when importing?

psql "service=subscription_odyssey" -f subscription_dump.sql

After advice on #postgressql I changed my command to the following:

psql "service=subscription_odyssey" -f subscription_dump.sql -v ON_ERROR_STOP=1

Here is the first reported error now:

psql "service=subscription_odyssey" -f subscription_dump.sql -v ON_ERROR_STOP=1
SET
psql:subscription_dump.sql:6: ERROR:  unrecognized configuration parameter "lock_timeout"
Solution

  • You're trying to restore a dump from 9.3 or 9.4beta (since 9.3 is when lock_timeout was introduced) to an older version, probably 9.0 or older - before the change to standard_conforming_strings. So it'll be trying to intepret backslashes in strings as escapes, escaping a quote somewhere, and getting lost trying to parse strings as SQL.

    You might have more luck with a custom-format dump (pg_dump -Fc), but your best bet will be to make a new dump of the 9.3 server using the pg_dump from your old version. If you don't have access to the 9.3 server anymore, install 9.3 temporarily to restore the dump to it.