PostgreSQL Backups

While shell scripting is not my strong suit I put this together to do automatic backups of the Postgres database that backs the ja-sig.org services. The script will vacuum each database, do a full dump of each database, do a dump of each schema and do a global meta-data backup. All the dumps are compressed with bzip2 and date/time stamped. Logs and backups older than 7 days are removed.

The results get stored in a date-stamped directory and all actions are logged into a date/time stamped log file.

The script can be easily customized using the variables at the top of the file and should work on any PostgreSQL database.

<br />#!/bin/bash<br /><br /># setup environment<br />PG_PORT=5432<br /><br />PG_HOME=/usr/postgres/8.2<br />PG_VAR=/postgres/8.2<br /><br />PG_DATA=$PG_VAR/data<br />PG_BACKUPS=$PG_VAR/backup<br />PG_LOGS=$PG_VAR/logs<br /><br />PG_BIN=$PG_HOME/bin<br />PG_DUMPALL=$PG_BIN/pg_dumpall<br />PG_DUMP=$PG_BIN/pg_dump<br />PG_PSQL=$PG_BIN/psql<br />PG_VACUUMDB=$PG_BIN/vacuumdb<br /><br />PG_BACKUP=$PG_BACKUPS/`date "+%Y%m%d"`<br />PG_LOG_FILE=$PG_LOGS/backup_`date "+%Y%m%d_%H%M%S"`.log<br /><br />NICE="nice -n 20"<br />COMPRESS="bzip2 -9"<br />DUMPALL_ARGS="-p $PG_PORT -c -g -o"<br />DUMP_ARGS="-p $PG_PORT -c -o -F t"<br />VACUUM_ARGS="-z -p $PG_PORT -d"<br />PSQL_ARGS="-p $PG_PORT -U postgres -q -c"<br /><br /><br />echo "`date`: Script $0 starting" >> $PG_LOG_FILE 2>&1<br /><br /># Make the backup directory<br />mkdir -p $PG_BACKUP >> $PG_LOG_FILE 2>&1<br /><br /># Iterate over all databases<br />databases=`$PG_PSQL $PSQL_ARGS "\l" 2>> $PG_LOG_FILE | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`<br />for database in $databases<br />do<br /> echo "`date`: Vacuuming database '$database'" >> $PG_LOG_FILE 2>&1<br /> $PG_VACUUMDB $VACUUM_ARGS $database >> $PG_LOG_FILE 2>&1<br /> echo "`date`: Vacuuming of database '$database' complete" >> $PG_LOG_FILE 2>&1<br /><br /> PG_DUMP_DB_FILE=$PG_BACKUP/backup.`date "+%Y%m%d_%H%M%S"`.$database.tar.bz2<br /> echo "`date`: Backing up database '$database' to '$PG_DUMP_DB_FILE'" >> $PG_LOG_FILE 2>&1<br /> $NICE $PG_DUMP $DUMP_ARGS $database 2>> $PG_LOG_FILE | $NICE $COMPRESS > $PG_DUMP_DB_FILE 2>> $PG_LOG_FILE<br /> echo "`date`: Backing up database '$database' to '$PG_DUMP_DB_FILE' complete" >> $PG_LOG_FILE 2>&1<br /> <br /> # iterate over all schemas in this database<br /> schemas=`$PG_PSQL $PSQL_ARGS "\dn" -d $database 2>> $PG_LOG_FILE | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`<br /> for schema in $schemas<br /> do<br /> PG_DUMP_SCHM_FILE=$PG_BACKUP/backup.`date "+%Y%m%d_%H%M%S"`.$database.$schema.tar.bz2<br /> echo "`date`: Backing up schema '$schema' of database '$database' to '$PG_DUMP_SCHM_FILE'" >> $PG_LOG_FILE 2>&1<br /> $NICE $PG_DUMP $DUMP_ARGS -n $schema $database 2>> $PG_LOG_FILE | $NICE $COMPRESS > $PG_DUMP_SCHM_FILE 2>> $PG_LOG_FILE<br /> echo "`date`: Backing up schema '$schema' of database '$database' to '$PG_DUMP_SCHM_FILE' complete" >> $PG_LOG_FILE 2>&1<br /> done<br />done<br /><br /># Dump global meta-data<br />PG_DUMPALL_FILE=$PG_BACKUP/backup.`date "+%Y%m%d_%H%M%S"`.global_metadata.sql.bz2<br />echo "`date`: Dumping database cluster meta-data to '$PG_DUMPALL_FILE'" >> $PG_LOG_FILE 2>&1<br />$PG_DUMPALL $DUMPALL_ARGS 2>> $PG_LOG_FILE | $NICE $COMPRESS > $PG_DUMPALL_FILE 2>> $PG_LOG_FILE<br />echo "`date`: Dumping database cluster meta-data to '$PG_DUMPALL_FILE' complete" >> $PG_LOG_FILE 2>&1<br /><br />echo "`date`: Removing old backups" >> $PG_LOG_FILE 2>&1<br />find $PG_BACKUPS -mtime +7 -exec rm -Rf {} \;<br />echo "`date`: Removing old backups complete" >> $PG_LOG_FILE 2>&1<br /><br />echo "`date`: Removing old logs" >> $PG_LOG_FILE 2>&1<br />find $PG_LOGS -mtime +7 -exec rm -Rf {} \;<br />echo "`date`: Removing old logs complete" >> $PG_LOG_FILE 2>&1<br /><br />echo "`date`: Script $0 complete" >> $PG_LOG_FILE 2>&1<br /><br />exit 0<br />