pg_dump and pg_restore: input file does not appear to be a valid archive

Question

I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get:

pg_restore: [archiver] input file does not appear to be a valid archive

I have done following operations:

pg_dump -a -f db.txt dbname

And:

pg_restore -a -d dbname db.txt

What might be wrong?

Answer 1

You are dumping in plain SQL format which was designed to feed to psql. This is not recognized by pg_restore.

cat db.txt | psql dbname

Should do the trick

Answer 2

pg_dump by default creates the sql commmands necessary to recreate the data. To recover it, you just need to invoke psql (not pg_restore ) with the file as input . pg_restore is only to be used for the binary (not default, and less usual not recommended) format of pg_dump. Read the docs.

Update: The pg_dump binary formats (-Fc -Ft) that are to be used with pg_restore are ok, and offer some extra flexibility. But they are less standard (non SQL), less apt for importing from some tools (eg. a php frontend) or manipulate with a text editor, and a little less portable to other versions and even other databases. For backups, I'd stick with the default plain format. For other scenarios, the binary + pg_restore option can be equally or more apt.

The point to keep is that in Postgresql, in the typical scenario, the backup normally is done by pg_dump (plain) and the restore with the standard command line client (psql).

Answer 3

Try passing the --format=c option to pg_dump. This will allow pg_restore to restore it.

Answer 4

This is what I would do to backup my old database and restore

To back up your database

pg_dump --format=c olddb_name > db_dump_file.dump

To restore that backup

pg_restore -v -d newdb_name db_dump_file.dump

Read more on pg_dump and pg_restore

Answer 5

For windows users try

type db.txt | psql --username="YOURNAME" dbname

Works like a charm

Answer 6

You can do something to MySQL's SOURCE command:

psql dbname

Then, in postgresql terminal:

\i filename

Answer 7

cat dumpFileName | psql -h ip -d dbName -U userName -W

Answer 8

If you're on Windows, call the psql client of Postgres, in the shell, with the parameters as below:

psql -h <your_host_name> -p <postgres_port_number> -U <your_user_name_in_postgres> -f "<your_sql_script>.sql" <your_database_name>

Example:

psql -h localhost -p 5432 -U postgres -f "C:\Users\Mx\Downloads\northwind.postgre.sql" northwind

Answer 9

This error message could also mean that there's actually something wrong with the backup file (or your assumptions about it).

In one case, I had mounted a backup file in a Docker container and tried to restore, but it failed with does not appear to be a valid archive. And in fact, the file was empty, because the mount was not performed correctly.

Answer 10

I got the same error below:

pg_restore: error: input file does not appear to be a valid archive

Because I exported but did not archive apple database to backup.sql with pg_dump as shown below:

pg_dump -U john apple > backup.sql

Or:

pg_dump -U john -Fp apple > backup.sql

Then, I tried to import non-archive backup.sql to orange database with pg_restore which must be used to import archive files:

pg_restore -U john -d orange < backup.sql

So, I did it with psql which must be used to import non-archive files, then I could solve the error. *My answer explains how to export schema and data and my answer explains how to import schema and data:

psql -U john -f backup.sql orange