35

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?

Improve this question

4 Answers 4

Reset to default
35

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format.

From pg_dump manpage:

  -F format, --format=format
       Selects the format of the output.  format can be one of the
       following:

       p, plain
           Output a plain-text SQL script file (the default).

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 with psql -f out.sql [other options]. You will probably want to create the target database first, as the --create option is not present in the pg_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.

Improve this answer
1
  • 1
    in my case I was importing the wrong file, it had similar name.. hope this comment will help someone someday Commented Jul 27, 2020 at 20:36
2

One possible problem is your data file is truncated. Check to see if you're downloading the full file by comparing file sizes.

Improve this answer
2

I was encountering this error on Windows after making a backup and then immediately attempting to restore it. Turned out the issue was, I wrote the file out using pg_dump ... > filename, which apparently corrupts the output. Instead, I needed to write the file out using pg_dump ... -f filename. Once I had a backup file I'd created that way, it restored without incident.

Improve this answer
0
1

If you use git-lfs, make sure to have the actual file, not the pointer!

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.