Re: Postgresql backup bash script.

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