Re: Postgresql backup bash script.

2001/3/19 Grant <grant(at)conprojan(dot)com(dot)au>

> 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