Rails backup schemes to backup Postgres databases

The hobbyist developer is back just to document something I created for myself - just to see if I could do it. Maybe someone else needs a Postgres backup scheme. I have two, one in Bash, the other in Ruby

I've had three Rails apps running on a Digital Oceans droplet for about 10 years. I upgraded the droplet about four years ago. At some point I figured that I should have some kind of backup. I initially had a bash script that semi worked. Something happened that it stopped working with a password error. I tried to fix the copied script but gave up when I found a new script from somewhere. I wish I could remember where, just to acknowledge the author.

I know very little about bash/shell scripts - mainly copy/paste and try to make it work for whatever I needed. The script I found was just to backup one DB. I just add a front end script to handle the 3 apps. Here are the scripts:

##### backup_all.sh

# for use with cron, eg:
# 0 3 * * * postgres /var/db/db_backup.sh foo_db

DATABASES=(myvfw_production ptgolf_production our_books_production);

for DATABASE in "${DATABASES[@]}"
do
    # echo $DATABASE
    /home/rails/backups/apps/db_backup.sh "$DATABASE"
done

##### db_backup.sh

#!/bin/bash

if [[ -z "$1" ]]; then
    echo "Usage: $0 <db_name> [pg_dump args]"
    exit 1
fi

DB="$1"; shift
DUMP_ARGS=$@
# DIR="/home/rails/backups/$DB"
suffix="_production"
dbname=${DB%$suffix}
DIR="/home/rails/backups/apps/databases/$dbname"
echo $DIR
# exit 1

KEEP_DAILY=7
KEEP_WEEKLY=5
KEEP_MONTHLY=12

