This is a permutation of the original pg_backup script as written by speedboy_420. I have made changes to allow for environment variables to make backups of a remote host, change backup direct via ENV variable, as well as use bzip2 for all compression. Example UsageCopy script to /usr/bin/pg_backup or similar. chmod 755 /usr/bin/pg_backup Put something like this in /etc/cron.d/backup
0 3 * * * postgres export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1 0 4 * * * postgres export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
#!/bin/sh # # Postgresql backup script # # Author # | # +-- speedboy (speedboy_420 at hotmail dot com) # +-- spiderr (spiderr at bitweaver dot org) # # Last modified # | # +-- 16-10-2001 # +-- 16-12-2007 # # Version # | # +-- 1.1.2 # # Description # | # +-- A bourne shell script that automates the backup, vacuum and # analyze of databases running on a postgresql server. # # Tested on # | # +-- Postgresql # | | # | +-- 8.1.9 # | +-- 7.1.3 # | +-- 7.1.2 # | +-- 7.1 # | +-- 7.0 # | # +-- Operating systems # | | # | +-- CentOS 5 (RHEL 5) # | +-- Linux Redhat 6.2 # | +-- Linux Mandrake 7.2 # | +-- FreeBSD 4.3 # | +-- Cygwin (omit the column ":" because NT doesn't quite digest it) # | # +-- Shells: # | # +-- sh # +-- bash # +-- ksh # # Requirements # | # +-- grep, awk, sed, echo, bzip2, chmod, touch, mkdir, date, psql, # test, expr, dirname, find, tail, du, pg_dump, vacuum_db # # Installation # | # +-- Set the path and shell you wish to use for this script on the # | first line of this file. Keep in mind this script has only been # | tested on the shells above in the "Tested on" section. # | # +-- Set the configuration variables below in the configuration # | section to appropriate values. # | # +-- Remove the line at the end of the configuration section so that # | the script will run. # | # +-- Now save the script and perform the following command: # | # | chmod +x ./pg_backup.sh # | # +-- Now run the configuration test: # | # | ./pg_backup.sh configtest # | # | This will test the configuration details. # | # +-- Once you have done that add similiar entries given as examples # below to the crontab (`crontab -e`) without the the _first_ # # characters. # # # Run a backup of the remote database host 'foodb', likely on a private network # 00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2 >> /var/log/pgsql/pg_backup.log 2>&1 # # Run a backup of the local database host 'foodb' to a custom backup directory # 00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1 # # Restoration # | # +-- Restoration can be performed by using psql or pg_restore. # | Here are two examples: # | # | a) If the backup is plain text: # | # | Firstly bunzip2 your backup file (if it was bzip2ped). # | # | nunzip2 backup_file.bz2 # | psql -U postgres database < backup_file # | # | b) If the backup is not plain text: # | # | Firstly bunzip2 your backup file (if it was bzip2ed). # | # | bunzip2 backup_file # | pg_restore -d database -F {c|t} backup_file # | # | Note: {c|t} is the format the database was backed up as. # | # | pg_restore -d database -F t backup_file_tar # | # +-- Refer to the following url for more pg_restore help: # # http://www.postgresql.org/idocs/index.php?app-pgrestore.html # # To Do List # 1. db_connectivity() is BROKEN, if the script can not create a # connection to postmaster it should die and write to the logfile # that it could not connect. # 2. make configtest check for every required binary # ######################################################################## # Start configuration # ######################################################################## # ################## # Authentication # ################## # # Postgresql hostname to connect to. if [ $PGHOST ]; then PARAM_PGHOST="-h $PGHOST" else PGHOST="localhost" fi
# Postgresql username to perform backups under. if [ -z $PGUSER ]; then PGUSER="postgres" fi
# Postgresql password for the Postgresql username (if required). # postgresql_password="password"
################## # Locations # ################## # # Location to place backups. if [ -z $PGBACKUPDIR ]; then PGBACKUPDIR="/var/lib/pgsql/backups" fi subdir=`date +%Y-%m`
# Location to place the pg_backup.sh logfile. if [ -z $PGLOGDIR ]; then PGLOGDIR="/var/log/pgsql/pg_backup.log" fi
# Location of the psql binaries. if [ -z $PGBINDIR ]; then PGBINDIR="/usr/bin" fi
################## # Permissions # ################## # # Permissions for the backup location. permissions_backup_dir="0755"
# Permissions for the backup files. permissions_backup_file="0644"
# Permissions for the backup logfile. permissions_backup_log="0644"
################## # Other options # ################## # # Databases to exclude from the backup process (separated by a space) exclusions="template"
# Backup format # | # +-- p = plain text : psql database < backup_file # +-- t = tar : pg_restore -F t -d database backup_file # +-- c = custom : pg_restore -F c -d database backup_file # backup_format="t"
# Backup large objects backup_large_objects="yes"
# bzip2 the backups backup_bzip2="yes"
# Date format for the backup # | # +-- %d-%m-%Y = DD-MM-YYYY # +-- %Y-%m-%d = YYYY-MM-DD # +-- %A-%b-%Y = Tuesday-Sep-2001 # +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001 # | # +-- For more date formats type: # # date --help # backup_date_format="%Y-%m-%d"
# You must comment out the line below before using this script #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1 ######################################################################## # End configuration # ######################################################################## # ################# # Variables # ################# # version="1.1.2" current_time=`date +%H%M` date_info=`date +$backup_date_format` PGPASSWORD="$postgresql_password" PATH="$PATH:/bin:/usr/bin"
# Export the variables export PGUSER PGPASSWORD PATH
################# # Checking # ################# # # Check the backup format if [ "$backup_format" = "p" ]; then backup_type="Plain text SQL" backup_args="-F $backup_format"
elif [ "$backup_format" = "t" ]; then backup_type="Tar" if [ "$backup_large_objects" = "yes" ]; then backup_args="-b -F $backup_format" else backup_args="-F $backup_format" fi elif [ "$backup_format" = "c" ]; then backup_type="Custom" if [ "$backup_large_objects" = "yes" ]; then backup_args="-b -F $backup_format" else backup_args="-F $backup_format" fi else backup_format="c" backup_args="-F $backup_format" backup_type="Custom" fi
################# # Functions # ################# # # Obtain a list of available databases with reference to the user # defined exclusions db_connectivity() { tmp=`echo "($exclusions)" | sed 's/\ /\|/g'` if [ "$exclusions" = "" ]; then databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` else databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` fi }
# Setup the permissions according to the Permissions section set_permissions() { # Make the backup directories and secure them. mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"
# Touch the log file touch "$PGLOGDIR"
# Make the backup tree chmod -f $permissions_backup_log "$PGLOGDIR" chmod -f $permissions_backup_dir "$PGBACKUPDIR" chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir" # chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info" }
# Run backup run_b() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 1 }
# Run backup and vacuum run_bv() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 1 }
# Run backup, vacuum and analyze run_bva() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 1 }
# Run vacuum run_v() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR done exit 1 }
# Run vacuum and analyze run_va() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR done exit 1 }
# Print information regarding available backups print_info() { echo "Postgresql backup script version $version" echo "" echo "Available backups:" echo "" if [ ! -d $PGBACKUPDIR ] ; then echo "There are currently no available backups" echo "" exit 0 else for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`" #for j in `ls "$PGBACKUPDIR/$i"`; do # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`" #done done echo "" fi exit 1 }
# Print configuration test print_configtest() { echo "Postgresql backup script version $version" echo "" echo "Configuration test..." echo ""
# Check database connectivity information echo -n "Database hostname : " echo "$PGHOST" echo -n "Database username : " echo "$PGUSER" echo -n "Database connectivity : " $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."
# Backup information echo "" echo "Backup information:" echo "" echo -n "Backup format : " if [ "$backup_format" = "p" ]; then echo "Plain text SQL" elif [ "$backup_format" = "t" ]; then echo "Tar" else echo "Custom" fi
echo -n "Backup large objects : " if [ "$backup_large_objects" = "yes" ]; then echo "Yes" else echo "No" fi echo -n "bzip2 backups : " if [ "$backup_bzip2" = "yes" ]; then echo "Yes" else echo "No" fi echo -n "Backup date format : $date_info" echo ""
# File locations echo -n "Backup directory : " echo "$PGBACKUPDIR" echo -n "Backup logfile : " echo "$PGLOGDIR" echo -n "Postgresql binaries : " echo "$PGBINDIR"
# Backup file permissions
echo -n "Backup directory permissions : " echo "$permissions_backup_dir" echo -n "Backup file permissions : " echo "$permissions_backup_file" echo -n "Backup log permissions : " echo "$permissions_backup_log"
# Databases that will be backed up echo -n "Databases that will be backed up : " echo "" for i in $databases; do echo " $i" done
# Databases that will not be backed up echo -n "Databases that will not be backed up :" echo "" if [ "$exclusions" = "" ]; then echo " none" else for i in $exclusions; do echo " $i" done echo "" fi
# Check if the backups location is writable echo "Checking permissions:" echo "" echo -n "Write access : $PGBACKUPDIR: " # Needed to create/write to the dump location" test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"
# Check if the logfile location is writable echo -n "Write access : $PGLOGDIR: " # Needed to create/write to this scripts logfile" if [ ! -x $PGLOGDIR ] ; then test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No" else test -w "$PGLOGDIR" && echo "Yes" || echo "No" fi
# Check if the binaries are executable echo -n "Execute access : $PGBINDIR/psql: " test -x $PGBINDIR/psql && echo "Yes" || echo "No"
echo -n "Execute access : $PGBINDIR/pg_dump: " test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"
echo -n "Execute access : $PGBINDIR/vacuumdb: " test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"
echo "" exit 1 }
# Print help print_help() { echo "Postgresql backup script version $version" echo "" echo "Usage: $0 [options]" echo "" echo "Options:" echo " b, Backup ALL databases" echo " bv, Backup and Vacuum ALL databases" echo " bva, Backup, Vacuum and Analyze ALL databases" echo " v, Vacuum ALL databases" echo " va, Vacuum and Analyze ALL databases" echo " info, Information regarding all available backups" echo " configtest, Configuration test" echo " help, This message" echo "" echo "Report bugs to <speedboy_420 at hotmail dot com >." exit 1 }
case "$1" in # Run backup b) db_connectivity set_permissions run_b exit 1 ;; # Run backup and vacuum bv) db_connectivity set_permissions run_bv exit 1 ;;
# Run backup, vacuum and analyze bva) db_connectivity set_permissions run_bva exit 1 ;;
# Run vacuum v) db_connectivity set_permissions run_v exit 1 ;;
# Run vacuum and analyze va) db_connectivity set_permissions run_va exit 1 ;;
# Print info info) set_permissions print_info exit 1 ;;
# Print configtest configtest) db_connectivity set_permissions print_configtest exit 1 ;;
# Default *) print_help exit 1 ;; esac
| | This is a permutation of the original pg_backup script as written by speedboy_420. I have made changes to allow for environment variables to make backups of a remote host, change backup directory via ENV variable, as well as use bzip2 for all compression. There is also a github gist repo for this file. Example UsageCopy script to /usr/bin/pg_backup or similar. chmod 755 /usr/bin/pg_backup Put something like this in /etc/cron.d/backup
0 3 * * * postgres export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1 0 4 * * * postgres export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
If you want to clear out older backups, you can use these two entries in /etc/cron.d/backup
# Remove backups older than 1 week except those made on the 9th, 19th, and 29th 5 4 * * * root find /bak/database/ -mtime +7 -name "*_db-*[123456780]-0[3-4]00*" -exec rm {} \; # Remove backups older than 60 days except those made on the 9th of the month 5 4 * * * root find /bak/database/ -mtime +60 -name "*_db-*[12]9-0[3-4]00*" -exec rm {} \;
Restoring a database looks like:
bzcat /tmp/pg_db-localhost-foobar-backup-2011-03-14-0300.bz2 |nice pg_restore --verbose -U postgres -F t -d foobar
#!/bin/sh # # Postgresql backup script # # Author # | # +-- speedboy (speedboy_420 at hotmail dot com) # +-- spiderr (spiderr at bitweaver dot org) # # Last modified # | # +-- 16-10-2001 # +-- 16-12-2007 # # Version # | # +-- 1.1.2 # # Description # | # +-- A bourne shell script that automates the backup, vacuum and # analyze of databases running on a postgresql server. # # Tested on # | # +-- Postgresql # | | # | +-- 8.1.9 # | +-- 7.1.3 # | +-- 7.1.2 # | +-- 7.1 # | +-- 7.0 # | # +-- Operating systems # | | # | +-- CentOS 5 (RHEL 5) # | +-- Linux Redhat 6.2 # | +-- Linux Mandrake 7.2 # | +-- FreeBSD 4.3 # | +-- Cygwin (omit the column ":" because NT doesn't quite digest it) # | # +-- Shells: # | # +-- sh # +-- bash # +-- ksh # # Requirements # | # +-- grep, awk, sed, echo, bzip2, chmod, touch, mkdir, date, psql, # test, expr, dirname, find, tail, du, pg_dump, vacuum_db # # Installation # | # +-- Set the path and shell you wish to use for this script on the # | first line of this file. Keep in mind this script has only been # | tested on the shells above in the "Tested on" section. # | # +-- Set the configuration variables below in the configuration # | section to appropriate values. # | # +-- Remove the line at the end of the configuration section so that # | the script will run. # | # +-- Now save the script and perform the following command: # | # | chmod +x ./pg_backup.sh # | # +-- Now run the configuration test: # | # | ./pg_backup.sh configtest # | # | This will test the configuration details. # | # +-- Once you have done that add similiar entries given as examples # below to the crontab (`crontab -e`) without the the _first_ # # characters. # # # Run a backup of the remote database host 'foodb', likely on a private network # 00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2 >> /var/log/pgsql/pg_backup.log 2>&1 # # Run a backup of the local database host 'foodb' to a custom backup directory # 00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1 # # Restoration # | # +-- Restoration can be performed by using psql or pg_restore. # | Here are two examples: # | # | a) If the backup is plain text: # | # | Firstly bunzip2 your backup file (if it was bzip2ped). # | # | nunzip2 backup_file.bz2 # | psql -U postgres database < backup_file # | # | b) If the backup is not plain text: # | # | Firstly bunzip2 your backup file (if it was bzip2ed). # | # | bunzip2 backup_file # | pg_restore -d database -F {c|t} backup_file # | # | Note: {c|t} is the format the database was backed up as. # | # | pg_restore -d database -F t backup_file_tar # | # +-- Refer to the following url for more pg_restore help: # # http://www.postgresql.org/idocs/index.php?app-pgrestore.html # # To Do List # 1. db_connectivity() is BROKEN, if the script can not create a # connection to postmaster it should die and write to the logfile # that it could not connect. # 2. make configtest check for every required binary # ######################################################################## # Start configuration # ######################################################################## # ################## # Authentication # ################## # # Postgresql hostname to connect to. if [ $PGHOST ]; then PARAM_PGHOST="-h $PGHOST" else PGHOST="localhost" fi
# Postgresql username to perform backups under. if [ -z $PGUSER ]; then PGUSER="postgres" fi
# Postgresql password for the Postgresql username (if required). # postgresql_password="password"
################## # Locations # ################## # # Location to place backups. if [ -z $PGBACKUPDIR ]; then PGBACKUPDIR="/var/lib/pgsql/backups" fi subdir=`date +%Y-%m`
# Location to place the pg_backup.sh logfile. if [ -z $PGLOGDIR ]; then PGLOGDIR="/var/log/pgsql/pg_backup.log" fi
# Location of the psql binaries. if [ -z $PGBINDIR ]; then PGBINDIR="/usr/bin" fi
################## # Permissions # ################## # # Permissions for the backup location. permissions_backup_dir="0755"
# Permissions for the backup files. permissions_backup_file="0644"
# Permissions for the backup logfile. permissions_backup_log="0644"
################## # Other options # ################## # # Databases to exclude from the backup process (separated by a space) exclusions="template"
# Backup format # | # +-- p = plain text : psql database < backup_file # +-- t = tar : pg_restore -F t -d database backup_file # +-- c = custom : pg_restore -F c -d database backup_file # backup_format="t"
# Backup large objects backup_large_objects="yes"
# bzip2 the backups backup_bzip2="yes"
# Date format for the backup # | # +-- %d-%m-%Y = DD-MM-YYYY # +-- %Y-%m-%d = YYYY-MM-DD # +-- %A-%b-%Y = Tuesday-Sep-2001 # +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001 # | # +-- For more date formats type: # # date --help # backup_date_format="%Y-%m-%d"
# You must comment out the line below before using this script #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1 ######################################################################## # End configuration # ######################################################################## # ################# # Variables # ################# # version="1.1.2" current_time=`date +%H%M` date_info=`date +$backup_date_format` PGPASSWORD="$postgresql_password" PATH="$PATH:/bin:/usr/bin"
# Export the variables export PGUSER PGPASSWORD PATH
################# # Checking # ################# # # Check the backup format if [ "$backup_format" = "p" ]; then backup_type="Plain text SQL" backup_args="-F $backup_format"
elif [ "$backup_format" = "t" ]; then backup_type="Tar" if [ "$backup_large_objects" = "yes" ]; then backup_args="-b -F $backup_format" else backup_args="-F $backup_format" fi elif [ "$backup_format" = "c" ]; then backup_type="Custom" if [ "$backup_large_objects" = "yes" ]; then backup_args="-b -F $backup_format" else backup_args="-F $backup_format" fi else backup_format="c" backup_args="-F $backup_format" backup_type="Custom" fi
################# # Functions # ################# # # Obtain a list of available databases with reference to the user # defined exclusions db_connectivity() { tmp=`echo "($exclusions)" | sed 's/\ /\|/g'` if [ "$exclusions" = "" ]; then databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` else databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR` fi }
# Setup the permissions according to the Permissions section set_permissions() { # Make the backup directories and secure them. mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"
# Touch the log file touch "$PGLOGDIR"
# Make the backup tree chmod -f $permissions_backup_log "$PGLOGDIR" chmod -f $permissions_backup_dir "$PGBACKUPDIR" chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir" # chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info" }
# Run backup run_b() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 0 }
# Run backup and vacuum run_bv() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 0 }
# Run backup, vacuum and analyze run_bva() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" if [ "$backup_bzip2" = "yes" ]; then bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2" else chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time" fi finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR done exit 0 }
# Run vacuum run_v() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR done exit 0 }
# Run vacuum and analyze run_va() { for i in $databases; do start_time=`date '+%s'` timeinfo=`date '+%T %x'`
"$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1 finish_time=`date '+%s'` duration=`expr $finish_time - $start_time` echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR done exit 0 }
# Print information regarding available backups print_info() { echo "Postgresql backup script version $version" echo "" echo "Available backups:" echo "" if [ ! -d $PGBACKUPDIR ] ; then echo "There are currently no available backups" echo "" exit 0 else for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`" #for j in `ls "$PGBACKUPDIR/$i"`; do # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`" #done done echo "" fi exit 0 }
# Print configuration test print_configtest() { echo "Postgresql backup script version $version" echo "" echo "Configuration test..." echo ""
# Check database connectivity information echo -n "Database hostname : " echo "$PGHOST" echo -n "Database username : " echo "$PGUSER" echo -n "Database connectivity : " $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."
# Backup information echo "" echo "Backup information:" echo "" echo -n "Backup format : " if [ "$backup_format" = "p" ]; then echo "Plain text SQL" elif [ "$backup_format" = "t" ]; then echo "Tar" else echo "Custom" fi
echo -n "Backup large objects : " if [ "$backup_large_objects" = "yes" ]; then echo "Yes" else echo "No" fi echo -n "bzip2 backups : " if [ "$backup_bzip2" = "yes" ]; then echo "Yes" else echo "No" fi echo -n "Backup date format : $date_info" echo ""
# File locations echo -n "Backup directory : " echo "$PGBACKUPDIR" echo -n "Backup logfile : " echo "$PGLOGDIR" echo -n "Postgresql binaries : " echo "$PGBINDIR"
# Backup file permissions
echo -n "Backup directory permissions : " echo "$permissions_backup_dir" echo -n "Backup file permissions : " echo "$permissions_backup_file" echo -n "Backup log permissions : " echo "$permissions_backup_log"
# Databases that will be backed up echo -n "Databases that will be backed up : " echo "" for i in $databases; do echo " $i" done
# Databases that will not be backed up echo -n "Databases that will not be backed up :" echo "" if [ "$exclusions" = "" ]; then echo " none" else for i in $exclusions; do echo " $i" done echo "" fi
# Check if the backups location is writable echo "Checking permissions:" echo "" echo -n "Write access : $PGBACKUPDIR: " # Needed to create/write to the dump location" test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"
# Check if the logfile location is writable echo -n "Write access : $PGLOGDIR: " # Needed to create/write to this scripts logfile" if [ ! -x $PGLOGDIR ] ; then test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No" else test -w "$PGLOGDIR" && echo "Yes" || echo "No" fi
# Check if the binaries are executable echo -n "Execute access : $PGBINDIR/psql: " test -x $PGBINDIR/psql && echo "Yes" || echo "No"
echo -n "Execute access : $PGBINDIR/pg_dump: " test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"
echo -n "Execute access : $PGBINDIR/vacuumdb: " test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"
echo "" exit 0 }
# Print help print_help() { echo "Postgresql backup script version $version" echo "" echo "Usage: $0 [options]" echo "" echo "Options:" echo " b, Backup ALL databases" echo " bv, Backup and Vacuum ALL databases" echo " bva, Backup, Vacuum and Analyze ALL databases" echo " v, Vacuum ALL databases" echo " va, Vacuum and Analyze ALL databases" echo " info, Information regarding all available backups" echo " configtest, Configuration test" echo " help, This message" echo "" echo "Report bugs to <speedboy_420 at hotmail dot com >." exit 0 }
case "$1" in # Run backup b) db_connectivity set_permissions run_b exit 0 ;; # Run backup and vacuum bv) db_connectivity set_permissions run_bv exit 0 ;;
# Run backup, vacuum and analyze bva) db_connectivity set_permissions run_bva exit 0 ;;
# Run vacuum v) db_connectivity set_permissions run_v exit 0 ;;
# Run vacuum and analyze va) db_connectivity set_permissions run_va exit 0 ;;
# Print info info) set_permissions print_info exit 0 ;;
# Print configtest configtest) db_connectivity set_permissions print_configtest exit 0 ;;
# Default *) print_help exit 0 ;; esac
|