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.

## 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

# Date and time variables
 DATE=`date +%Y-%m-%d`
 TIME=`date +%k:%M:%S`
# 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 ./ postgres"

# 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

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

# 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
   pg_dump -D $i > /backup/postgresql_dumps/"$FILENAME"

# we also like a dump with copy statements
 for i in $DATABASES; do
   pg_dump $i > /backup/postgresql_dumps/"$FILENAME"

# 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


  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 *