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.
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
head. It’s fairly fast.