Postgres Backups to S3 with WAL-G and Kamal

How to set up PITR backups for Postgres using Kamal and WAL-G.

The Kamal setup guides I found online focus on S3 backups using pg_dump. You don’t want that for a production database. A better solution is to set up your Postgres database for Point-In-Time Recovery (PITR) using WAL-G or pgBackRest. This means your database is continuously archiving WAL segments to an S3 bucket (roughly every 60 seconds), so you can restore to any point in time.

With LLMs, it’s not that hard to set up. This quick guide focuses on WAL-G, because I’ve found it to be a lot easier to set up than pgBackRest.

Prerequisites

This guide assumes you have a kamal setup and an S3-compatible bucket with appropriate credentials.

Postgres, WAL-G & Kamal

Normally, you’d use a pre-built Postgres docker image with Kamal. However, to use WAL-G, you need a custom image. Your Dockerfile should look like this:

# you may want to use the official postgres image, I prefer the pgvector image for my apps
FROM pgvector/pgvector:pg18

ARG WALG_VERSION=v3.0.8
ARG WALG_ASSET=wal-g-pg-22.04-amd64

RUN set -eux; \
  apt-get update; \
  apt-get install --no-install-recommends -y ca-certificates curl; \
  cd /tmp; \
  curl -fsSLO "https://github.com/wal-g/wal-g/releases/download/${WALG_VERSION}/${WALG_ASSET}"; \
  curl -fsSLO "https://github.com/wal-g/wal-g/releases/download/${WALG_VERSION}/${WALG_ASSET}.sha256"; \
  sha256sum -c "${WALG_ASSET}.sha256"; \
  install -m 0755 "${WALG_ASSET}" /usr/local/bin/wal-g; \
  rm -f "${WALG_ASSET}" "${WALG_ASSET}.sha256"; \
  wal-g --version; \
  rm -rf /var/lib/apt/lists/*

You need to build this image and push it to your container registry. Then, you can use it in your Kamal setup as a database accessory:

aliases:
  # ...
  db_backup: accessory exec --reuse db "wal-g backup-push /var/lib/postgresql/pgdata"
  db_backups: accessory exec --reuse db "wal-g backup-list"

accessories:
  db:
    image: ghcr.io/myuser/postgres-walg:latest # <--- REPLACE WITH YOUR IMAGE URL
    host: xxx.xxx.xxx.xxx
    cmd: "postgres -c wal_level=replica -c archive_mode=on -c archive_timeout=60 -c archive_command='wal-g wal-push %p'"
    env:
      clear:
        POSTGRES_USER: myapp
        POSTGRES_DB: myapp_production
        PGUSER: myapp # lets wal-g connect over the local socket
        PGDATA: /var/lib/postgresql/pgdata
        AWS_REGION: us-east-1
        WALG_COMPRESSION_METHOD: brotli
        WALG_DELTA_MAX_STEPS: "6"

      # Set these in your Kamal secrets:
      secret:
        - POSTGRES_PASSWORD
        - AWS_ACCESS_KEY_ID
        - AWS_SECRET_ACCESS_KEY
        - WALG_S3_PREFIX
    directories:
      - data:/var/lib/postgresql

First Backup

Once your database is running, you should trigger an initial backup with:

kamal db_backup

Check the database accessory logs to see if the backup was successful. You can also list backups with:

kamal db_backups

If everything is set up correctly, your database will be archived to the S3 bucket.

Scheduled Backups & Retention

Restores replay every WAL segment since the last base backup. Without nightly base backups restore time grows unbounded, and without cleanup nothing ever deletes old data from the bucket.

You can add cronjobs to your database host that will schedule nightly backups and clean up old backups. For example:

# Replace `myapp-db` with your accessory's container name (check `docker ps`).
0 3 * * * docker exec myapp-db wal-g backup-push /var/lib/postgresql/pgdata
30 3 * * * docker exec myapp-db wal-g delete retain FULL 2 --confirm

Alternatively, you can run kamal db_backup from a scheduled CI job.

With WALG_DELTA_MAX_STEPS: 6, nightly backups produce one full backup a week; retain FULL 2 keeps the two most recent full chains (~two weeks of point-in-time recovery).

Restoring from Backup

Your next step should be to test if the backups are actually working. One way to do it is to have an LLM write you a script that will restore latest backup to your local development database.

I’m not going to cover it in detail, because it’s just a prompt away, but here’s a quick and dirty version that assumes your local database is running in a docker container (using the same WAL-G image for Postgres).

WARNING: This will destroy your local database!

#!/usr/bin/env bash
# Restore the latest production WAL-G backup into the local development DB.
#
# This is destructive for the local myapp_development database only. It
# does not modify production.
set -euo pipefail

HERE="$(cd "$(dirname "$0")" && pwd)"
REPO="$(cd "$HERE/.." && pwd)"

cd "$REPO"

usage() {
  cat <<'EOF'
Usage:
  script/restore-production-db-to-local --yes

Environment overrides:
  WALG_BACKUP_NAME     Defaults to LATEST
  WALG_IMAGE           Defaults to ghcr.io/!!!MYUSER!!!/postgres-walg:latest
  WALG_PLATFORM        Defaults to linux/amd64
  LOCAL_DATABASE       Defaults to myapp_development
  LOCAL_DATABASE_USER  Defaults to postgres
  LOCAL_DATABASE_PASS  Defaults to postgres
  LOCAL_DATABASE_HOST  Defaults to postgres
  RECOVERY_PGDATA      Defaults to /var/lib/postgresql/pgdata
  KEEP_DUMP            Set to 1 to keep the temporary logical dump

The script reads production WAL-G credentials from .kamal/secrets via 1Password.
It hydrates the WAL-G backup in a temporary Postgres container, then logically
restores myapp_production into the local Compose postgres service.
EOF
}

if [[ "${1:-}" == "-h" || "${1:-}" == "--help" ]]; then
  usage
  exit 0
fi

if [[ "${1:-}" != "--yes" ]]; then
  usage >&2
  echo >&2
  echo "Refusing to replace the local development database without --yes." >&2
  exit 1
fi

require() {
  command -v "$1" >/dev/null 2>&1 || {
    echo "Missing required command: $1" >&2
    exit 1
  }
}

require docker
require zsh

secrets_path_prefix=""
ruby_path=""
if command -v mise >/dev/null 2>&1; then
  ruby_path="$(mise which ruby 2>/dev/null || true)"
  if [[ -n "$ruby_path" ]]; then
    secrets_path_prefix="$(dirname "$ruby_path"):"
  fi
fi

secrets_env="$(
  PATH="${secrets_path_prefix}${PATH}" zsh -lc '
    set -e
    cd "$1"
    if [[ -n "$2" ]]; then
      KAMAL_RUBY="$2"
      kamal() { "$KAMAL_RUBY" bin/kamal "$@"; }
    fi
    source .kamal/secrets >/dev/null

    for key in POSTGRES_PASSWORD AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY WALG_S3_PREFIX; do
      printf "export %s=%q\n" "$key" "${(P)key}"
    done
  ' zsh "$REPO" "$ruby_path"
)"
eval "$secrets_env"

WALG_BACKUP_NAME="${WALG_BACKUP_NAME:-LATEST}"
WALG_IMAGE="${WALG_IMAGE:-ghcr.io/!!!MYUSER!!!/postgres-walg:latest}" # <--- REPLACE WITH YOUR IMAGE URL
WALG_PLATFORM="${WALG_PLATFORM:-linux/amd64}"
RECOVERY_PGDATA="${RECOVERY_PGDATA:-/var/lib/postgresql/pgdata}"
LOCAL_DATABASE="${LOCAL_DATABASE:-myapp_development}"
LOCAL_DATABASE_USER="${LOCAL_DATABASE_USER:-postgres}"
LOCAL_DATABASE_PASS="${LOCAL_DATABASE_PASS:-postgres}"
LOCAL_DATABASE_HOST="${LOCAL_DATABASE_HOST:-postgres}"
RECOVERY_CONTAINER="${RECOVERY_CONTAINER:-myapp-walg-recovery}"
RECOVERY_VOLUME="${RECOVERY_VOLUME:-myapp-walg-recovery-$(date +%Y%m%d%H%M%S)}"
DUMP_PATH="${DUMP_PATH:-$(mktemp -t myapp-production.XXXXXX.dump)}"

compose_project="$(
  docker compose config --format json 2>/dev/null \
    | sed -n 's/^  "name": "\(.*\)",$/\1/p' \
    | head -n 1
)"
compose_project="${compose_project:-myapp}" # <---- REPLACE WITH YOUR COMPOSE PROJECT NAME IF NOT DETECTED
LOCAL_NETWORK="${LOCAL_NETWORK:-${compose_project}_default}"

cleanup() {
  docker rm -f "$RECOVERY_CONTAINER" >/dev/null 2>&1 || true
  docker volume rm "$RECOVERY_VOLUME" >/dev/null 2>&1 || true

  if [[ "${KEEP_DUMP:-0}" != "1" ]]; then
    rm -f "$DUMP_PATH"
  else
    echo "Kept logical dump at $DUMP_PATH"
  fi
}
trap cleanup EXIT

echo "==> restoring production backup: $WALG_BACKUP_NAME"
echo "==> local database: $LOCAL_DATABASE"
echo "==> recovery volume: $RECOVERY_VOLUME"

docker volume create "$RECOVERY_VOLUME" >/dev/null

echo "==> fetching WAL-G backup into temporary volume"
docker run --rm \
  --platform "$WALG_PLATFORM" \
  -e WALG_S3_PREFIX \
  -e AWS_REGION="${AWS_REGION:-us-east-1}" \
  -e AWS_ACCESS_KEY_ID \
  -e AWS_SECRET_ACCESS_KEY \
  -v "${RECOVERY_VOLUME}:/var/lib/postgresql" \
  --entrypoint bash \
  "$WALG_IMAGE" \
  -lc "
    set -euo pipefail
    mkdir -p \"$(dirname "$RECOVERY_PGDATA")\"
    wal-g backup-fetch \"$RECOVERY_PGDATA\" \"$WALG_BACKUP_NAME\"
    chown -R postgres:postgres \"$RECOVERY_PGDATA\"
    chmod 700 \"$RECOVERY_PGDATA\"
    cat >> \"$RECOVERY_PGDATA/postgresql.auto.conf\" <<'POSTGRESQL_AUTO_CONF'
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_action = 'promote'
POSTGRESQL_AUTO_CONF
    touch \"$RECOVERY_PGDATA/recovery.signal\"
  "

echo "==> replaying WAL in temporary Postgres container"
docker run -d \
  --platform "$WALG_PLATFORM" \
  --name "$RECOVERY_CONTAINER" \
  -e PGDATA="$RECOVERY_PGDATA" \
  -e WALG_S3_PREFIX \
  -e AWS_REGION="${AWS_REGION:-us-east-1}" \
  -e AWS_ACCESS_KEY_ID \
  -e AWS_SECRET_ACCESS_KEY \
  -v "${RECOVERY_VOLUME}:/var/lib/postgresql" \
  "$WALG_IMAGE" >/dev/null

recovered=false
for _ in $(seq 1 180); do
  if docker exec \
    -e PGPASSWORD="$POSTGRES_PASSWORD" \
    "$RECOVERY_CONTAINER" \
    psql -U myapp -d myapp_production -Atc 'select pg_is_in_recovery()' 2>/dev/null \
      | grep -qx 'f'; then
    recovered=true
    break
  fi

  sleep 2
done

if [[ "$recovered" != "true" ]]; then
  echo "Recovery did not finish in time. Recent logs:" >&2
  docker logs --tail=200 "$RECOVERY_CONTAINER" >&2 || true
  exit 1
fi

echo "==> creating logical dump from recovered production database"
docker exec \
  -e PGPASSWORD="$POSTGRES_PASSWORD" \
  "$RECOVERY_CONTAINER" \
  pg_dump -U myapp -d myapp_production -Fc --no-owner --no-acl > "$DUMP_PATH"

echo "==> starting local postgres"
docker compose up -d postgres >/dev/null

local_ready=false
for _ in $(seq 1 60); do
  if docker compose exec -T postgres pg_isready -U "$LOCAL_DATABASE_USER" -d postgres >/dev/null 2>&1; then
    local_ready=true
    break
  fi

  sleep 2
done

if [[ "$local_ready" != "true" ]]; then
  echo "Local postgres did not become ready. Recent logs:" >&2
  docker compose logs --tail=200 postgres >&2 || true
  exit 1
fi

echo "==> replacing local database $LOCAL_DATABASE"
docker compose exec -T \
  -e PGPASSWORD="$LOCAL_DATABASE_PASS" \
  postgres \
  psql -U "$LOCAL_DATABASE_USER" -d postgres -v ON_ERROR_STOP=1 <<SQL
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '$LOCAL_DATABASE'
  AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS "$LOCAL_DATABASE";
CREATE DATABASE "$LOCAL_DATABASE" OWNER "$LOCAL_DATABASE_USER";
SQL

dump_dir="$(dirname "$DUMP_PATH")"
dump_file="$(basename "$DUMP_PATH")"

echo "==> restoring logical dump into local database"
docker run --rm \
  --platform "$WALG_PLATFORM" \
  --network "$LOCAL_NETWORK" \
  -e PGPASSWORD="$LOCAL_DATABASE_PASS" \
  -v "${dump_dir}:/dump:ro" \
  "$WALG_IMAGE" \
  pg_restore \
    -h "$LOCAL_DATABASE_HOST" \
    -U "$LOCAL_DATABASE_USER" \
    -d "$LOCAL_DATABASE" \
    --no-owner \
    --no-acl \
    "/dump/$dump_file"

echo "==> marking restored database as development"
docker compose exec -T \
  -e PGPASSWORD="$LOCAL_DATABASE_PASS" \
  postgres \
  psql -U "$LOCAL_DATABASE_USER" -d "$LOCAL_DATABASE" -v ON_ERROR_STOP=1 <<'SQL'
DO $$
BEGIN
  IF to_regclass('public.ar_internal_metadata') IS NOT NULL THEN
    INSERT INTO ar_internal_metadata (key, value, created_at, updated_at)
    VALUES ('environment', 'development', NOW(), NOW())
    ON CONFLICT (key)
    DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at;
  END IF;
END
$$;
SQL

echo "==> restored local database"
docker compose exec -T \
  -e PGPASSWORD="$LOCAL_DATABASE_PASS" \
  postgres \
  psql -U "$LOCAL_DATABASE_USER" -d "$LOCAL_DATABASE" -c '\dt'