Skip site navigation (1) Skip section navigation (2)

Re: Postgresql backup bash script.

From: Dimitar Atanasov <datanasov(at)gmail(dot)com>
To: Grant <grant(at)conprojan(dot)com(dot)au>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Postgresql backup bash script.
Date: 2011-05-03 12:52:37
Message-ID: BANLkTikV8mrq5TpshWKKr+rUzNyZd+ZS2A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: raghu ramDate: 2011-05-03 13:30:46
Subject: Re: Can we Flush the Postgres Shared Memory ?
Previous:From: RaghavendraDate: 2011-05-03 12:31:11
Subject: Re: Can we Flush the Postgres Shared Memory ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group