RimuHosting Blog

Daily PostgreSQL Backups

We have had a few backup scripts posted, however i noticed today when a customer asked for a postgreSQL one we don’t actually have one handy.
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

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" backupspace.rimuhosting.com -e "set ftp:ssl-protect-data true;mirror --reverse --delete $DIR/ /; exit"

Let me know if you have any problems with the script, or any corrections/patches/ideas :)

Liz Quilty

, , , ,

3 responses to “Daily PostgreSQL Backups”

  1. I was getting blanks in the list so I editted it to be:
    LIST=$(su – postgres -c “/usr/bin/psql -lt” |/usr/bin/awk ‘{ print $1}’ |/bin/grep -vE ‘^[ ]*[\|]?$|^-|:|^List|^Name|template[0|1]’)

  2. Nice script, thank you!

    lftp always delete by ftp-folder. How can I change the script?

    • feel free to edit the script and the lftp comment to not delete it or change it to do something else