Differences from version 2 to 14



@@ -1,4 +1,6 @@

-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\) | 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 -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}
Page History
Date/CommentUserIPVersion
03 Sep 2013 (12:55 UTC)
spiderr24.106.202.12314
Current • Source
spiderr24.106.202.12313
View • Compare • Difference • Source
spiderr24.106.202.12312
View • Compare • Difference • Source
spiderr174.97.129.25511
View • Compare • Difference • Source
spiderr174.97.129.2559
View • Compare • Difference • Source
spiderr174.97.129.2558
View • Compare • Difference • Source
spiderr65.190.141.137
View • Compare • Difference • Source
spiderr65.190.141.136
View • Compare • Difference • Source
spiderr65.190.141.134
View • Compare • Difference • Source
spiderr66.194.217.2212
View • Compare • Difference • Source