How to automate Postgres backups on Fly.io

This article will look at how to back up your Postgres database on fly.io. Fly.io is a platform that allows you to deploy your apps close to your users.

You can deploy your database to fly in minutes, but unfortunately, you will have to manage scaling, upgrades, security patches, a backup plan, monitoring, and recovery.

Self Managed Databases For Production Apps

Make sure you have all monitoring & data recovery procedures in place when you are managing your databases. Something WILL go wrong, at the worst possible time! You need to have:

Monitoring to alert you when something goes wrong.

A recovery plan - i.e. scripts that you can run. You don’t want to be googling this during a crisis.Prerequisites

This article assumes you have already installed the Flyctl CLI tool and have a Postgres cluster up and running. The instructions for how to get started are on the getting started docs linked at the end of this article.

Using volumes and snapshots

Fly.io already performs daily backups of your volumes on Postgres and saves them for 7 days. This is ok for applications that do not need more regular backups.

Here are a few useful commands for listing your volume snapshots and restoring your database from a snapshot.

# list your volumes
$ fly volumes list -a <postgres-app-name>

ID                   NAME    SIZE REGION ATTACHED VM CREATED AT
vol_c345grd003vf72qy pg_data 10GB ord    se561cf5d    1 weeks ago

Use the volume-id to list the snapshots and restore them manually.

$ fly volumes snapshots list <volume-id>

ID                  SIZE   CREATED AT
vs_1Bs34jja82qQ78si 32 MiB 3 hours ago
vs_sjy7ALnds19daU04 38 MiB 1 day ago

Use the snapshot id from the output above to restore your Postgres app from a snapshot.

$ fly postgres create --snapshot-id <snapshot-id>

This is ok but doesn’t give us much control. Ideally, we should be able to create scheduled, automated backups. The last thing you want when your app is down and customers are upset is to be searching ‘how to restore..’ on google. How can we improve this?

Connect to the database and backup locally

We create a backup manually. This gives us some flexibility for when we backup the database. There are a few ways to connect to the cluster.

# fly's postgres utitility
$ flyctl postgres connect -a <postgres-app-name>

# proxy forwarding
$ flyctl proxy 5432 -a <postgres-app-name>

• The first command uses the flyctl pg utility that can be used to manage your Postgres cluster. Bookmark this link. It has all the commands you need to attach to a pg cluster, view configuration, restart, etc. Using this method will give you access to a psql shell.

• The second method will forward the server port to your local system where you can then connect with a GUI like DBeaver or Datagrip and perform a database backup manually or use psql locally to do a backup.

You will probably already have something listening on port 5432. You can run this instead to connect to port localhost:5434 for example.

$ flyctl proxy 5434:5432 -a <postgres-app-name>

Here is an example of how you can connect from the DBeaver interface.

Backup the database

Now that we are connected, we can perform the backup using the normal Postgres utilities.

$ pg_dump -h localhost -p 5434 -U postgres your-db-name > output-backup.sql

• Here we are using the pg_dump utility to connect and backup our database the file backup.sql .

• You will be prompted for a password. Use the password that was supplied when you first created your database.

Automating the process

We will create a python script to perform the backups and save them to an S3 bucket. We will use GitHub actions to run the script on a schedule. Let’s install dependencies and create a script that can be used to connect to the app and deploy the database.

Install dependencies

Create and activate a virtual environment

# Create and activate a virtual env
$ python3 -m venv .venv
$ source .venv/bin/activate

Add a requirements.txt file for your dependencies.

sh==1.14.0
typer[all]==0.7.0
boto3==1.26.3

• SH - subprocess replacement

• Typer - is a library for creating small CLI (Command Line Programs).

• boto3 - is a python library for communicating with S3-compatible storage.

Create the CLI application

Here is what it does:

• connects to the database using fly’s cli.

• create a backup using pg_dump

• upload the backup to S3.

• close connections

import os
import time
from pathlib import Path

import boto3
import environ
import sh
import typer
from botocore.exceptions import ClientError
from rich import print as rprint


