Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

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

7 Answers 7

up vote 35 down vote accepted

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

share|improve this answer

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

share|improve this answer
    
[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

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

share|improve this answer
    
Would whoever modded this down like to explain why? The accepted answer explains that this will work :-) –  psmears Sep 25 '10 at 9:28

For windows users try

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

Works like a charm

share|improve this answer

You can do something to MySQL's SOURCE command:

psql dbname

Then, in postgresql terminal:

\i filename
share|improve this answer

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

share|improve this answer

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

share|improve this answer
    
It's usually helpful if you explain what that does and why it might help. –  Falcon Momot Apr 11 at 18:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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