postgres-backup

#!/bin/bash

max_dump_age="4"

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

source $DIR/postgres-environment

databases=$(psql -U postgres -c "\l" | grep "^ " | grep -v "^   " | grep -v template0 | awk '{print $1}')
backup_dir="$HOME/backups/postgres"
date=$(date '+%Y%m%d%H%M%S')

mkdir -p $backup_dir

for database in $databases
do
  echo "[$(date '+%d/%b/%Y:%H:%M:%S %z')] local backup of database \"$database\" started"
  pg_dump -Fc -U postgres $database | gzip > $HOME/$database.$date.dump.gz.tmp
  mv $HOME/$database.$date.dump.gz.tmp $backup_dir/$database.$date.dump.gz
  echo "[$(date '+%d/%b/%Y:%H:%M:%S %z')] finished pg_dump of database \"$database\""
  echo "[$(date '+%d/%b/%Y:%H:%M:%S %z')] starting analyze on database \"$database\""
  psql -U postgres -c "set vacuum_freeze_min_age=100000; set vacuum_freeze_table_age=10000000; set maintenance_work_mem='1GB'; analyze;" --quiet $database
  echo "[$(date '+%d/%b/%Y:%H:%M:%S %z')] local backup of database \"$database\" finished"
done

echo "[$(date '+%d/%b/%Y:%H:%M:%S %z')] removing old local backups"
find $backup_dir -name "*.dump.gz" -mtime +$max_dump_age | xargs rm -f