Backup PostgreSQL Schemas

I have been handed the responsibility to manage the backups of a PostgreSQL database. Easy enough you would say, but there is a catch.

  1. The owner wants backups to be kept of each individual schema within the database
  2. I have never managed a PostgreSQL DB before

So not being one to back down from such a challenge, I searched high and low all over the Internet, but either the Internet community isn’t interested in doing this or I am not looking in the right places. So here, with the help of fragments of scripts from around the web, I have worked out a solution which accomplishes task at hand. It is all bottled in the script below which I think is self-explanatory with the aid of a generous helping comments scattered throughout the file.

#Description:
#This script will create a compressed backup of the Genesis Postgres Db and store it on a predefined folder.
#Backups that are older than 30days will also be removed automatically

####### Make and change to directory where backups will be saved #######
BASE_DIR="/path/to/backup/folder"
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p $DIR
cd $DIR

####### Full Postgres Backup #######
sudo -u postgres pg_dumpall | gzip -c > All_Db.sql.gz

####### Individual Schema Backups #######
# 1. Select individual schemas within the database and pipe the results into sed which does Step 2
# 2. Clean up the output from the SQL above
#     - Get rid of the empty spaces at the beginning of each line
#     - Remove the head and tail info from the file(Title, labels, etc)
for schema in $(sudo -u dbOwnerUN psql -d DBName -c "SELECT schemata.schema_name FROM information_schema.schemata;"|sed 's/^[ \t]*//;s/[ \t]*$//;1,2d;N;$!P;$!D;$d');
do
sudo -u dbOwnerUN pg_dump -Ft -n "$schema" DBName | gzip -c > "$schema".sql.gz
done

####### Delete backup files older than 30 days #######
OLD=$(find $BASE_DIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
echo deleting old backup files: $OLD
echo $OLD | xargs rm -rfv
fi

As I said, this is my first attempt at anything like this so it may not be the best or easiest way of accomplishing this. (I am just sharing what I know and recording for my reference). So please post comments if you have a better solution.

….Questions are also welcomed