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
Views: Raw Message | Whole Thread | Download mbox
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.

# Location of the backup logfile.
# Location 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 | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"

In response to


Browse pgsql-admin by date

  From Date Subject
Next Message mike 2001-03-19 04:06:20 Re: Graphics GUI for PostgreSQL
Previous Message Peter Misovic 2001-03-18 22:41:26 Graphics GUI for PostgreSQL