function rotate {
    rotation=$1
    fdate=`date +%Y-%m-%d -d $date`
    file=$DIR/daily/*$fdate*.gz
    mkdir -p $DIR/$rotation/ || abort
    if [ -f $file ]; then
        cp $file $DIR/$rotation/ || abort
    else
        echo
    fi
}

function prune {  
    dir=$DIR/$1
    keep=$2
    ls $dir | sort -rn | awk " NR > $keep" | while read f; do rm $dir/$f; done
}

function abort {
    echo "aborting..."
    exit 1
}

mkdir -p $DIR/daily || abort
mkdir -p $DIR/weekly || abort
mkdir -p $DIR/monthly || abort
mkdir -p $DIR/yearly || abort

date=`date +%Y-%m-%d` || abort
day=`date -d $date +%d` || abort
weekday=`date -d $date +%w` || abort
month=`date -d $date +%m` || abort

# Do the daily backup
/usr/bin/pg_dump $DB $DUMP_ARGS | gzip > $DIR/daily/${DB}_$date.sql.gz
test ${PIPESTATUS[0]} -eq 0 || abort

# Perform rotations
if [[ "$weekday" == "0" ]]; then
    rotate weekly
fi
if [[ "$day" == "01" ]]; then
    rotate monthly
fi
if [[ "$month/$day" == "01/01" ]]; then
    rotate yearly
fi

prune daily $KEEP_DAILY
prune weekly $KEEP_WEEKLY
prune monthly $KEEP_MONTHLY

While don't know bash, I more or less concluded that it:

• Builds a timestamp based name for the database
• Calls pg_dump to download the sql dump
• Gzips the sql
• Adds the zipped file to a structured directory
• Prunes the directory size

The directory structure is basically

• this script (pg_gzip_backup.rb)
• a log file to catch any crontab errors and log updates
• a root pg_dump directory
  • an app directory using the app name for each DB
    • daily backups
    • weekly backups
    • monthly backups
    • yearly backups

The script is run from cron once a day. Another cron script runs some time later and does a rsync to an offsite repo.

I've had this running for a couple of years and will occasionaly unzip the latest dump and upload it into development or staging. I think once someone deleted a record and all siblings. I downloaded the backup and extracted the record and siblings to yaml /json. I then uploaded it back into the DB.

The daily/weekly/monthly/yearly periods might be overkill but it was there! You could just use X daily backups and forget about the weekly, monthly and yearly backups.

Not having anything to do, I decided to write a Ruby version of the backup scheme. I think I changed the directory structure a little by using a different directory naming scheme. I also added a little upfront configuration values. Here is the Ruby version in all it's glory:

class PgBackup
  require "date"
  require 'fileutils'
  # example crontab files. should write error to a log file in dir
    # cron staging 00 2 * * * /home/developer/.rbenv/shims/ruby /home/developer/apps/backup/pg_gzip_backup.rb >> /home/developer/apps/backup/log 2>&1
    # cron local   14 12 * * * /Users/xxx/.rbenv/shims/ruby /Users/xxx/work/common/backup/pg_gzip_backup.rb >> /Users/salex/work/common/backup/log 2>&1 
    # rsync --rsh="ssh -p8611" -a --delete /home/rails/backups/apps/databases/  [e-mail]:/home/post/backups/databases

  attr_accessor :dir, :apps, :today

  def initialize
    # puts ARGV[0]
    # ARGV[0].present? @stage = ARGV[0] : @stage = 'production'
    # config local paths for stage, dir, pgdump, gzip and an array of databases/apps
    @stage = "development" # normally production but set to development or staging for testing
    @dir = "/Users/salex/work/common/pg_backup"
    # find path of gzip and pg_dump with `which app` and set class vars
    @pgdump = "/opt/homebrew/bin/pg_dump"
    @gzip = "/usr/bin/gzip"
    @apps = %w[myusers myvfw ptgolf our_books] # array of app names
    # go to work and generate the pg dumps
    @today = Date.today  # current date used in filename of dumps
    set_backup_folders # check or create a dump folder structure for each app
    gzip_dumps# get the dumps and gzip them
  end

  def gzip_dumps
    apps.each do |app|
      db = "#{app}_#{@stage}" # create the rails/pg database name
      file_name = db+"_#{today.to_s}.sql" # create the dump name
      file_path = "#{dir}#{app}/daily/#{file_name}" # always dump daily
      `pg_dump #{db} > #{file_path}`
      `#{@gzip} -f #{file_path}`
      # daily period is now zipped, copy and prune if needed
      @zipped_file_path = file_path+'.gz'
      copy_period('weekly') if is_eow?
      copy_period('monthly') if is_eom?
      copy_period('yearly') if is_eoy?
      prune("#{dir}#{app}/daily/",7)
      prune("#{dir}#{app}/weekly/",5)
      prune("#{dir}#{app}/monthly/",12)
    end
  end

  def copy_period(period) # if it's the end of a period, copy daily
    period_file_path = @zipped_file_path.sub('daily',period)
    FileUtils.copy(@zipped_file_path,period_file_path)
  end

  def is_eow?
    today.sunday? # It's Sunday
  end
  def is_eom?
    today.month != (today + 1).month # It's the last day of the month
  end
  def is_eoy? 
    today.year != (today + 1).year # It's the last day of the year
  end

  def prune(dir,size)
    curr_files = Dir.children(dir).sort.reverse # reverse in case of a .file in folder
    if curr_files.size > size
      File.delete(dir + curr_files[size]) # delete the oldest file
    end
  end

  private

  def set_backup_folders
    File.write(@dir+"/log", "") if !File.exist?(@dir+"/log")
    File.write(@dir+"/log", "script opened #{Time.now}\n", mode: 'a+')
    if !Dir.exist?(@dir+"/pg_dumps/")
      # create all app dumps
      Dir.mkdir(@dir+"/pg_dumps/")
      apps.each{|a| make_new_app_set(a)}
    else
      # create any new app dumps added to the apps array after initial creation
      apps.each do |a|
        if !Dir.exist?(@dir+"/pg_dumps/#{a}/")
          make_new_app_set(a)
        end
      end
    end
    @dir = @dir+"/pg_dumps/"
  end

  def make_new_app_set(app)
    Dir.mkdir(@dir+"/pg_dumps/#{app}/")
    Dir.mkdir(@dir+"/pg_dumps/#{app}/daily/")
    Dir.mkdir(@dir+"/pg_dumps/#{app}/weekly/")
    Dir.mkdir(@dir+"/pg_dumps/#{app}/monthly")
    Dir.mkdir(@dir+"/pg_dumps/#{app}/yearly")
  end
  # fire this ruby script
  PgBackup.new
end

Not only do I not understand bash scripting with all its hieroglyphic notation, piping etc, I'm not great at Ruby - but I'm proud that I got it working!

I initially tried to use Zlib but the gz files would not uncompress on a Mac. I finally figured out the Zlib for Ruby only inflates or deflates stings and does the add gz headers to a file. Gzip is on the Mac and Linux by default. I just had to get around the piping stuff. Just for info: one db dump was 3.7gb and reduced to 549kb. The other was 2.2gb and reduced to 323kb.

I use rbenv but I'm sure it can be converted to rvm or just the host version of Ruby. For cron everything is based off the root or home directory. The first 5 lines in def initialize is the configuration. Just define path to the script and everything is based off of that. There are a couple crontab examples in the top comments that needs the Ruby path. Didn't know that cron was so picky! There is also a rsync scheme to push the changes to a remote.

I'm sure there is some commercial version of a postgres backup, but then I'd have to figure it out. Try it out in development and see what you think.

Hope it interests someone.