Extracting a single database from a pg_dumpall PostgreSQL dump

Some data got deleted from one of our databases, and we had to restore it from our backups.

To make sure we get everything from our PostgreSQL cluster’s, we use pg_dumpall to make a SQL dump. It’s in no way the most efficient way to do a backup of a PostgreSQL database cluster, but it is by far the most flexible.

Update 16-12-2013: Rune Kaagaard updated the script and added an installer. Thanks Rune!

Numerous times I’ve had to fish our just one database from the dump created by pg_dumpall and finally got around to sticking it in a script.

Usage: postgresql_dump_extract.sh <dump_file> <db_name> > database.sql

The script does one pass over the file using grep and then uses byte offsets to cut the dump apart using tail and head. It’s fairly fast.

Enjoy!

  • Redirection to stderr with “> 2” is not working.

  • Kai

    You just saved me a lot of work. Thanks!

  • I’m not sure why, but I’m getting, “error: start or end not found” when I run this script.

    • Then it can’t find a line that says “connect dbname” in your dump file.

      Feel free to tinker with the script.

    • I got the same error message with one of my databases. Its name included a dash, e.g. my-database.

      Iff a database name includes dash characters, pg_dumpall will generate a connect statement with its database name parameter in double quotes, e.g. the full dump will won’t include the statement connect my-database, but connect "my-database".

      The solution / workaround then is to run pg_extract_db with the database name in (shell-escaped) double quotes:

      pgxextractdb big-dump-file.sql “my-database” >my-database.sql

  • rune_kg

    Thank you so much for this! On my system it did not work for the last database in the file, because “end_point” would be empty. I also needed to remove OWNER,REVOKE and GRANT commands to make the restore work with other database credentials. I did just that, added an install command, a list dbs command and put it in a gist here: https://gist.github.com/runekaagaard/7924963

    You saved me much hassle, and the cool thing about your solution compared to the other floating around the interwebs is that its actually fast enough for large files too! Great work!

    • It worked wonderfully in 2010, the dump format might have changed. I’ll add a link in the top for your updated gist – thanks!

      • rune_kg

        Thx for the linkback!! Cheers