Reputation: 3505

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.

Upvotes: 244

Answers (21)

Pencilcheck

Reputation: 2922

Turns out the problem was incompatible column names, I simply drop the table, and rerun the command and it works

Upvotes: 0

Aurthur Musendame

Reputation: 1

For me what worked was giving permissions to schema public after creating the database before running the restore.

Latest versions of postgres require permissions to be explicity given

create database mydb;
\c myqb;
grant all on schema public to <user>

The run the restore

Upvotes: 0

dorinand

Reputation: 1717

I encountered the same issue and found two solutions:

Solution #1

# Execute pg_dumpall only on globals only
pg_dumpall --globals-only > globals.bak

The --globals-only option will dump only global objects, no databases.

# Execute pg_dump to backup database mydb with compression Z7
pg_dump -Z7 -Fc --dbname=mydb -f db.bak

Compression was choosen based on Using compression with PostgreSQL’s pg_dump.

Solution #2

Instead of COPY, which is default used for output of pg_dumpall, you can define --inserts - dump data as INSERT commands, rather than COPY.

pg_dumpall --inserts > db.bak

This works fine but keep in mind that INSERT is slower than COPY. More information can be found in the docs:

Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safer, though even slower.

There is also an interesting article, Speed up your PostgreSQL bulk inserts with COPY where demonstrates that COPY is 3.5-4 times faster than INSERT and provides a good source of information.

I prefer to have one backup file (option #2) and a slow restore does not matter to me because my database have only few GBs. However, in the case of large databases, option #1 makes more sense..

I hope this helps somebody.

PostgreSQL version 16.2.

Upvotes: 0

Robert N

Reputation: 1174

I was encountering this error on Windows after making a backup and then immediately attempting to restore it. Turned out the issue was, I wrote the file out using pg_dump ... > filename, which apparently corrupts the output. Instead, I needed to write the file out using pg_dump ... -f filename. Once I had a backup file I'd created that way, it restored without incident.

Upvotes: 1

Stephan K.

Reputation: 15702

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

Upvotes: 48

Alisherbek

Reputation: 23

check that the columns in the table and the columns in the backup file suitable

Upvotes: -3

SamratK

Reputation: 21

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.

Upvotes: 2

Pavel Stehule

Reputation: 45770

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

Upvotes: 377

Cedric M

Reputation: 11

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 ;)

Upvotes: 1

user300778

Reputation: 31

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.

Upvotes: 2

Felipe Valdes

Reputation: 2217

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.

Upvotes: 4

Jeremy Thompson

Reputation: 65544

I followed all these example's and they all failed with the error we are talking about:

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;

Upvotes: 0

John Makridis

Reputation: 21

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!

Upvotes: 1

Ekrem Gurdal

Reputation: 1248

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.

Upvotes: 5

mountainclimber11

Reputation: 1400

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.

Upvotes: 0

Farshid Ashouri

Reputation: 17691

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

Upvotes: 2

Geets

Reputation: 41

Install postgresql-(your version)-postgis-scripts

Upvotes: 4

So4ne

Reputation: 1182

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

Upvotes: 35

João Neves Filho

Reputation: 619

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

Upvotes: 10

cognalog

Reputation: 440

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.

Upvotes: 2

oraserrata

Reputation: 389

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.

Upvotes: 8

psql invalid command \N while restore sql

Answers (21)

Related Questions