Posts Tagged postgresql

Daily PostgreSQL Backups script

Posted by on Wednesday, 23 June, 2010

I love Backups!
This one is for a daily dump of the database, rolling over every 10 days

Put the following into a file in /etc/cron.daily/postgres-backup

#!/bin/bash
DIR=/backup/pgsql
FTPUSR=yourusername
FTPPASS=yourftppass
FTPHOST=yourftphost
 
LIST=$(su - postgres -c "/usr/bin/psql -lt" |/usr/bin/awk '{ print $1}' |/bin/grep -vE '^-|:|^List|^Name|template[0|1]')
DATE=$(/bin/date '+%Y%m%d');
TENDAY=$(/bin/date -d'10 days ago' '+%Y%m%d');
/bin/mkdir -p $DIR/$DATE/
/bin/chown postgres:postgres $DIR/$DATE/
 
for d in $LIST
do
su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$DATE/$d.sql.gz"
done
 
rm -rf $DIR/$TENDAY/
/usr/bin/lftp -u "$FTPUSR,$FTPPASS" ${FTPHOST} -e "set ftp:ssl-protect-data true;mirror --reverse --delete $DIR/ /; exit"