How to sync database backups to Amazon S3

How-to Guides
, ,
#1

Goal

To transfer regular database backups to Amazon S3.

Assumptions

You will need:

  • An SSH key configured on your Platform.sh account
  • A project with a configured database (PostgreSQL in this example)

Problems

While Platform.sh provides managed services that make configuring databases to application very easy, certain projects may require syncing regular backups to a specified location like Amazon S3.

Steps

The following steps refer to PostgreSQL specifically, though they could be generalized to other databases. Consult each service’s documentation for differences, and modify the credentials accordingly.

1. Create sensitive environment variables for AWS credentials

AWS access keys are located in “My Security Credentials” under “Access Keys”. New keys can be created there as well.

platform variable:create --level=project --name=AWS_ACCESS_KEY_ID --value=<KEY_ID> --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true
platform variable:create --level=project --name=AWS_SECRET_ACCESS_KEY --value=<ACCESS_KEY> --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true

2. Create a sensitive environment variable for PostgreSQL

Since PostgreSQL comes with a default password main, create another sensitive environment variable PGPASSWORD for the cron job

platform variable:create --level=project --name=PGPASSWORD --value=main --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true

3. Create a writable mount for the dumps

If one has not already been created, define a mount backups in .platform.app.yaml that the dumps will be written to.

mounts:
    'backups':
        source: local
        source_path: backups

4. Install AWS CLI

Modify the build hook in .platform.app.yaml to install awscli

hooks:
    build: |
        ...
        if [ ! -z "$AWS_ACCESS_KEY_ID" ] && [ ! -z "$AWS_SECRET_ACCESS_KEY" ]; then
            pip install futures
            pip install awscli --upgrade --user 2>/dev/null
        fi

5. Define a cron job

Add a cron to .platform.app.yaml that will dump the database and sync them to S3 every 15 minutes.

crons:
    dumps_to_s3:
        spec: '*/15 * * * *'
        cmd: |
            if [ "$PLATFORM_BRANCH" = master ]; then
                ./cron/forward_dumps_s3.sh
            fi

6. Include a script to perform the dump and sync

Include cron/forward_dumps_s3.sh with the bucket and folder information for the location on S3.

#!/usr/bin/env bash

TODAY_DATE=$(date +%Y-%m-%d-%H:%M:%S)
DUMP_FILE=${TODAY_DATE}-DUMP.sql

DB_HOST="database.internal"
DB_PORT=5432
DB_USER="main"
DUMP_LOC="backups"

AWS_BUCKET=<your bucket name>
AWS_FOLDER=<bucket folder name>

pg_dump --host=$DB_HOST --port=$DB_PORT --username=$DB_USER --file=$DUMP_LOC/$DUMP_FILE

aws s3 sync ~/$DUMP_LOC s3://$AWS_BUCKET/$AWS_FOLDER/

7. Verify

Log into S3 and verify that the sync worked as planned.

43%20AM

Conclusion

Providing AWS credentials via environment variables and installing the AWS CLI, it’s easy to sync regular database backups to S3.

Note: Thanks to Contextual Code for laying the groundwork for this How-to with Store Platform.sh logs on S3.

Transferring backups from P.sh to S3
How to send MySQL database backups to Amazon S3 every six hours