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 }} |
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 PGUSER
and 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!
Top comments (1)
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