Re: Postgresql backup bash script.

[Dimitar Atanasov <datanasov@xxxxxxxxx>]

2001/3/19 Grant <grant@xxxxxxxxxxxxxxxx>

Hello, I have written a backup script that will vacuum, analyze and backup every postgresql database.

(1) Modify logfile and backup_dir variables to suite your needs.
(2) I have a trust relationship so I am never prompted for a password on connection.
(3) Add an entry to crontab to perform the backups nightly or whenever you wish.
(4) Have fun.

# Crontab starts here.
00 01 * * * /path/to/script/backup > /dev/null 2>&1
#--------------------------------------------------

# Backup script starts here.

#!/bin/bash
# Location of the backup logfile.
logfile="/path/to/logfile.log"
# Location to place backups.
backup_dir="/directory/to/place/backups"
touch $logfile
timeslot=`date +%H-%M`
databases=`psql -h localhost -U postgres -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 and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
       /server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
       /server/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"
done
#-------------------------------------------------

Hi All,
I've changed a little bit the script and here it is now:

----------- cut --------------------
#!/bin/bash

set -e
set -b
# Location of the backup logfile.
logfile="/var/log/pg_sql_backup/bkp.log"

# Location to place backups.
backup_dir="/mnt/backup/Databases/pgsql/"
timeslot=`date +%Y-%m-%d-%H-%M-%S`

if [ $# -lt 1 ]; then
        echo "pg_backup help for more detailed help"
        exit 0
fi

if [ "$1" == "help" ]; then
        echo ""
        echo "Usage: pg_backup <hostname> show - shows all databases on pg_sql server"
        echo "Usage: pg_backup <hostname> <database_name> - backups specified database"
        echo "Usage: pg_backup <hostname> - backups all databases on pg_sql server"
        echo "Usage: pg_backup <hostname> db_size - show sizes of all databases on pg_sql server"
        exit 0
fi

if [ "$2" == "show" ]; then
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|" | grep -v template0 |grep -v ":")
        echo "Server $1 contains folloing databases:"
        echo "======================================"
        for i in $databases; do
          echo $i
        done
        echo "======================================"
        exit 0
fi

if [ "$2" == "db_size" ]; then
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|" |grep -v template0 |grep -v ":")
        echo "Server $1 contains folloing databases:"
        echo "======================================"
        for i in $databases; do
          psql -h "$1" -U backup -q -c "SELECT pg_size_pretty(pg_database_size('$i')) as \"$i\";" template1
        done
        echo "======================================"
        exit 0
fi

if [ -n "$2" ]; then 
        databases="$2"
else
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|"  |grep -v template0 |grep -v ":")
fi

for i in $databases; do
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Seshon Started" | tee -a "$logfile"
        echo "[$1] [$timeinfo] Vacuum started on database: $i" | tee -a "$logfile"
        vacuumdb -z -h "$1" -U backup "$i" >> "$logfile"
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Vacuum complete on database: $i" | tee -a "$logfile"

        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Backup started on database: $i" | tee -a "$logfile"
        pg_dump "$i" -h "$1" -U backup | gzip > "$backup_dir/pgsql-$1-$i-$timeslot-database.gz"
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Backup complete on database: $i" | tee -a "$logfile"
        echo "[$1] [$timeinfo] Seshon Ended" | tee -a "$logfile"

done

echo "All is OK"
----------- cut --------------------
in cron you can put the following script:

--------- cut ----------
#!/bin/bash

echo -e "============== DataBase Backups ==============="
echo -e "===============================================\n"
for i in host1 host2 host3 hostXXX; do
    echo -e "=== Connecting to $i ==="
    echo -e "=== Starting backup of $i ==="
    /etc/everbread/crons/pg_backup.sh $i show
    /etc/everbread/crons/pg_backup.sh $i db_size
    /etc/everbread/crons/pg_backup.sh $i
    echo -e "=== Backup of $i finished ==="
    echo -e "=== Disconnected from $i ====\n"
done

echo -e "==============================================="
echo -e "============= Data Base Backups Done ==========\n"


----------cut ----------
-- 
Regards,
Dimitar Atanasov