I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump
:
sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name
This command generates a valid sql file that starts with droping any existing database objects, then generates all tables, indizes, sequences and so on, and finally inserts data.
When I try to restore the generated backup file with: (line breaks added for display purposes only)
sudo pg_restore
-d database_name -h 127.0.0.1 -U postgres
--format=c --clean --create out.sql
it fails and prints:
pg_restore: [archiver] did not find magic string in file header
What is the reason for that?
Best Answer
You're restoring with
pg_restore --format=c ...
but thepg_dump
was not done with--format=c
, it was done with the default, plain format.From
pg_dump
manpage:A dump in plain format should be fed directly to the
psql
command-line tool,pg_restore
doesn't know what it is, which is the reason of this error message: did not find magic string in file header.You can look directly at the dump file with
more out.sql
in shell and you'll see readable SQL commands. Restore it withpsql -f out.sql [other options]
. You will probably want to create the target database first, as the--create
option is not present in thepg_dump
invocation.On the other hand, you may reinvoke the dump adding
--format=c
to its options. Then that would be the opposite:pg_restore
must be used to interpret a dump file in the custom format.