Script di esempio per l'amministrazione di PostgreSQL
PosgreSQL Administration Sample Scripts

* Crontab
* Backup logico completo
* Backup logico suddiviso per database (utile per ripristini parziali e/o migrazioni)
* Backup fisico con continuous archiving

# Riavvio settimanale base dati
15 00 * * 0 pg_ctl -m fast restart > /data1/BCK/log/restart.log 2>&1
# Backup logico del database cluster
05 01 * * * /var/lib/pgsql/scripts/pgDumpAll.sh > /data1/BCK/log/pgDumpAll.log 2>&1
#
# Backup logico della struttura del cluster e dei singoli database su file separati
# 05 02 * * * /var/lib/pgsql/scripts/pgDumpStructDB.sh > /data1/BCK/log/pgDumpStructDB.log 2>&1
# 05 03 * * * /var/lib/pgsql/scripts/pgDumpDB.sh > /data1/BCK/log/pgDumpDB.log 2>&1
#
# Backup fisico del database cluster con continuos archiving
# 05 04 * * * /var/lib/pgsql/scripts/pgDumpPhysical.sh > /data1/BCK/log/pgPhysical.log 2>&1
#
# Svecchiamento backup dalla directory dei backup
05 05 * * * find /data1/BCK/logico -name 'postgresql-*.gz' -mtime +8 -exec rm {} \;
# 10 05 * * * find /data1/BCK/fisico -name '*.tar.gz' -mtime +3 -exec rm {} \;
# 15 05 * * * find /data1/BCK/archdir -mtime +4 -exec rm {} \;


#!/bin/bash
## pgDumpAll.sh
timeslot=`date +%Y%m%d`
backup_dir="/data1/BCK/logici"
logfile="$backup_dir/../log/backup-logfile-$timeslot.log"
timeinfo=`date '+%T %x'`
echo "Backup completo start: $timeinfo " >> $logfile
pg_dumpall | gzip > "$backup_dir/postgresql-all-$timeslot.sql.gz"
RET=$?
timeinfo=`date '+%T %x'`
echo "Backup completo end: $timeinfo  Return code: $RET" >> $logfile


#!/bin/bash
## pgDumpDB.sh
timeslot=`date +%Y%m%d`
logfile="/BCK_pgdata/backup-logfile-$timeslot.log"
backup_dir="/BCK_pgdata"
databases=`psql -d postgres -U sys -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup completo start: $timeinfo - database:      $i " >> $logfile
#        /server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
        $PGSQL_HOME/bin/pg_dump -U sys -d -F p -D -v $i | gzip > "$backup_dir/postgresql-$i-$timeslot-database.sql.gz"
done


#!/bin/bash
## pgDumpStructDB.sh
timeslot=`date +%Y%m%d`
logfile="/BCK_pgdata/backup-logfile-$timeslot.log"
backup_dir="/BCK_pgdata"
timeinfo=`date '+%T %x'`
echo "Backup infrastruttura completa start: $timeinfo " >> $logfile
$PGSQL_HOME/bin/pg_dumpall -U sys --schema-only | gzip > "$backup_dir/postgresql-schema-only-$timeslot.sql.gz"
$PGSQL_HOME/bin/pg_dumpall -U sys --roles-only | gzip > "$backup_dir/postgresql-roles-only-$timeslot.sql.gz"
$PGSQL_HOME/bin/pg_dumpall -U sys --tablespaces-only | gzip > "$backup_dir/postgresql-tablespaces-only-$timeslot.sql.gz"


#!/bin/bash
## pgDumpPhysical.sh
timeslot=`date +%Y%m%d`
backup_dir="/data1/BCK/fisici"
logfile="$backup_dir/../log/backup-logfile-$timeslot.log"
timeinfo=`date '+%T %x'`
echo "Backup fisico start: $timeinfo " >> $logfile
psql -d postgres -q -c "SELECT pg_start_backup('bck-full-$timeinfo');"
cd /
tar cvfz $backup_dir/pgData-$timeslot.tar.gz ./data/pgsql/9.0/data
RET=$?
psql -d postgres -q -c "SELECT pg_stop_backup();"
timeinfo=`date '+%T %x'`
echo "Backup fisico end: $timeinfo  Return code: $RET" >> $logfile