psql invalid command \\N while restore sql

问题:

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.

回答1:

Postgres uses "\N" as substitute symbol for NULL value. But all psql commands starts by backslash "\" symbol. So you can get this messages, when probably copy statement fails, but a loading of dump continues. This message is only false alarm. You have to search a lines before for reason why COPY statement fails.

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

psql -v ON_ERROR_STOP=1

回答2:

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

回答3:

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.

回答4:

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

回答5:

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

回答6:

Install postgresql-(your version)-postgis-scripts

回答7:

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.

回答8:

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.

回答9:

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

回答10:

For me using postgreSQL 10 on SUSE 12, I resolved the invalid command \N error by increasing disk space. Lack of disk space was causing the error for me. You can tell if you are out of disk space if you look at the file system your data is going to in the df -h output. If file system/mount is at 100% used, after doing something like psql -f db.out postgres (see https://www.postgresql.org/docs/current/static/app-pg-dumpall.html) you likely need to increase the disk space available.