psql prints invalid command \N many times during a restore
invalid command \NShort answer
The \N lines are data rows, not commands. A COPY ... FROM STDIN statement earlier in the dump failed, so psql read its rows as if they were commands. Scroll up to the first real error, fix it (missing role, missing database, wrong tool), and rerun with ON_ERROR_STOP set.
Fix it fast
1Find out which cause you have
Rerun the restore so that it stops at the first failure. The error that ends the run is the real problem; the \N lines were only the fallout.
createdb -T template0 mydb psql -X --set ON_ERROR_STOP=on mydb < dump.sql2Match what you see, then jump to its fix
- 1
ERROR: role "someone" does not exist, shortly before the first invalid command lineA role named in the dump does not existFix - 2
The restore starts and the first CREATE or COPY statement fails.The target database was not created from template0, or does not existFix - 3
The file is a non-text dump (not readable SQL), and psql fails on the first lines.The file is not a plain-SQL dumpFix
- 1
What does the error look like?
invalid command \N
Cause 1 of 3
A role named in the dump does not exist
1Check
A plain dump records who owns each object. The manual says every user who owns objects or holds permissions in the dumped database must exist before the restore, or the objects are not recreated with the original ownership.
psql -X -c '\du'2Fix
Create the missing roles first with CREATE ROLE, then restore again into a fresh database.
dropdb mydb
createdb -T template0 mydb
psql -X --set ON_ERROR_STOP=on mydb < dump.sql3Verify
The restore runs to the end without stopping, and the exit status is 0.
echo $?Confirmed by postgresql.org
Cause 2 of 3
The target database was not created from template0, or does not exist
1Check
The dump does not create the database. The manual says to create it from template0 before running psql, so objects and extensions are not mixed with whatever template1 contains.
psql -X -l2Fix
Create an empty database from template0 and restore into it.
createdb -T template0 mydb3Verify
psql connects to the new database and the restore passes the first COPY.
psql -X -d mydb -c '\dt'Confirmed by postgresql.org
Cause 3 of 3
The file is not a plain-SQL dump
1Check
Non-text dumps are restored with pg_restore, not psql. Plain files open with readable SQL; custom-format files do not.
head -c 200 dump.sql2Fix
Restore it with pg_restore into an empty database.
pg_restore -d mydb dump.sql3Verify
pg_restore finishes without errors.
psql -X -d mydb -c '\dt'Confirmed by postgresql.org
How do I stop it coming back?
- Always restore with ON_ERROR_STOP set, so the first error stops the run.
- Use -1 (--single-transaction) for small dumps so a failed restore leaves nothing half loaded.
- Write down which roles own objects in the database before you dump it, so you can recreate them.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
pg_restore refuses a dump file as not a valid archive
pg_restore: [archiver] input file does not appear to be a valid archive
pg_restore only reads archives made by pg_dump in custom, tar or directory format.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix