A lot of "invalid command \N" when I try to restore PostgreSQL dump

Question

I made a backup of a database on my mac and tried to restore it on a computer with ubuntu. When I execute

psql -U uname -d dbname -f ~/dump_from_mac

I have a lot of error messages like "invalid command \N" and "relation 'SomeTable' does not exist". My question is very similar with Can't copy table to another database with pg_dump but I don't know how to fix my dump file. I wipe my mac and can't make new dump.

Use `psql -v ON_ERROR_STOP=1` as suggested in: http://stackoverflow.com/a/20428547/238814 — Daniel Vérité, Aug 03 '15 at 18:00
Possible duplicate: http://stackoverflow.com/questions/20427689/psql-invalid-command-n-while-restore-sql — lucrussell, Apr 14 '16 at 16:13

Andrey · Accepted Answer · 2017-05-21T03:48:37.650

8

My problem was solved by setting postgresql-contrib package

sudo apt-get install postgresql-contrib

and creating extension uuid-ossp in my db

CREATE EXTENSION "uuid-ossp";

My db haven't this extension by default and psql could not execute uuid_generate_v1() function from my dump file. In most cases install postgresql-contrib is enough, but sometimes problem may be in some missed extensions too.

edited May 21 '17 at 03:48

answered Aug 03 '15 at 20:30

Andrey

190
1
1
10

1

I had same problem. Since my backup had some extensions, solved installing postgresql-contrib – Afonso Lage May 15 '16 at 22:39
Just running `apt-get install postgresql-contrib` solved the problem for me after loading a database dumped with `pg_dumpall -c -f out`. Thanks! – Linus Oleander May 21 '17 at 01:34
This was very annoying. Thank you, kind soul. – jason m Jun 19 '20 at 21:03
For all those working with containers like Docker, using `apk --update add postgresql-contrib` worked for me. – batero Apr 07 '22 at 14:57

score 0 · Answer 2 · answered Jul 25 '18 at 11:25

Here is my solution:

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.

A lot of "invalid command \N" when I try to restore PostgreSQL dump

2 Answers2