# create a typer app
app = typer.Typer()


@app.command()
def fly_db_connect(app_name="app-name", bg: int = 0):
    """
    Connect to the database
    :param app_name: The name of the app
    :param bg: Run in the background (0-false, 1-true)
    """
    rprint(f"[green]Connecting to {app_name}, {bg}")
    _bg = bg == 1
    rprint(f"[green]Connecting to the database: Running in the background: {_bg}")
    try:
        return sh.fly("proxy", "5433:5432", app=app_name, _out=rprint, _bg=_bg)
    except sh.ErrorReturnCode as e:
        rprint(e)


@app.command()
def fly_db_backup(
    password=None,
    db_name="db",
    port=5433,
    user="postgres",
    host="localhost",
    app_name="app-name",
):
    """Connect to fly.io and backup the database"""
    password = password or os.getenv("PGPASSWORD")
    db_connection = None
    try:
        rprint("[green] Backing up the database")
        # start timer
        start = time.time()
        db_connection = fly_db_connect(app_name=app_name, bg=1)
        # wait for the connection here
        time.sleep(3)
        
        filename = f"dbbackup-vanty-{datetime.now().timestamp()}.sql"
        rprint(f"[green]Backing up the database to {filename}, please wait...")
        process = sh.pg_dump(
            "-h",
            host,
            "-p",
            port,
            "-U",
            user,
            "-f",
            filename,
            db_name,
            _out=rprint,
            _in=password,
            _bg=False,
        )
        rprint(process)

        rprint("[green] backup complete, uploading to sq")
        upload_file(filename)

        # end timer
        end = time.time()
        rprint(f"[green] Total runtime of the program is [red] {end - start}")
        db_connection.terminate()

    except sh.ErrorReturnCode as e:
        rprint(e)
        if db_connection:
            db_connection.terminate()

• app = typer.Typer() - creates a new Typer app that can be used for adding sub-commands.

• The fly-db-connect sub-command connects to your fly Postgres application and fly-db-backup will back up the database to the local file system. You will notice below that the dashes are used (instead of underscores) when referring to the commands in the cli.

• rprint - pretty print the output using rich.

We have one more sub-command to add for uploading files to S3 but before that, let’s do a quick sanity check.

# check we are on the right track
$ python3 -m cli --help

Usage: python -m cli [OPTIONS] COMMAND [ARGS]...                                                                                                                                                         
                                                                                                                                                                                                          
╭─ Options ──────────────────────────────────────────────────────────────
│ --install-completion          Install completion for the current shell.                                                                                                                                
│ --show-completion             Show completion for the current shell...│
│ --help                        Show this message and exit.                                                                                                                                              │
╰───────────────────────────────────────────────────────────────────────
╭─ Commands ────────────────────────────────────────────────────────────
│ fly-db-backup                  Connect to fly.io and backup the db                                                                                                        
│ fly-db-connect                 Connect to the database                                                                                                                                                                                                                                             
╰───────────────────────────────────────────────────────────────────────

Running the cli file with the --help flag displays a colorful output showing the available documented commands.

Let’s round this up by adding a function to upload files to S3. This can be any S3-compatible platform.

import boto3
.....


@app.command()
def upload_file(file_name, bucket):
    """
    Upload a file to an S3 bucket

    :param file_name: File to upload
    :param bucket: Bucket to upload to
    :return: True if file was uploaded, else False
    """

    # Upload the file
    rprint(f"[green] Uploading {file_name} to {bucket}")
    s3_client = boto3.client(
        "s3",
        # endpoint_url=f"https://{account_id}.r2.cloudflarestorage.com",
        aws_access_key_id=os.getenv("S3_ACCESS_KEY"),
        aws_secret_access_key=os.getenv("S3_SECRET_ACCESS_KEY"),
    )
    try:
        s3_client.upload_file(file_name, bucket, file_name)
    except ClientError as e:
        print(e)
        return False
    return True

This is a simple function that will upload the file to a bucket.

• endpoint_url - You can use any S3-compatible service. Cloudflare’s R2 or Backblaze for example.

You will notice we did not include credentials in the function. These should be included as variables in the environment. If you are running this locally, you can export the variables to your shell.

export S3_ACCESS_KEY=your-key S3_SECRET_ACCESS_KEY=your-id

GitHub actions

The final step is to create a GitHub action so we can run this script on a schedule. Before we start, a quick recap of the files we should already have.

├── .github
│   └── workflows
├── cli.py
└── requirements.txt

In the .github/workflows folder, add the following backup.yml file.

on:
  schedule:
    - cron: '0 */6 * * *'

  push:
    branches: ["main" ]

jobs:
  backup-db:
    name: Backup DB
    runs-on: ubuntu-latest
    steps:

      - uses: actions/checkout@v2
      - uses: superfly/flyctl-actions/setup-flyctl@master

      - name: Set up Python 3.10
        uses: actions/setup-python@v2
        with:
          python-version: 3.9

      - name: Dependencies
        run: |
          apt-get update
          apt-get install --yes postgresql-client
          pip install -r requirements.txt

      - name: Backup
        run: python -m cli fly-backup-db
          env:
            FLY_API_TOKEN: ${{ secrets.FLY_API_TOKEN }}
            PGPASSWORD: ${{ secrets.PGPASSWORD }}
            S3_ACCESS_KEY: ${{ secrets.S3_ACCESS_KEY }}
            S3_SECRET_ACCESS_KEY: ${{ secrets.S3_SECRET_ACCESS_KEY }}

A lot going on here, let’s go over the important bits.

1. First, we add the triggers. This section defines which events will trigger the action. In our case, we are running the script every six hours and on every push to the main branch.

on:
  schedule:
    - cron: '0 */6 * * *'

  push:
    branches: ["main" ]

2. Create our Backup DB job, and create a few steps to install dependencies. The flyctl-actions Github action includes the flyctl cli. We also include an action to install python and other dependencies

jobs:
  backup-db:
    name: Backup DB
    runs-on: ubuntu-latest
    steps:      
      - uses: actions/checkout@v2
      - uses: superfly/flyctl-actions/setup-flyctl@master

      - name: Set up Python 3.10
        uses: actions/setup-python@v2
        with:
          python-version: 3.9

      - name: Dependencies
        run: |
          apt-get update
          apt-get install --yes postgresql-client
          pip install -r requirements.txt

3. Finally, we add the step to run the script. This step also includes the environment variables that should be available when the script runs. These should be added to your project secrets.

   yaml

       
    - name: Backup
         run: python -m cli fly-backup-db
           env:
             FLY_API_TOKEN: ${{ secrets.FLY_API_TOKEN }}
             PGPASSWORD: ${{ secrets.PGPASSWORD }}
             S3_ACCESS_KEY: ${{ secrets.S3_ACCESS_KEY }}
             S3_SECRET_ACCESS_KEY: ${{ secrets.S3_SECRET_ACCESS_KEY }}
   

Managing your databases

In this section, we will take a quick look at other utilities for managing your database.

Scaling your database

This assumes your database was created with v2 of fly’s cli which uses machines instead of Nomad. To scale your database you need to scale the VM. First, fetch the list of machines for your app.

# fetch the list of machines 
$ fly machines list --app <app-name>

>>
ID              NAME            STATE   REGION  IMAGE                                   
5683075a740428  wild-dew-7996   started ams     flyio/postgres:14.4 (v0.0.32)   

Scale the memory to 1GB.

$ fly machine update 5683075a740428 --memory 1024 --app pg-test

Here is the reference for other machine update commands.

High availability

Fly provides high availability and global replication through stolon, a cloud-native, high-availability manager.

$ fly machine clone 5683075a740428 --region ord --app <app-name>

Conclusion

And that’s it, you can push the code to a repository on GitHub to test on a live environment. These files can be included as part of a larger project or run as a small self-contained project for your backups.

Useful links

• A previous post on GitHub actions and testing them locally

• Fly.io’s documentation on Postgres

• Introduction to creating python CLI applications with Typer