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.

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!

#!/bin/sh
 
if [ $# -ne 2 ]
then
	echo "Usage: $0 <postgresql sql dump> <db_name>" >&2
	exit 1
fi
 
db_file=$1
db_name=$2
 
if [ ! -f $db_file -o ! -r $db_file ]
then
	echo "error: $db_file not found or not readable" >&2
	exit 2
fi
 
grep -b "^\connect" $db_file | grep -m 1 -A 1 "$db_name$" | while read line
do
	bytes=`echo $line | cut -d: -f1`
 
	if [ -z "$start_point" ]
	then
		start_point=$bytes
	else
		end_point=$bytes
	fi
done
 
if [ -z "$start_point" -o -z "$end_point" ]
then
	echo "error: start or end not found" >&2
	exit 3
fi
 
db_length=`expr $end_point - $start_point`
 
tail -c +$start_point $db_file | head -c $db_length | tail +3