Back up Fly Postgres with GitHub Actions

Fly Postgres stores its data on volumes, and the fine folks at Fly.io set up automatic snapshots for us. However, I sleep better at night knowing that my databases are backed up with pg_dump.

I found surprisingly few results when I searched for a quick solution to this problem. One of the solutions was a python script, which seemed overkill for such a simple task, but it was a useful starting point. In the end, I came up with a relatively short backup.yml that does the job:

	name: Back up database
	run-name: Task
	on:
	schedule:
	- cron: '0 4 * * *'
	workflow_dispatch:
	jobs:
	backup:
	runs-on: ubuntu-latest
	env:
	FLY_API_TOKEN: ${{ secrets.FLY_API_TOKEN }}
	PGUSER: postgres
	PGPASSWORD: ${{ secrets.PGPASSWORD }}
	PGDATABASE: your_database_name
	PGHOST: localhost
	PGPORT: 5555
	steps:
	- uses: s3-actions/s3cmd@v1.2.0
	with:
	provider: aws
	region: eu-central-1
	access_key: ${{ secrets.S3_ACCESS_KEY }}
	secret_key: ${{ secrets.S3_SECRET_KEY }}
	- uses: superfly/flyctl-actions/setup-flyctl@master
	- name: Set filename
	run: echo "filename=db-$(date -u +"%Y-%m-%d-%H%M%S").dump" >> $GITHUB_ENV
	- name: Dump database and upload to S3
	run: |
	flyctl proxy 5555:5432 -a your-fly-db-app-name &
	sleep 3
	echo Dumping ...
	pg_dump -Fc -f ${{ env.filename }}
	ls
	s3cmd put --acl-private ${{ env.filename }} s3://your-s3-bucket/your-path/${{ env.filename }}

view raw backup.yml hosted with ❤ by GitHub

The task will run every day at 4AM. workflow_dispatch is there so that the task can be run manually as well.

Don't forget to set the correct region, update PGUSERand PGDATABASE, as well as replace your-fly-db-app-name, your-s3-bucket, and your-path.

You will also need to visit your repository settings and set the following secrets:

• FLY_API_TOKEN — A Fly personal access token (generate one here)
• PGPASSWORD — the password for the database being backed up

And finally, don't forget: a backup is not a backup if you've never tried restoring it!

Comments

[Zek]

This was helpful for me. I've made some modifications to install a specific version of pg_dump (it must match the version of Postgres deployed on Fly), as well as gzip the resulting file before uploading.

Gist