88

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?

3
  • Try importing the dump on the same machine that generated it. Also, check the Postgres versions.
    – Hank Gay
    Jun 14, 2010 at 16:18
  • I can't try importing it on the same machine, because it's a production machine. Any idea, what else I can do?
    – gruszczy
    Jun 14, 2010 at 16:19
  • This solution may also be relevant to some people: stackoverflow.com/questions/42433414/…
    – Nah
    Feb 24, 2017 at 9:56

10 Answers 10

114

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

2
  • 13
    or psql dbname < db.txt ;) Dec 7, 2017 at 16:59
  • it also requires username in windows machine : "cat dump_file | psql db_user"
    – sanalism
    Apr 26, 2020 at 19:44
37

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

2
  • 2
    [OT] I beg to differ regarding the "not recommended" status of the custom output format - the sentence "This is the most flexible format in that it allows reordering of loading data as well as object definitions..." from the manual seems to me as quite an endorsement. Jun 14, 2010 at 17:09
  • "not recommended" was an overstatement, I agree. But "most flexible" does not necessarily means "most recommended". Clarified.
    – leonbloy
    Jun 14, 2010 at 17:25
18

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

4
  • Would whoever modded this down like to explain why? The accepted answer explains that this will work :-)
    – psmears
    Sep 25, 2010 at 9:28
  • @skrafi: What is your point?
    – psmears
    Apr 20, 2016 at 8:57
  • 2
    sorry, copy-pasted wrong output, the right one is pg_restore: [archiver] did not find magic string in file header so this doesn't work for simple sql dump
    – skrafi
    Apr 20, 2016 at 9:05
  • @skrafi: I'm not sure what you mean. If you use pg_dump --format=c ...> archivefile, and then use pg_restore on archivefile, then (at least when I tested it just now) it works fine. Maybe your file is corrupt somehow? Or you have hit a particular bug?
    – psmears
    Apr 20, 2016 at 9:35
10

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

6

For windows users try

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

Works like a charm

1
  • This saved me thank you! Should be accepted answer for windows at least. Aug 8, 2019 at 19:30
2

You can do something to MySQL's SOURCE command:

psql dbname

Then, in postgresql terminal:

\i filename
2
cat dumpFileName | psql -h ip -d dbName -U userName -W
1
  • 1
    It's usually helpful if you explain what that does and why it might help. Apr 11, 2014 at 18:23
1

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.

1

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
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .