Postgresql 8.1 database bash dump script

(Last Updated On: 27 October, 2017)

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=<code>hostname</code>
 MAILLIST=&quot;name1@something.com,name2@something.com&quot;
 BACKUP_DIR=&quot;/backup/postgresql_dumps&quot;

# Date and time variables
 DATE=<code>date +%Y-%m-%d</code>
 TIME=<code>date +%k:%M:%S</code>
 TODAY=$DATE&quot;T&quot;$TIME
# only postgres can run this script!
 if [ <code>whoami</code> != &quot;postgres&quot; ]; then
   echo &quot;pgsql_dump tried to run, but user is not postgres!&quot; &gt;&gt; $LOGFILE
   echo &quot;You are not postgres, can not run.&quot;
   echo &quot;Try: su -c ./pgsql_dump.sh postgres&quot;
   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 &quot;rm&quot; (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 [ <code>find $BACKUP_DIR -type f -name '*.sql' | wc -l</code> -eq 0 ]; then
 echo &quot;There are no pgsql dumps for the last 2 days at $HOSTNAME. Something is wrong!&quot; | mail -s &quot;[PGSQLDUMP ERROR] $HOSTNAME&quot; $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=<code>psql -q -c &quot;\l&quot; | sed -n 4,/\eof/p | grep -v rows | grep -v template0 | awk {'print $1'} | sed -e '/^$/d'</code>
# Dump the databases in individual files
 for i in $DATABASES; do
   FILENAME=&quot;$i-$TODAY.sql&quot;
   pg_dump -D $i &gt; /backup/postgresql_dumps/&quot;$FILENAME&quot;
 done

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

# full backup is also necessary
 pg_dumpall -D &gt; /backup/postgresql_dumps/&quot;all-databases $TODAY&quot;.sql
 pg_dumpall &gt; /backup/postgresql_dumps/&quot;all-databases $TODAY&quot;.sql

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

2 Comments

Leave a Reply

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