psql invalid command \N while restore sql
SqlPostgresqlDumpSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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 - Sql
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:
- inserting a single, small table (e.g.,
pg_restore --table=orders full_database.dump > orders.dump
) - 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) - 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.
Solution 5 - Sql
You can generate your dump using INSERTS statements, with the --inserts parameter.
Solution 6 - Sql
Same thing was happened to me today. I handled issue by dumping with --inserts command.
What I do is:
-
pg_dump with inserts:
pg_dump dbname --username=usernamehere --password --no-owner --no-privileges --data-only --inserts -t 'schema."Table"' > filename.sql
-
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.
Solution 7 - Sql
Install postgresql-(your version)-postgis-scripts
Solution 8 - Sql
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.
Solution 9 - Sql
Most times, the solution is to install postgres-contrib
package.
Solution 10 - Sql
My solution was this:
psql -U your_user your_db < your.file.here.sql 2>&1|more
this way I could read the error message
I hope this helps anybody.
Solution 11 - Sql
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.
Solution 12 - Sql
I had the same problem, I created a new database and got invalid command \N
on restore with psql.
I solved it by setting the same tablespace with the old database.
For example, old database backup had tablespace "pg_default", I defined the same tablespace to the new database, and the above error has gone!
Solution 13 - Sql
I followed all these example's and they all failed with the error we are talking about:
https://stackoverflow.com/questions/3195125/copy-a-table-from-one-database-to-another-in-postgres
What worked was the syntax with -C, see here:
pg_dump -C -t tableName "postgres://$User:$Password@$Host:$Port/$DBName" | psql "postgres://$User:$Password@$Host:$Port/$DBName"
>Also if there are differing Schema's between the two, I find altering one dB's schema to match the others is necessary for Table copies to work, eg:
DROP SCHEMA public;
ALTER SCHEMA originalDBSchema RENAME TO public;
Solution 14 - Sql
For me it was the ENCODING and LOCALE that differ from the source database. Once I dropped the target DB and recreated it it was working fine.
Solution 15 - Sql
In my case the problem was a lack of disk space on my target machine. Simply increasing the local storage fixed it for me.
Hope this helps someone ;)
Solution 16 - Sql
Adding my resolution, incase it helps anyone. I installed postgis but the error wasn't resolved. The --inserts option was not feasible as I had to copy a big schema having tables with thousands of rows. For the same database I didn't see this issue when pg_dump and psql (restore) were run on mac. But the issue came when pg_dump was run on linux machine, the dump file copied to mac and tried for restore. So I opened the dump file in VSCode. It detected unusual line terminators and gave option to remove them. After doing that the dump file restore ran without the invalid command \N errors.