Encrypt Your DB Backups

I’m mostly putting this here for my reference. I do this in multiple projects and always seem to forget where my instructions are.

Generate Keys

From your user’s home directory:

openssl req -x509 -nodes -days 1000000 -newkey rsa:4096 -keyout .ssh/backup_key.pem -out .ssh/backup_key.pem.pub

Fill out the questions appropriately. Or not. I don’t think it matters really.

That will create the public and private keys.

backup_key.pem - private key backup_key.pem.pub - public key

Add AWS Keys

Create a file to hold your AWS keys.

mkdir ~/.aws vi ~/.aws/credentials

Here is the format:

aws_access_key_id = ACCESSKEY
aws_secret_access_key = SECRETKEY
aws_region = us-east-1

Backup Script

I don’t want to bore with details but here is what this script will do from a high level:

  1. Take DB name
  2. Set the backup file name with timestamp
  3. dump the DB with pg_dump
  4. remove the old versions
  5. copy to s3 for storage
# #######################

# Database Name

# Location to place backups.
# Numbers of days you want to keep copies of your databases

if [ -z "${database_name}" ]
 echo "Please specify a database name as the first argument"
 exit 1

# String to append to the name of the backup files
backup_date=`date +%Y-%m-%d-%H-%M-%S`
echo "Dumping ${database_name} to ${backup_dir}${backup_name}"

pg_dump ${database_name}|bzip2|openssl smime -encrypt \
 -aes256 -binary -outform DEM \
 -out ${backup_dir}${backup_name} \

find ${backup_dir} -type f -prune -mtime \
    +${number_of_days} -exec rm -f {} \;

# push file to S3
aws s3 cp ${backup_dir}${backup_name} s3://YOURBUCKET/db_backup/


Set up a crontab for this script. This one fires off at 3am PT daily. Change as needed.

crontab -e 0 3 * * * /location/pg_backup.sh YOURDB


Decryption is pretty straight forward. Get your private key and pull the file from S3.

openssl smime -decrypt -in my_database.sql.sql.bz2.enc -binary -inform DEM -inkey private.pem | bzcat > my_database.sql.sql