This week has been the week of pg_restore. Yay Postgres!

The actual code that was put into this fix was a lot less work than helping my fellow developer QA the monstrosity that is our backup and restore task. Aside from the smallest of configuration tweaks to get his local machine running like mine, he ran into an error that does a terrible job of explaining what it means. The offender is this:

pg_restore: [archiver] did not find magic string in file header

(Gotta say, I find the “magic string” bit hilarious, since my instructor at code school specifically wanted to take the magic — not the fun — out of programming.)

Let me back up for a second. What’s happening here is I’m using pg_dump to create a backup of a database that we currently have — either development, staging, or production for a given customer. This *dumps* everything into a nice, neat, zipped folder, and within that folder is a massive SQL file that says exactly what’s happening when I run the backup task.

Then I run the restore task with pg_restore, and that looks in my zipped folder, grabs the SQL file, and runs it to restore all the data from whatever database I got the backup from. More specifically, it drops any existing database objects to start clean, generates all your tables, sequences, indices, etc. and then it adds back your data. So the same thing that goes out must come back in. That’s where our magic string error comes into play.

With both the dump and restore tasks, you can specify options for your SQL file. In this case, I needed to use this option:

--format=c

This means I’m using a custom format. The Postgres documentation defines this option as such:

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

I was using this option for the restore, but not for the backup. What happens when you try to restore with a custom format but the backup was created with plain format? Ding ding ding! An error! Luckily, this is an easy fix once diagnosed. Add --format=c to both the dump and restore options, and make sure to run pg_dump before you try to run your restore. pg_restore will thank you, and so will these kittens.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.