psql invalid command \N while restore sql

Question

Postgres uses \N as substitute symbol for NULL value. But all psql commands start with a backslash \ symbol. You can get these messages, when a copy statement fails, but the loading of dump continues. This message is a false alarm. You have to search all lines prior to this error if you want to see the real reason why COPY statement failed.

Is possible to switch psql to "stop on first error" mode and to find error:

psql -v ON_ERROR_STOP=1

I received the same error message when trying to restore from a binary pg_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

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

I have run into this error in the past as well. Pavel is correct, it is usually a sign that something in the script created by pg_restore is failing. Because of all the "/N" errors, you aren't seeing the real problem at the very top of the output. I suggest:

1. inserting a single, small table (e.g., pg_restore --table=orders full_database.dump > orders.dump )
2. if you don't have a small one, then delete a bunch of records out of the restore script - I just made sure the ./ was the last row to be loaded (e.g., open orders.dump and delete a bunch of records)
3. watch the standard output, and once you find the problem, you can always drop the table and reload

In my case, I didn't have the "hstore" extension installed yet, so the script was failing at the very top. I installed hstore on the destination database, and I was back in business.

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

Same thing was happened to me today. I handled issue by dumping with --inserts command.

What I do is:

1) pg_dump with inserts:

pg_dump dbname --username=usernamehere --password --no-owner --no-privileges --data-only --inserts -t 'schema."Table"' > filename.sql

2) psql (restore your dumped file)

psql "dbname=dbnamehere options=--search_path=schemaname" --host hostnamehere --username=usernamehere -f filename.sql >& outputfile.txt

Note-1 ) Make sure that adding outputfile will increase speed of import.

Note-2 ) Do not forget to create table with exact same name and columns before importing with psql.