Board index Linux Shell Scripts

Moderator: chandranjoy

PostgreSQL Backup Script

Postby chandranjoy » Fri Jun 22, 2012 10:09 am

PostgreSQL backup script:

Code: Select all
#!/bin/bash
# Location to place backups.
backup_dir="/home/postgres-backup/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copie of your databases
number_of_days=30
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;



This script run under Linux.

This script need to run under the postgres user. The best way to do that is to insert it into crontab :
# su – postgres


$vi backup_pgsql.sh

(insert the above script)
$chmod 700 backup_pgsql.sh


$crontab -e


And add a line like this one :
10 0 * * * /var/lib/pgsql/backup_pgsql.sh


Reference:
http://www.defitek.com/blog/2010/01/06/ ... up-script/
http://archives.postgresql.org/pgsql-ad ... g00143.php
http://wiki.postgresql.org/wiki/Automat ... p_on_Linux
chandranjoy
Site Admin
 
Posts: 283
Joined: Fri Oct 23, 2009 11:19 pm

Return to Shell Scripts

Who is online

Users browsing this forum: No registered users and 1 guest


cron