jeff-j of osxroot
3/25/2015 - 6:24 PM

came from: https://discussions.apple.com/thread/3227951?start=0&tstart=0 This script dumps all databases in postgres individually. Databas

came from: https://discussions.apple.com/thread/3227951?start=0&tstart=0

This script dumps all databases in postgres individually. Databases are vacuumed and then dumped. Dump-Format is currently set as compressed pgsql dump (See "-F c" in $BACKUP_OPTIONS) File names consist of "databasename"-"backupdade".pgdump I have programmed to automatically purge backups older than two weeks (see " -2w " in $EXP_DATE) If you drop a database you will have to remove backups for that database manually.

I have enabled a daily run of this scrip by placing the executable script somwhere and add it to the crontab of root: sudo crontab -e Enter the following line and your backups start every day at 6:00. Adjust paths and scriptname according to your own setup.

0 6 * * * ~/.bin/postgres_dump.sh > /dev/null 2>&1

postgres_backup.sh
content_copyfile_download
# #!/bin/bash
if (test "`whoami`" != "root") then
        echo "You need to be root to start this"
        exit 1
fi
 
 
PG_PORT="5432"
PG_USER="_postgres"
PG_BIN="/usr/bin/"
 
 
ACT_DATE=$(date '+%y-%m-%d')
EXP_DATE=$(date -v -2w '+%y-%m-%d')
BACKUP_DIR="/Backups/postgres"
LOGFILE=$BACKUP_DIR"/_pg_backup.log"
COMPRESSION="9"
PG_CON="-U $PG_USER"
BACKUP_OPTIONS="$PG_CON -b -C -F c -Z $COMPRESSION"
VACUUM_OPTIONS="$PG_CON -eq"
 
 
echo "***** DB_BACKUP $ACT_DATE *****" >>$LOGFILE
for db in `$PG_BIN/psql $PG_CON -lt | sed /\eof/p | grep -v = | awk {'print $1'}`
  do
    # vacuum
    if [ "X" == "X$db" ]; then
          continue;
    fi
    echo $(date '+%c')" -- vacuuming database $db" >> $LOGFILE
    if $PG_BIN/vacuumdb $VACUUM_OPTIONS $db
      then
      #echo "OK!" >>$LOGFILE
          sleep 1
    else
      echo "No Vacuum in database $db!" >>$LOGFILE
    fi
    # backup
    echo $(date '+%c')" -- backing up database $db" >>$LOGFILE
    if  $PG_BIN/pg_dump $BACKUP_OPTIONS -f $BACKUP_DIR/$db-$ACT_DATE.pgdump $db
      then
      if [ -e $BACKUP_DIR/$db-$EXP_DATE.pgdump ]; then
          echo $(date '+%c')" -- deleting old backup" >>$LOGFILE
          rm $BACKUP_DIR/$db-$EXP_DATE.pgdump
      fi
    else
      echo "FAIL Database $db backup!" >>$LOGFILE
    fi
  done