psql invalid command \N while restore sql

Question

I'm trying to restore my dump file, but it caused an error:

psql:psit.sql:27485: invalid command \N

Is there a solution? I searched, but I didn't get a clear answer.

Answer 1

I go the same error message when trying to restore from a binary dump. I simply used pg_restore to restore my dump and completely avoid the \N errors, e.g.

pg_restore -c -F t -f your.backup.tar

Explanation of switches:

-f, --file=FILENAME output file name -F, --format=c|d|t backup file format (should be automatic) -c, --clean clean (drop) database objects before recreating

Answer 2

In my recent experience, it's possible to get this error when the real problem has nothing to do with escape characters or newlines. In my case, I had created a dump from database A with
pg_dump -a -t table_name > dump.sql
and was trying to restore it to database B with
psql < dump.sql (after updating the proper env vars, of course)
What I finally figured out was that the dump, though it was data-only (the -a option, so that the table structure isn't explicitly part of the dump), was schema-specific. That meant that without manually modifying the dump, I couldn't use a dump generated from schema1.table_name to populate schema2.table_name. Manually modifying the dump was easy, the schema is specified in the first 15 lines or so.

Answer 3

Most times, the solution is to install postgres-contrib package.

Answer 4

You can generate your dump using INSERTS statements, with the --inserts parameter.

Answer 5

Install postgresql-(your version)-postgis-scripts

Answer 6

I know this is an old post but I came across another solution : postgis wasn't installed on my new version, which caused me the same error on pg_dump