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

Postgresql backup bash script.

From: Grant <grant(at)conprojan(dot)com(dot)au>
To: pgsql-admin(at)postgresql(dot)org
Subject: Postgresql backup bash script.
Date: 2001-03-19 00:11:56
Message-ID: Pine.LNX.4.21.0103191003490.15132-100000@webster.conprojan.com.au (view raw or flat)
Thread:
Lists: pgsql-admin
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
#-------------------------------------------------


In response to

Responses

pgsql-admin by date

Next:From: mikeDate: 2001-03-19 04:06:20
Subject: Re: Graphics GUI for PostgreSQL
Previous:From: Peter MisovicDate: 2001-03-18 22:41:26
Subject: Graphics GUI for PostgreSQL

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