How To Use GitHub Actions To Schedule PostgreSQL Backups
In this article I’ll take you through how I created a scheduled GitHub Action that connects to my Neon PostgreSQL database, creates a backup using pg_dump, and uploads it to an AWS S3 Bucket every night at midnight (ET).
There are three main components to creating this kind of scheduled backup:
- AWS
- You’ll need to know your AWS Account ID and have permissions that allow you to create Roles, Identity Providers, S3 Buckets, and be able to update Bucket policies.
- PostgreSQL Database
- You’ll need to have the connection string for your database, know which region the database is deployed, and which version of PostgreSQL your database uses.
- GitHub Action
- To write this Action you’ll need to have access to both Actions and Settings > Secrets, along with variables for the GitHub Repository that you’d like to run the Action from.
The Finished GitHub Action
Here’s a link to the finished Action I’m using to back up the database used by my site. The Action backs up my Neon database every night at midnight and deletes any previous .sql.gz files from the S3 Bucket.
Later on in this article, I’ll explain what each of the steps do and where you’ll need to make changes so that it works with your database, GitHub Repository and AWS Credentials.
But first…
Setup Identity Provider, Role and S3 Bucket
There are three parts to the AWS setup, they are:
- Creating an OIDC Identity Provider.
- Creating a Role.
- Creating an S3 Bucket and updating the S3 Bucket policy.
Add an Identity Provider
An OIDC (OpenID Connect) Identity Provider (IdP) in AWS is a third-party service that handles authentication. GitHub must be added as an identity provider to allow the Action to use your AWS credentials.
To create a new Identity Provider, navigate to IAM > Access Management > Identity Providers, and click Add provider.
On the next screen, select OpenID Connect and add the following to the Provider URL and Audience fields:
- Provider URL: https://token.actions.githubusercontent.com
- Audience:
sts.amazonaws.com
When you’re done, click Add Provider.
You should now see that this provider is visible in the list under IAM > Access Management > Identity Providers.
Create Role
A Role is an identity that you can assume to obtain temporary security credentials for specific tasks or actions within AWS. Roles are used to delegate permissions and grant access to AWS services without the need for credentials like passwords or access keys.
To create a new Identity Provider, navigate to IAM > Access Management > Roles, and click Create role.
On the next screen, create a Trusted Identity for the Role.
Select Trusted Identity
On this screen select Web Identity, then select token.actions.githubusercontent.com from the Web Identity dropdown menu.
Once you select the Identity Provider, you’ll be shown a number of fields to fill out. Select sts.amazonaws.com from the Audience dropdown menu, then fill out the GitHub repository details as per your requirements.
When you’re done, click Next.
For reference, the options shown in the image below are for the following repository: https://github.com/PaulieScanlon/paulie-dev-2023
Add Permissions — Skip
You can skip selecting anything from this screen and hit Next to continue.
Name, Review and Create
On this screen, give the Role a name and description. You’ll use the role name in the code for the GitHub Action (I’ve named mine, paulie-dev-2023-github-action). Consider naming the role specifically to avoid confusion later on.
When you’re ready, hit Create role.
Setup S3 Bucket
There are two parts to creating an S3 Bucket:
- Creating the Bucket.
- Updating the Bucket policy.
Creating the Bucket
AWS S3 (Amazon Simple Storage Service) buckets are storage containers used to store objects in Amazon’s cloud storage service. An S3 bucket can store any amount of data, from files and documents to images and videos, or in the case of a database backup, a .gz (GNU zip) file.
To create a new Bucket, navigate to S3 > Buckets, and click Create bucket.
On the next screen, select General Purpose for the Bucket Type and then give your Bucket a name.
The most important thing to notice on this screen is which region you’re creating the Bucket for.
As mentioned, my database provider is Neon, which deploys databases to AWS. In my case, it’s important to create my S3 Bucket in the same region as my database (us-east-1 N. Virginia) so that I won’t incur any egress charges when performing backups. That’s because when performing a database dump, you’re extracting a large amount of data from storage; and by uploading it to an S3 bucket, you’re inserting a large amount of data back into storage. By ensuring the AWS regions are the same, not only will the data never leave the AWS network, it will never leave the AWS region — and thus, no egress charges and no cost.
Updating the Bucket Policy
To ensure the Role being used in the GitHub Action can perform actions on the S3 Bucket, you’ll need to update the Bucket policy.
Select your Bucket then select the Permissions tab and click Edit.
You can now add the following policy, which grants the Role you created earlier access to perform S3 List, Get, Put and Delete actions. You’ll need to list the Bucket name twice under Resources — one without the trailing slash, one with a trailing slash and wildcard. Without both links, the Action will fail (I don’t know why, sorry).
From the snippet above, replace the Role name (paulie-dev-2023-github-action) with your Role name and replace the S3 Bucket name (paulie-dev-db-backup) with your S3 Bucket name.
When you’re ready, click Save changes.
Setup GitHub Secrets
There are a number of sensitive variables used in the GitHub Action. In my case, the repository is public and naturally I don’t want to reveal these in the Action’s code. To avoid this, I’m using GitHub Secrets.
To do the same in your GitHub account, navigate to Settings > Secrets and variables > Actions, and add the following variables.
-
AWS_ACCOUNT_ID- This can be found by clicking your name in the AWS navigation menu, the first item in the list will be your AWS Account ID.
DATABASE_URL- This should be the full PostgreSQL connection string: e.g. postgres://paulie:123@abc.us-east-1.aws.neon.tech/paulie-db
S3_BUCKET_URL- This is the name of the S3 Bucket created earlier, mine is paulie-dev-db-backup.
Creating the GitHub Action
With the AWS Role, Identity Provider S3 Bucket, S3 Bucket policy and GitHub Secrets all set up, you can now create the GitHub Action.
In your repository, create a new directory and name it .github. Inside this directory, create another directory and name it workflows. Then create a new file (I’ve named mine db-backup.yml).
Add the following code to the .yml file. The changes you might need to make are to PG_VERSION (which is the PostgreSQL version of your database) and the AWS_REGION (the region of the S3 Bucket).
You might also like to rename the Action. Mine is named Backup Neon | paulie-dev (us-east-1) and the job I’ve named db-backup.
GitHub Action Explained
Below are each of the steps contained within the Action, together with an explanation about what each one does.
on:
I’ve used a schedule that runs each night, at midnight in the US Eastern Time Zone. You can read more about schedules in the GitHub docs, and the actual syntax is known as POSIX cron syntax.
workflow_dispatch is an event in GitHub Actions that allows you to manually trigger a workflow run through the GitHub Actions user interface, and is particularly useful for development and testing purposes.
jobs:
This will be the name of the job that runs within the Action.
permissions:
id-token: write grants permission to write OIDC tokens, enabling the job to authenticate with external services, e.g AWS.
env:
These variables are a combination of GitHub Secrets and variables that aren’t sensitive but are used in the Action’s code.
Install PostgreSQL
I’m installing PostgreSQL from an Apt repository, which contains the relevant PostgreSQL package suitable for use in Ubuntu environments. The PG_VERSION environment variable is used in the install command to ensure that I’m installing PostgreSQL version 16.
Get Timestamp
To create backups with a date as part of the file name, I’ve created a step that will create a timestamp and save it to the Action’s env. I can later refer to this value using env.TIMESTAMP. Here’s a helpful resource for understanding the Unix timestamp format options: Print or set system date and time.
Using the same formatting I’ve used would result in a new file named something like this: 03-June-2024@19:17:13.sql.gz
Run pg_dump
This step was actually pretty frustrating. To ensure you’re using the version of PostgreSQL you’ve installed (in my case 16), I have to access pg_dump directly from the binaries. Not including this path results in the Action attempting to use an incorrect version of PostgreSQL.
Configure AWS Credentials
I’m using the configure-aws-credentials action from AWS to set up the necessary credentials for the GitHub Action to interact with AWS services, using the Role defined earlier.
Empty Bucket (Optional)
This step is optional, and will empty the S3 Bucket before writing a new file. In my case, I don’t need more than the last backup — but you might want to keep this rolling and save all previous backups. If so, you can safely remove this step. You might also want to remove s3:ListBucket, s3:GetObject and s3:DeleteObject from the list of Actions in the S3 Bucket policy.
Upload to Bucket
Finally, the upload to S3. Quite simply, using the AWS cp command I copy the newly created .sql.gz file and upload it to the S3_BUCKET_URL.
Finished
And that’s it. I now have a scheduled GitHub Action that runs each night at midnight, which performs a full backup of data and schema from my Neon PostgreSQL database and uploads it to an S3 Bucket for safekeeping — lovely stuff!
