66

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?

migrated from stackoverflow.com Apr 17 '11 at 9:46

This question came from our site for professional and enthusiast programmers.

  • Try importing the dump on the same machine that generated it. Also, check the Postgres versions. – Hank Gay Jun 14 '10 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 '10 at 16:19
  • This solution may also be relevant to some people: stackoverflow.com/questions/42433414/… – Muhammad Hannan Feb 24 '17 at 9:56
93

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

26

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
    [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. – Milen A. Radev Jun 14 '10 at 17:09
  • "not recommended" was an overstatement, I agree. But "most flexible" does not necessarily means "most recommended". Clarified. – leonbloy Jun 14 '10 at 17:25
13

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

  • Would whoever modded this down like to explain why? The accepted answer explains that this will work :-) – psmears Sep 25 '10 at 9:28
  • @skrafi: What is your point? – psmears Apr 20 '16 at 8:57
  • 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 '16 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 '16 at 9:35
6

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

5

For windows users try

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

Works like a charm

  • This saved me thank you! Should be accepted answer for windows at least. – Daniel Butler Aug 8 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
    It's usually helpful if you explain what that does and why it might help. – Falcon Momot Apr 11 '14 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.