Postgres full dump script

fulldump.sh

#!/bin/bash
#  $Id: fulldump.sh,v 1.4 2013/10/31 14:25:36 postgres Exp postgres $
#  $Source: /var/lib/postgresql/8.2/RCS/fulldump.sh,v $
#  $Log: fulldump.sh,v $
#
#  Revision 1.4  2013/10/31 14:25:36  postgres
#  Removed pg_dump '-D' option, added $RETVAL
#  default value for the initial case - MS.
#
#  Revision 1.3  2007/09/28 13:29:07  postgres
#  Removed local custom database list - DV.
#
#  Revision 1.2  2007/09/28 12:45:04  postgres
#  Documentation update
#
#
#
#  Conditional Postgres backups
#
#  Authors: Dick Visser <[email protected]>
#           Marco Solari <[email protected]>
#
#  Based on ideas of Colin Stearman and Murthy Kambhampaty
#
#  WHAT IS IT FOR?
#  This script will save database global information (like user names
#  and passwords) and all databases into separate files.
#  Databases are dumped using pg_dump in the custom file format and
#  compress at level 9 (highest possible). Some checks are done to
#  prevent dumping the same data twice. THIS WILL BE MOSTLY VALUABLE
#  WHEN BACKUPPING DUMPS TO AN EXTERNAL OR REMOTE DISK VIA SOME
#  DIFFERENTIAL METHOD (rsync, rsnapshot, ...).
#
#  HOW DO I INSTALL IT?
#  1. Choose or create a directory to house your backups. For example
#     a directory "backups" in postgres' homedirectory. 
#  2. Copy this script to postgres' homedirectory and
#     make sure they are executable by user "postgres", ie:
#       chown postgres.postgres fulldump.sh
#       chmod 700 fulldump.sh
#  3. Check that the bkpdir variable below points to that directory.
#     You can back up anywhere, but the directory must be writable by 
#     user "postgres".
#
#  HOW DO I USE IT?
#  1. Make sure you are logged into user "postgres" 
#  2. Change to the directory where you plan to store the backups and
#     where the scripts are.
#  3. Type in ./fulldump.sh at the prompt.
#
#  WHAT WILL HAPPEN?
#  1. fulldump.sh will execute pg_dumpall to get the global data only
#  2. It will then execute pg_dump repeatedly for each database, dumping
#     the database to a tempfile. If that backup is the same as the
#     previous backup, the tempfile is deleted. If not, it will overwrite
#     the old backup.
#  4. A log of the proceedings will be kept in fulldump.log
#  5. Any errors or informational messages will be stored in fulldump.log.err
#  6. The script will report its overall success or failure.
#

# Change to the location where you want to keep the backups
bkpdir="/var/db-backups"

###########  No User Changes needed below this line ###############
[ `whoami` != "postgres" ] && \
	echo "This must be run by user postgres" && \
	exit

# Set up some variables
LogFile="$HOME/fulldump.log"
Globals="globals.pgdump"
Failed=0

# Switch to backup directory and confirm
cd $bkpdir
[ $? -ne 0 ] && echo "Backup directory ($bkpdir) not found. Dump canceled." && exit

# Delete any earlier logs
rm $LogFile 1>/dev/null 2>&1
        
# Get the global info (users, etc) from the database
pg_dumpall -g >$Globals 2>/dev/null
[ $? -ne 0 ] && echo "[Failed]" && echo "Error dumping globals to $Globals. Dump canceled." && exit
echo "$(date +%c): Successfully dumped global database data to $Globals" >> $LogFile

# Dump all databases except templates
for db in $(psql -d template1 -t -c "select datname from pg_database where datistemplate='f' order by datname;");
do
	echo -en "\n\n\nDumping $db:\n\n" >> $LogFile
	RETVAL=-1
	pg_dump -v -v -v -Z 9    -x -Fc "$db" -f "$bkpdir/$db.pgdump.tmp" >>$LogFile 2>&1 && \
	RETVAL=$?
	if [ -f "$bkpdir/$db.pgdump" ]; then
		# Compare the two dumps. Within the first 52 bytes is a
		# timestamp that is not relevant, so skip that part.
		if !(cmp -i 51 "$bkpdir/$db.pgdump" "$bkpdir/$db.pgdump.tmp" > /dev/null 2>&1 ) then
			mv "$bkpdir/$db.pgdump.tmp" "$bkpdir/$db.pgdump"
		else
			rm "$bkpdir/$db.pgdump.tmp"
		fi
	else
		if [ -f "$bkpdir/$db.pgdump.tmp" ]; then
			mv "$bkpdir/$db.pgdump.tmp" "$bkpdir/$db.pgdump"
		fi
	fi
				
	[ $RETVAL -ne 0 ] && echo -en "$db BACKUP FAILED\r\n" && Failed=1
done

if [ $Failed -eq 0 ]; then
	exit 0
else
	echo "An error occured."
fi
#  End of script