pg_restore refuses a dump file as not a valid archive
pg_restore: [archiver] input file does not appear to be a valid archiveShort answer
pg_restore only reads archives made by pg_dump in custom, tar or directory format. A plain-text SQL dump, which is pg_dump's default, or a gzip file of one, is not an archive. Check how the dump was made, then restore a plain dump with psql and a compressed one through gunzip into psql.
Fix it fast
1Find out which cause you have
Look at the file before restoring it. A plain dump is readable SQL; a gzip file is compressed bytes.
file backup.dump head -c 200 backup.dump2Match what you see, then jump to its fix
- 1
head shows readable SQL such as SET statements and CREATE TABLE, and the dump was taken without -FThe dump is plain-text SQL, not an archiveFix - 2
file reports gzip compressed data, and the name ends in .gzThe file was compressed by gzip after dumpingFix - 3
pg_dump was run with -Fd and -f dirname, and the restore command points at a file inside it or at a different pathA directory-format dump was given as a single file path, or the wrong pathFix
- 1
What does the error look like?
pg_restore: [archiver] input file does not appear to be a valid archive
Cause 1 of 3
The dump is plain-text SQL, not an archive
1Check
pg_dump writes a plain-text SQL script unless you pass a format option. pg_restore does not handle that format.
head -n 20 backup.sql2Fix
Feed the script to psql instead. Create the target database first from template0; the script does not create it.
createdb -T template0 newdb
psql -X -d newdb -f backup.sql3Verify
psql finishes without errors, and the tables exist.
psql -d newdb -c '\dt'Confirmed by postgresql.org
Cause 2 of 3
The file was compressed by gzip after dumping
1Check
Dumps piped through gzip are compressed copies of a plain script. They are not a pg_dump archive either.
file backup.sql.gz2Fix
Decompress it into psql in one pipe. If the dump is meant for pg_restore, take it again with pg_dump -Fc.
gunzip -c backup.sql.gz | psql -X -d newdb
# next time, for pg_restore:
pg_dump -Fc dbname > backup.dump3Verify
Restore a custom dump with pg_restore and list its table of contents to see it is accepted.
pg_restore -l backup.dump
pg_restore -d newdb backup.dumpConfirmed by postgresql.org
Cause 3 of 3
A directory-format dump was given as a single file path, or the wrong path
1Check
With directory format, pg_dump creates a directory and its path is what pg_restore needs. The directory must not exist before the dump.
ls -l backup_dir2Fix
Point pg_restore at the directory itself and name the format.
pg_restore -Fd -d newdb backup_dir3Verify
The listing prints without the archive error.
pg_restore -l backup_dir | headConfirmed by postgresql.org
How do I stop it coming back?
- Name dumps by format: .sql for plain, .dump for custom, a folder name for directory.
- Use pg_dump -Fc when you want pg_restore, selective restore and compression.
- Run pg_restore -l on every backup file as a quick check after the dump finishes.
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
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
pg_dump refuses to run: server version is newer than pg_dump
pg_dump: detail: server version: 17.2; pg_dump version: 14.13
pg_dump will not dump a server whose major version is newer than its own.
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