Backing up databases to a remote source can sometimes be a time-consuming or daunting process. Fortunately, there exists a nice solution with Postgres with logrotate, pg_dump, and the AWS S3 CLI. I implemented this solution, and it has been working great, to back up a Mattermost (read Slack clone) database.

In this example, we will be creating a daily backup and posting it to an AWS S3 bucket we have created. First, if you’re not familiar with logrotate, Rackspace has a great beginner’s guide I recommend checking out. Its also useful for understanding all of the config options that you have at your disposal as well.

Taken from the guide above:

The logrotate utility makes log rotation easy. “Log rotation” refers to the practice of archiving an application’s current log, starting a fresh log, and deleting older logs. The system usually runs logrotate once a day, and when it runs it checks rules that can be customized on a per-directory or per-log basis.

The “hard” part of this whole automated backup process is creating the logrotate config file. First, create a file in the /etc/logrotate.d/ directory. I’ve named mine mm-bkup.

Here is what my mm-bkup file looks like, with annotations, to help better explain what each config option is doing. Once again, this is file is /etc/logrotate.d/mm-bkup and can have different config options depending on what you want.

# where to archive the logs (backups)
/var/backups/postgres/postgres.pgdump.gz {
  daily    # how often to rotate (backup)
  missingok    # ignores error if the directory does not exist
  rotate 7    # how many archived logs (backups) are kept locally before logrotate starts deleting the older ones
  nocompress    # old versions of log files are not compressed
  create 640 postgres postgres    # sets permissions on the newly created log file using [mode] [owner] [group] syntax
  postrotate    # start script for after the log file is created
  sudo -u postgres pg_dump mattermost > /var/backups/postgres/postgres.pgdump    # dump the mattermost database via pg_dump
  gzip -9f /var/backups/postgres/postgres.pgdump    # gzip (compress) the db dump at maximum compression
  /usr/bin/s3cmd sync /var/backups/postgres/* s3://YOUR_S3_URL    # post the backups to your s3 bucket
  endscript    # end script for after the log file is created

And that’s all there is to it! The /var/backups/postgres directory will locally have the most recent, uncompressed backups and our S3 bucket will have a larger history of compressed backups.

For further reading on some of the tools used here, check out these links:

Sam Rubin

A full stack software engineer located in Arlington, Virginia

smrubin sammrubin