Postgresql 8.1 database bash dump script

I’ve wrote a simple bash script to dump databases from a Postgresql 8.1 database server
which uses the pg_dump and pg_dumpall.


#!/bin/bash
## This scripts dumps all the databases in a Postgres 8.1 server, localhost
## 2 dump files are made for each database. One with Inserts another without.
## some variables, change them to fit yours
 LOGFILE="/var/lib/pgsql/data/pg_log/pgsql_dump.log"
 HOSTNAME=`hostname`
 MAILLIST="name1@something.com,name2@something.com"
 BACKUP_DIR="/backup/postgresql_dumps"

# Date and time variables
 DATE=`date +%Y-%m-%d`
 TIME=`date +%k:%M:%S`
 TODAY=$DATE"T"$TIME
# only postgres can run this script!
 if [ `whoami` != "postgres" ]; then
   echo "pgsql_dump tried to run, but user is not postgres!" >> $LOGFILE
   echo "You are not postgres, can not run."
   echo "Try: su -c ./pgsql_dump.sh postgres"
   exit;
 fi

# clean up old dumps! (find all types which are files, with the name that ends with .sql,
# their date older than 7 days, and execute the command "rm" (remove) )
 find $BACKUP_DIR -type f -name '*.sql' -mtime +7 -exec rm {} \;

# Check if there any backup files.
# Action: find in folder BACKUP_DIR all files with file-extension .sql.
# Count the number of files with wc (word count, option -l, which counts the numbers of lines.
# If this number is 0, then there are no files, and clearly something is wrong,
# because you don't have any backups!
if [ `find $BACKUP_DIR -type f -name '*.sql' | wc -l` -eq 0 ]; then
 echo "There are no pgsql dumps for the last 2 days at $HOSTNAME. Something is wrong!" | mail -s "[PGSQLDUMP ERROR] $HOSTNAME" $MAILLIST
fi

# Create the log-file if it doesn't exist
if [ ! -e $LOGFILE ]; then
 touch $LOGFILE
fi

# Find which databases you have in your Postgresql server
# Action: list out all the databases, remove unwanted lines and characters, extract wanted line with awk (line 1),
# strip away white empty lines with the command 'sed':
DATABASES=`psql -q -c "\l" | sed -n 4,/\eof/p | grep -v rows | grep -v template0 | awk {'print $1'} | sed -e '/^$/d'`
# Dump the databases in individual files
 for i in $DATABASES; do
   FILENAME="$i-$TODAY.sql"
   pg_dump -D $i > /backup/postgresql_dumps/"$FILENAME"
 done

# we also like a dump with copy statements
 for i in $DATABASES; do
   FILENAME="$i-cp-$TODAY.sql"
   pg_dump $i > /backup/postgresql_dumps/"$FILENAME"
 done

# full backup is also necessary
 pg_dumpall -D > /backup/postgresql_dumps/"all-databases $TODAY".sql
 pg_dumpall > /backup/postgresql_dumps/"all-databases $TODAY".sql

# Finally vacuum the database
 vacuumdb -a -f -z -q

2 Comments

  1. gab

    Thank you very much for this ready-to-use script 😀

Leave a Reply

Your email address will not be published. Required fields are marked *