Daily PostgreSQL Backups script
Read the rest of this entry »
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" |