psql invalid command \N while restore sql

R

18

233

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.

Rapid answered 6/12, 2013 at 15:30 Comment(0)

R

361

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

Repugnance answered 6/12, 2013 at 16:13 Comment(10)

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. – Sherasherar 16/12, 2013 at 5:40

It is quite evil from PostgreSQL to give such a misleading warning, your answer saved me a lot of time! – Rookie 21/7, 2014 at 20:35

@Rookie - yes, it is not friendly - you can run psql in "stop on first error" mode. It simplify diagnostics "psql -v ON_ERROR_STOP=1" – Repugnance 22/7, 2014 at 6:23

Can happen when e.g. create table... fails in the start, but loading continues. – Herwick 11/1, 2017 at 11:34

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. – Colorific 4/4, 2017 at 19:26

I came here because of the same error. What I figured out was to do: (pg_restore ... | psql ...) 2>&1 | less – Ignaz 19/5, 2017 at 21:55

@PavelStehule You should add your comment as part of the answer as it's very pertinent & difficult to bypass these errors. – Bullivant 24/7, 2017 at 20:1

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? – Terreverte 27/11, 2021 at 13:58

@MarcusJuniusBrutus - yes - there have to be some reason why COPY command fails – Repugnance 27/11, 2021 at 15:21

Make sure that your PostgreSQL databases are compatible versions -- you may have exported data from a later version of PostgreSQL that has more functionality than the database you are trying to import it into. – Locket 25/7, 2022 at 17:43

C

48

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

Coltoncoltsfoot answered 10/7, 2014 at 15:55 Comment(2)

also much lower cpu usage, isn't it? – Callum 2/5, 2019 at 20:56

The option -F t solve my problem. – Theodore 28/3, 2023 at 1:24

Z

32

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

Zilber answered 10/2, 2017 at 14:15 Comment(0)

H

10

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

Hereunder answered 30/5, 2016 at 17:46 Comment(1)

This works for me! pg_dump --inserts $DATABASE > $FILENAME – Muldon 5/10, 2016 at 1:34

D

8

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.

Dividers answered 19/3, 2014 at 19:13 Comment(0)

D

5

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.

Dissimilar answered 25/7, 2018 at 11:22 Comment(0)

N

4

Install postgresql-(your version)-postgis-scripts

Newmown answered 27/5, 2017 at 7:21 Comment(0)

N

4

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.

Note answered 2/11, 2020 at 15:33 Comment(0)

T

2

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.

Televisor answered 17/2, 2016 at 20:45 Comment(0)

T

2

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

Tripterous answered 8/10, 2017 at 18:55 Comment(0)

E

2

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.

Energetic answered 11/11, 2020 at 10:35 Comment(0)

H

2

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.

Hyaluronidase answered 14/9, 2021 at 14:39 Comment(1)

Which line terminator are you speaking of? – Wellordered 18/3, 2023 at 2:6

U

1

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!

Uniat answered 23/10, 2019 at 11:14 Comment(0)

M

1

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

Mintun answered 19/8, 2021 at 5:41 Comment(0)

A

1

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.

Achene answered 28/2, 2023 at 18:45 Comment(0)

T

0

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.

Tracay answered 28/12, 2017 at 21:3 Comment(0)

S

0

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;

Recommended topics

Hot tags