PostgreSQL backups to S3 with retention limits

Fernando - Nov 11 '19 - - Dev Community

This is a bit of an update on the latest version of Martilla. The feature introduced that I'm more excited about is the ability to limit the retention of your backups.

This will be a walkthrough on how to backup a PostgreSQL DB to AWS S3 and easily enforce a retention limit. This other blog post gives an introduction to the CLI tool used.

Objective

Daily backups with a retention period of one week. This can be easily adapted to 3 backups per day + 1 month retention, or other any other combination desired.

For the sake of the walkthrough we'll stick to the simple daily backups with 7 day retention example.

Prerequisites

A working Ruby installation, a PostgreSQL database to backup and the Martilla CLI tool

$ gem install martilla
Enter fullscreen mode Exit fullscreen mode

The config file

Create a sample config file

$ martilla setup backup-config.yml
Enter fullscreen mode Exit fullscreen mode

The sample config file will look similar to the following

---
db:
  type: postgres
  options:
    host: localhost
    user: username
    password: password
    db: databasename
storage:
  type: local
  options:
    filename: database-backup.sql
    retention: 0
notifiers:
- type: none
Enter fullscreen mode Exit fullscreen mode

Database

The default database type is already postgres, although the same configuration options would work with a mysql alternative.

If we're exporting the backups from the same machine/VM that's hosting the PostgreSQL server we can actually remove options we don't need. Let's make the db directive look like the following snippet

db:
  type: postgres
  options:
    db: my_db
Enter fullscreen mode Exit fullscreen mode

The host will default to localhost and we'll set both the user & password as ENV variables.

$ export PG_USER="my_db_user"
$ export PG_PASSWORD="my_db_password"
Enter fullscreen mode Exit fullscreen mode

Storage

AWS S3 has quite a few configurable options (full list here), but we can make use of ENV variables for sensitive data again. The storage directive needs to look like the following snippet

storage:
  type: s3
  options:
    bucket: my_side_project
    filename: backups/backup.sql
    region: 'us-west-2'
    retention: 7
Enter fullscreen mode Exit fullscreen mode

All options have pretty straightforward names except for retention. This option is how Martilla knows when it should remove 'stale' backups that you no longer want to keep.

The retention option represents the maximum number of backups you're willing to keep at any given moment. In this case we're looking for daily backups and we want to hold on to them for no longer than a week. We have no need to keep them for any longer so we can save storage space by getting rid of them at that point. Simple as setting the retention option to 7.

Other valid scenarios include:

  • Backups every 4h but we're only interested on the most up to date version would work with retention set to 1 to keep only the latest backup dumped.
  • Weekly backups without any retention limit, they're all important to keep. This can be achieved with using the default value of 0 (no retention limit is set).

Don't forget to set your AWS credentials using ENV variables. If you're on AWS you can use IAM roles by not setting the ENV variables too.

$ export AWS_ACCESS_KEY_ID="my_access_key"
$ export AWS_SECRET_ACCESS_KEY="my_secret_key"
Enter fullscreen mode Exit fullscreen mode

Notifications / Alerts

Get notified when the backups are dumped successfully or alerted when they fail. Avoid feeling like a fool if you ever happen to need them and realize your backups hadn't been stored correctly for weeks.

The following notifiers directive sends you a Slack notification on a specific channel (learn about Slack webhooks here)

- type: 'slack'
  options:
    slack_channel: '#backups'
    slack_webhook_url: https://hooks.slack.com/services/TTSAMPLEPO/BWEBHOOKOQ/NDQLOLAMSKDMLOLASROFLMDLA
Enter fullscreen mode Exit fullscreen mode

There's also SMTP, AWS SES and other email notifiers you can configure. If you rather only get notified when the backups are failing set send_success & send_failure as options with true/false values.

For more details on all the available options check the docs

Test it out!

$ martilla backup backup-config.yml
Enter fullscreen mode Exit fullscreen mode

You should be able to see the backup appear in your S3 bucket within the backups directory. Martilla adds a timestamp by default to differentiate from each run.

Try executing this command multiple times, and you should eventually see that no more than 7 backups stored... That's the retention limit working!

Cron

The ol'reliable Cron, for running your backups. The correct usage of Cron or even alternatives to Cron aren't within the scope of this post. The following is just an oversimplified example that works for this scenario.

Start by editing your crontab file

$ crontab -e
Enter fullscreen mode Exit fullscreen mode

and add this line to it

0 23 * * * /bin/bash -l -c 'martilla backup /path/to/backup-config.yml'
Enter fullscreen mode Exit fullscreen mode

We did it! We now have daily backups of a PostgreSQL database that will be retained for 7 days.

Your config file should now look like this:

---
db:
  type: postgres
  options:
    db: my_db
storage:
  type: s3
  options:
    bucket: my_side_project
    filename: backups/backup.sql
    region: 'us-west-2'
    retention: 7
notifiers:
- type: 'slack'
  options:
    slack_channel: '#backups'
    slack_webhook_url: https://hooks.slack.com/services/TTSAMPLEPO/BWEBHOOKOQ/NDQLOLAMSKDMLOLASROFLMDLA
Enter fullscreen mode Exit fullscreen mode

Conclusions

Martilla is a very young project and I'm striving to keep it reliable & easy to configure for simple use cases. Bug reports and any other type of feedback/contribution is greatly appreciated!

Hope I can write about a new set of features soon (aiming for a backup restoration command). Pura Vida!

This post was originally published on visualcosita.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . .