Simple Bash Script for Rolling PostgreSQL Backups to AWS S3

Recently I’ve been working on a new web project that uses a PostgreSQL database. To save money of course I’m just running the DB and web server on the same tiny Linode instance instead of a separate expensive RDS instance or something that would manage backups and other jazz for you.

So since we are running the DB ourselves we must make our own backups. S3 storage makes sense for the backups since it is very cheap and easy to use especially with the small size of the backups.

I cobbled together a bash script to do this for us shown below (which is always a swear-fest since I use bash once in a blue moon and the syntax is finicky like with requiring no or one space between things…)

#!/bin/bash
#Simple rolling PostgreSQL backups to aws s3
NUM_BACKUPS=50
BACKUP_FOLDER="rolling-backups"
DB_USER="MY_USER_NAME"
S3_PATH="MY-S3-BUCKET"

mkdir -p $BACKUP_FOLDER

today=`date '+%Y-%m-%d %H:%M:%S'`
filename=$BACKUP_FOLDER"/"$today".bak"

echo -e "\nDumping database...\n"
pg_dumpall -U ${DB_USER} > "$filename"


#delete older backups
echo -e "\nDeleting older backups...\n"

count=$(ls rolling-backups | wc -l)
if [[ $count > $NUM_BACKUPS ]]
    then
	ls -t -d ${BACKUP_FOLDER}/* | sed -e '1,'${NUM_BACKUPS}'d' | xargs -d '\n' rm
fi


echo -e $"\nSyncing to s3...\n"
aws s3 sync ${BACKUP_FOLDER} s3://${S3_PATH} --delete --size-only

The script keeps only the most recent 50 files to s3 and deletes the oldest ones beyond the 50 count to save on storage space.

A couple key things must be done in order to get this script to work.

1. .pgpass file

First you will need to create a .pgpass file in the home directory of whichever user the script will be running under. A .pgpass file enables postgres commands to run without a password prompt which is very important for an automated script. After you create the .pgpass file you will need to change the permissions with a chmod 0600 .pgpass command or postgres will ignore the file.

Below is an example .pgpass file contents. More can be found out about .pgpass files in the PostgreSQL documentation here.

localhost:5432:*:myusername:mypassword

2. AWS credentials

Next if you haven’t already you’ll need to grab a credentials file from AWS and then put it into a .aws folder in the home directory of script run user. This is to allow the AWS command line tool to pickup the credentials automatically for S3 access.

3. Cron job

Finally you will need to add a line to your cron config file for this script in order to run the backups automatically.

If you’re running CentOS like me you can edit it with a sudo vi /etc/crontab command. I’m running the backup script once every 6 hours so if you want something like that you would add a line looking like the below to your cron file

0 */6 * * * your-username /home/your-username/postgres-s3-backup.sh >> /home/your-username/s3-backups.log

Oh and don’t forget to install the AWS CLI and make the script executable with a chmod +x command.

Thank you for reading!

Only cool people share. You do wanna be cool right?Share on Reddit0Share on Facebook0Share on StumbleUpon0Tweet about this on Twitter0

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.