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.

## 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=<code>date +%Y-%m-%d</code>
 TIME=<code>date +%k:%M:%S</code>
# 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 ./ postgres&quot;

# 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

# 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=<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
   pg_dump -D $i &gt; /backup/postgresql_dumps/&quot;$FILENAME&quot;

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

# 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


Leave a Reply

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