Automatic periodic backups from PostgreSQL to S3 using Cron

Published on Saturday, January 29, 2022

If you're managing your own databases you'll need to make sure you database is backed up properly. A good option is to store your backups in an S3-compatible object storage.

  1. Install s3cmd:
sudo apt install s3cmd
  1. Configure it:
s3cmd --configure

It will ask you several questions, consult your S3 providers docs for more information.

  1. Write a bash script to create a postgresql dump and upload it to S3:
DIR=$(dirname "${BASH_SOURCE[0]}")
DUMP_PATH="$DIR/$DB_NAME_$(date +"%Y-%m-%d@%H-%M").dump"

# DUMP the database
pg_dump --encoding utf8 --format c --compress 9 --file $DUMP_PATH $DB_NAME

DUMP_KEY=$DUMP_PATH | cut -c 3- # Remove the ./ from the path

# Upload the dump file to S3
s3cmd put $DUMP_PATH s3://$DB_NAME-db-backups/$DB_NAME/$DUMP_KEY

# Remove the dump file from disk

For more information about taking PostgreSQL backups, checkout my previous post.

  1. Write a crontab job to run your script periodically:
crontab -e
0 */3 * * * path/to/your/script/

Note: This expression means the job will be run every 3 hours. You can change it to whatever your want.

comments powered by Disqus