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.

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions

Comments

[crowmagnumb]

Yes, a very, very easy mistake to make as the number of these invalid command errors can be extremely large completely obscuring the first error hit early on.

[Tregoreg]

It is quite evil from PostgreSQL to give such a misleading warning, your answer saved me a lot of time!

[Pavel Stehule]

@Tregoreg - yes, it is not friendly - you can run psql in "stop on first error" mode. It simplify diagnostics "psql -v ON_ERROR_STOP=1"

[Abel]

This works for me! pg_dump --inserts $DATABASE > $FILENAME

[JaakL]

Can happen when e.g. create table... fails in the start, but loading continues.

[Barrett Clark]

Changing the \N character to \\N (with the backslash escaped) works for importing text-based fields. \N is the required format for NULL for integers and date fields. At least this is the experience I'm having with the file I am currently importing using Postgres 9.6.

[THK]

I came here because of the same error. What I figured out was to do: (pg_restore ... | psql ...) 2>&1 | less

[Le Droid]

@PavelStehule You should add your comment as part of the answer as it's very pertinent & difficult to bypass these errors.

[catbadger]

also much lower cpu usage, isn't it?

[iRhonin]

"I didn't have the "hstore" extension installed yet", TNX.

[matmat]

What a life saver!

[Marcus Junius Brutus]

Just to be perfectly clear: I understand this is not a "false alarm" but rather a "misleading error message". "false alarm" means nothing bad happened and there's nothing to investigate or to fix which, I think, is NOT the case when you see this message. Do I have this right?

[Pavel Stehule]

@MarcusJuniusBrutus - yes - there have to be some reason why COPY command fails