[TIP] backup script for PostgreSQL

Classic Servoy
1

Since someone recently asked me for this I thought I share this also on the forum.
It’s a backup script I use for backing up all my PostgreSQL databases. It’s an old shell script I found on the internet and adjusted it to my needs.
In fact I added a lot more parameters and comments so it’s easier to use.
It dumps .sql files of each of your databases in your PostgreSQL cluster that you can easily restore back with psql (psql -f /path/to/backup.sql).

I post here the source but also attach the actual file for easy download.

Enjoy!

#!/bin/bash

#
#  This script will dump all databases of the PostgreSQL cluster (excl. template1 and template0)
#  into a directory.
#  After all databases are dumped then it will dump the global data like users/roles/etc.
#
#  Backup times are logged in a log file in the same backup directory.
#
#  NOTE and WARNING: Before dumping the databases it will clear ANY files from the backup directory.
#
# --------------------------------------------------------------------------------------------------------
#
#  For more info on the switches for pg_dump and pg_dumpall use the --help switch with these 2 commands
#


# ===
# Parameters you can change
#
pg_tools="/usr/local/bin"   # path to the pgsql command-line tools, adjust accordingly
pg_host="-h localhost"      # leave empty if you want to use the local UNIX socket
                            # default in this script is "-h localhost" and will use TCP/IP
use_gzip=false              # gzip the dump files

backup_dir="/pgsql-backup" 
logfile="$backup_dir/postgresql_backup.log" 

# ===

databases=`$pg_tools/psql $pg_host -U postgres -d postgres -l | sed -n 4,/\eof/p | grep -v rows\) | grep -v template | awk {'print$1'}`

# clear backup folder for new data dump
rm $backup_dir/*

#rm $logfile   # only needed when you want the logfile to be in another location other than the backup directory.
touch $logfile

# start logging
timeinfo=`date '+%T %x'`
echo "Starting Backup at $timeinfo" >> $logfile

for i in $databases
do
        if $use_gzip; then
            # zipped
            $pg_tools/pg_dump $i $pg_host -U postgres | gzip > "$backup_dir/$i.sql.gz"
        else
            # non-zipped
            $pg_tools/pg_dump $i $pg_host -U postgres > "$backup_dir/$i.sql"
        fi
        
        # do some logging
        timeinfo=`date '+%T %x'`
        echo "Backup complete at $timeinfo for database: $i" >> $logfile
done

# get users, roles and other global data
if $use_gzip; then
    # zipped
    $pg_tools/pg_dumpall -g $pg_host -U postgres | gzip > "$backup_dir/pg_global_data.sql.gz"
else   
    # not zipped
    $pg_tools/pg_dumpall -g $pg_host -U postgres > "$backup_dir/pg_global_data.sql"
fi

# do some logging
timeinfo=`date '+%T %x'`
echo "Backup complete at $timeinfo for global data" >> $logfile

pg_backup.sh.gz (926 Bytes)

2

Great stuff: thanks Robert. :-)