Recently I wanted to set up nightly backups of my Postgres databases hosted on Neon.tech. Yes, they have backups and PITR but I knew I’d feel better if I had backups just in case. My business runs on AWS and so S3 was the obvious destination. I took this as an opportunity to look into what Amazon wants you to use for authenticating instead of having to click the shameful “Other” button (aka “Please just give my access id and secret that I’ve been using for over a decade”).
NOTE: Before I get started I want to say I’m sure nothing is this blog post is new or groundbreaking. I just wanted to document how I solved it in case it helps someone in the future.
AWS Setup
First you’ll need to create an S3 Bucket, this is incredibly straightforward and I left all the defaults as-is except I turned on versioning. I don’t plan to ever upload a file to an existing key but there isn’t a way to easily restrict to add-only (no overwrite) so I figure versions are a safe thing to turn on that might save me one day. Hopefully they are never needed.
Once you have your S3 bucket let’s go over to the “Management” tab and click on “Create lifecycle rule”. This isn’t strictly necessary but I wanted to move older backups to cheaper S3 storage after a certain amount of time.
Here is the rule that I created. I wanted files to move to IA after 30 days and Glacier Instant Retrieval after 60. Also I moved any noncurrent versions to Glacier Instant Retrieval after 30 days.
Once that’s done head over to IAM to add an Identity Provider. This is so that GitHub can assume the role without having to add in any access key/secret to your GitHub Actions Secrets. Go to “Identity Providers” then click “Add provider” and fill in the following:
- Select “OpenID Connect”
- Provider Url:
https://token.actions.githubusercontent.com - Audience:
sts.amazonaws.com
Now that we have that configured we are going to create a new role with a “Custom trust policy”. Edit the one below to match your account and GitHub user/repo.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Federated": "arn:aws:iam::YOUR_AWS_ACCOUNT_ID:oidc-provider/token.actions.githubusercontent.com"
},
"Action": "sts:AssumeRoleWithWebIdentity",
"Condition": {
"StringEquals": {
"token.actions.githubusercontent.com:aud": "sts.amazonaws.com",
"token.actions.githubusercontent.com:sub": "repo:USERNAME/REPO_NAME:ref:refs/heads/main"
}
}
}
]
}Once the role is created you’ll want to attach an inline policy, here is the one I used:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:PutObject",
"s3:AbortMultipartUpload",
"s3:ListMultipartUploadParts",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:ListMultiRegionAccessPoints"
],
"Resource": [
"arn:aws:s3:::YOUR_S3_BUCKET/*",
"arn:aws:s3:::YOUR_S3_BUCKET/"
]
}
]
}Now that you have your role and identity provider in place we can move on to GitHub.
GitHub Setup
Once all your AWS stuff is done we can setup our GitHub workflow. Copy the following file into the root of your repo at .github/workflows/backup.yml
name: Database Backup and Upload
on:
schedule:
- cron: '33 8 * * *' # Run at 8:33am UTC every day (Will be the middle of the night in the US)
workflow_dispatch: # Allows manual triggering from GitHub UI
permissions:
id-token: write
contents: read
jobs:
backup-and-upload:
runs-on: ubuntu-latest
strategy:
matrix:
include:
- prefix: PREFIX_1 # I had multiple databases I wanted backed up and each had a secret like:
- prefix: PREFIX_2 # "PREFIX_2_BACKUP_DB_CONNECTION". If you only have 1 DB you don't need this
steps:
- name: Checkout code
uses: actions/checkout@v3
- name: Install PostgreSQL client
run: |
sudo apt install -y postgresql-common
yes '' | sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt-get install -y postgresql-client-16
- name: Set Timestamp
run: echo "TIMESTAMP=$(date -u +'%Y-%m-%d-%H-%M-%S')" >> $GITHUB_ENV
- name: Dump database
run: |
/usr/lib/postgresql/16/bin/pg_dump ${{ secrets[format('{0}_BACKUP_DB_CONNECTION', matrix.prefix)] }} | gzip > "${TIMESTAMP}.sql.gz"
- name: Configure AWS credentials from Action OIDC
uses: aws-actions/configure-aws-credentials@v1
with:
aws-region: us-east-1
role-to-assume: arn:aws:iam::AWS_ACCOUNT_ID:role/ROLE_YOU_CREATED_ABOVE
role-session-name: GitHubActionSession
- name: Upload backup to S3
run: |
YEAR_MONTH=$(date -u +"%Y/%m")
aws s3 cp "${TIMESTAMP}.sql.gz" s3://YOUR_S3_BUCKET_NAME/${{ matrix.prefix }}/database/${YEAR_MONTH}/
This will run at 8:33am UTC every day and backup each of my databases to S3 in a folder named after the “prefix”. As I explain above, I have a secret per DB and the prefix is used to build the secret name here:
${{ secrets[format('{0}_BACKUP_DB_CONNECTION', matrix.prefix)] }}
If you just have 1 database you can hardcode the secret there and remove all the prefix/matrix logic and pass your secret connection string to pg_dump directly. Just a note, you cannot use a secret value as a matrix value, it will throw an error. My way around this was just specify my “prefix” and then build the secret name later.
Why “8:33”? Well GitHub says that Action schedules might be delayed if there are a lot of jobs running at that time so choosing to run “on the hour” is probably a contentious time, you could pick any time or have it run on the hour and deal with delays.
That’s it!
Like I said at the top, this isn’t rocket science or anything that hasn’t been done before, I just wanted to document how I accomplished it. I hope you found it useful!
Josh Strange 