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
- an app directory using the app name for each DB
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/ post@myrepo.us:/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.
Top comments (0)