| @@ -1,4 +1,6 @@ |
| -{attachment id=1059 float=right} 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.
|
| +{attachment id=1059 float=right} 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 [https://gist.github.com/546865/117f1831b695bf5f0c8a60d093aa8f00dceca04c|github gist repo] for this file. |
|
|
| !Example Usage
|
|
|
|
|
| @@ -7,8 +9,17 @@ |
|
|
| {code source="bash"}
|
| 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
|
| -{/code}
|
| +0 4 * * * postgres export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1{/code} |
| + |
| +If you want to clear out older backups, you can use these two entries in /etc/cron.d/backup |
| +{code source="bash"} |
| +# 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 {} \;{/code} |
| + |
| +Restoring a database looks like:{code source="bash"} |
| +bzcat /tmp/pg_db-localhost-foobar-backup-2011-03-14-0300.bz2 |nice pg_restore --verbose -U postgres -F t -d foobar{/code} |
|
|
| {code source="bash"}
|
| #!/bin/sh
|
|
|
| @@ -274,9 +285,9 @@ |
| 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`
|
| + 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 "\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`
|
| + 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
|
| }
|
|
|
|
|
| @@ -312,7 +323,7 @@ |
| 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
|
| + exit 0 |
| }
|
|
|
| # Run backup and vacuum
|
|
|
| @@ -333,7 +344,7 @@ |
| 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
|
| + exit 0 |
| }
|
|
|
| # Run backup, vacuum and analyze
|
|
|
| @@ -354,7 +365,7 @@ |
| 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
|
| + exit 0 |
| }
|
|
|
| # Run vacuum
|
|
|
| @@ -368,7 +379,7 @@ |
| 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
|
| + exit 0 |
| }
|
|
|
| # Run vacuum and analyze
|
|
|
| @@ -382,7 +393,7 @@ |
| 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
|
| + exit 0 |
| }
|
|
|
| # Print information regarding available backups
|
|
|
| @@ -404,7 +415,7 @@ |
| done
|
| echo ""
|
| fi
|
| - exit 1
|
| + exit 0 |
| }
|
|
|
| # Print configuration test
|
|
|
| @@ -513,7 +524,7 @@ |
| test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"
|
|
|
| echo ""
|
| - exit 1
|
| + exit 0 |
| }
|
|
|
| # Print help
|
|
|
| @@ -533,7 +544,7 @@ |
| echo " help, This message"
|
| echo ""
|
| echo "Report bugs to <speedboy_420 at hotmail dot com >."
|
| - exit 1
|
| + exit 0 |
| }
|
|
|
| case "$1" in
|
|
|
| @@ -542,14 +553,14 @@ |
| db_connectivity
|
| set_permissions
|
| run_b
|
| - exit 1
|
| + exit 0 |
| ;;
|
| # Run backup and vacuum
|
| bv)
|
| db_connectivity
|
| set_permissions
|
| run_bv
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Run backup, vacuum and analyze
|
|
|
| @@ -557,7 +568,7 @@ |
| db_connectivity
|
| set_permissions
|
| run_bva
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Run vacuum
|
|
|
| @@ -565,7 +576,7 @@ |
| db_connectivity
|
| set_permissions
|
| run_v
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Run vacuum and analyze
|
|
|
| @@ -573,14 +584,14 @@ |
| db_connectivity
|
| set_permissions
|
| run_va
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Print info
|
| info)
|
| set_permissions
|
| print_info
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Print configtest
|
|
|
| @@ -588,13 +599,13 @@ |
| db_connectivity
|
| set_permissions
|
| print_configtest
|
| - exit 1
|
| + exit 0 |
| ;;
|
|
|
| # Default
|
| *)
|
| print_help
|
| - exit 1
|
| + exit 0 |
| ;;
|
| esac
|
| {/code} |
|
|