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.

#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 #######
YMD=$(date "+%Y-%m-%d")
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');
sudo -u dbOwnerUN pg_dump -Ft -n "$schema" DBName | gzip -c > "$schema".sql.gz

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

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


3 thoughts on “Backup PostgreSQL Schemas

  1. Hey, thanks for the script. I was looking for a backup solution for my multi-tenant schema-based postgres app, and this appears to work great. However, I’m not sure how I’d go about restoring the db with all of the different .sql.gz files it produces. Did you ever come up with a restore script?

    • Yes, I have a script for that too.After doing this thousands of times over the last 2yrs, I had to find a way to automate at least some of it. So basically:
      – Decompresses the file
      – Drops the old schema
      – Creates the schema
      – Then use pg_restore to load the .sql into the new schema

      Again this might not be the best practice but it works for what i needed. I am sure WP will crud up my code but here it is below. It takes 2 attributes:
      – The location of the .gz
      – The name of the schema or ‘all’ if it’s a batch


      restore (){
      if [ -f $1.sql.gz ]; then
      gunzip $1.sql.gz
      if [ -f $1.sql ]; then
      sudo -u psql -d -c “DROP SCHEMA $1 CASCADE;” &> /dev/null
      echo “$1 dropped”
      sudo -u psql -d -U -c “CREATE SCHEMA $1” &> /dev/null
      echo “Data being re-imported for $1”
      sudo -u pg_restore -d -Ft -U -n $1 $1.sql &> /dev/null
      echo “No backup file for $1 exist in $DIR”

      if [ -z $1 ] || [ -z $2 ]; then
      echo “USAGE: sudo ./reload_db [/path/to/folder] [schema|all]”
      cd $1

      if [ $2 == “all” ]; then
      for schema in $(sudo -u psql -d -c “SELECT schemata.schema_name FROM information_schema.schemata;”|sed ‘s/^[ \t]*//;s/[ \t]*$//;1,2d;N;$!P;$!D;$d’);
      restore $schema
      restore $2
      echo “***>> Reload Process Complete <<***"

      Good Luck!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s