Postgres Backups to Object Storage, With Restore Drills

Decide how much data you can afford to lose

A migration deletes records, and your backup folder contains a dump nobody has opened. Fix that by treating restoration as part of the backup procedure. An uploaded file becomes useful recovery evidence only when you can retrieve it and rebuild a database from it.

This procedure targets Ubuntu 24.04 LTS with PostgreSQL already running directly on the VPS. It assumes local socket access through the operating-system account postgres, and uses app as the example database. Replace that database name before running the backup; containerized and remote databases need different connection settings.

Write down the oldest recoverable state you can tolerate and how long the application can remain unavailable. A daily logical dump can lose changes made after its snapshot, with a longer gap if jobs fail. If that is unacceptable, use physical backups with continuous WAL archiving and point-in-time recovery instead of shortening a cron interval indefinitely.

We recommend custom-format dumps for databases whose full export and restore fit your operating window. PostgreSQL takes a consistent snapshot while normal reads and writes continue, although conflicting schema changes can interfere. A database dump does not include cluster-wide roles or your application files. See the pg_dump documentation and keep a separate whole-server backup plan.

Install the database tools and AWS CLI

Install PostgreSQL clients through Ubuntu packages and the AWS CLI through its official Linux installer. This avoids depending on an awscli package being available in your configured Ubuntu repositories. The client package does not install a new database server.

set -euo pipefail
sudo env DEBIAN_FRONTEND=noninteractive apt-get update
sudo env DEBIAN_FRONTEND=noninteractive apt-get install -y ca-certificates curl unzip groff less postgresql-client cron util-linux
sudo install -d -o root -g root -m 0700 /etc/postgres-backup /var/lib/postgres-backup
command -v pg_dump pg_restore flock

The installer below selects the archive for the server architecture and installs a system-wide executable. It follows the archive method in the official AWS CLI installation instructions, which also explain signature verification. Use that verification procedure if your software installation policy requires signed-download validation.

set -euo pipefail
case "$(uname -m)" in
  x86_64) aws_arch=x86_64 ;;
  aarch64) aws_arch=aarch64 ;;
  *) printf 'Unsupported architecture for this installer\n' >&2; exit 1 ;;
esac
aws_install_dir=$(mktemp -d)
curl --fail --silent --show-error --location \
  "https://awscli.amazonaws.com/awscli-exe-linux-${aws_arch}.zip" \
  --output "$aws_install_dir/awscli.zip"
unzip -q "$aws_install_dir/awscli.zip" -d "$aws_install_dir"
sudo "$aws_install_dir/aws/install" \
  --install-dir /usr/local/aws-cli \
  --bin-dir /usr/local/bin \
  --update
/usr/local/bin/aws --version >/dev/null
test -x /usr/local/bin/aws

For the actual database, use PostgreSQL clients from the same release family as the server. A client older than the server can refuse the dump, and installing Ubuntu's default client does not establish compatibility with a separately managed PostgreSQL installation. Resolve that before scheduling anything.

Create a private bucket and restrict its credentials

Create a private bucket in your object-storage account and enable encryption at rest. Use a dedicated prefix for this database, with retention long enough to cover mistakes you might discover late. Keep bucket administration credentials off the VPS.

• Give the backup identity permission to upload objects and abort its multipart uploads under the backup prefix. Do not grant object deletion or bucket administration.
• Create a separate recovery identity with permission to list the backup prefix and download its objects. Store its credentials somewhere accessible without the production VPS.
• Configure lifecycle rules for expired backups and abandoned multipart uploads. If versioning is enabled, account for noncurrent versions in the retention policy.
• Use enforced object retention when recovery must survive stolen server credentials. Versioning alone does not prevent a sufficiently privileged identity from deleting historical copies.

An upload credential can still write a new object at an existing key, so removing deletion permission is only part of the protection. Unique backup paths reduce accidental replacement; enforced retention protects stored versions against deletion according to the bucket's settings. Review S3 Object Lock behavior before selecting a retention mode, especially where administrators can bypass governance settings.

Save the following as /etc/postgres-backup/env, owned by root with permission mode 0600. Replace the example bucket and region with your account's settings. For another S3-compatible service, set S3_ENDPOINT to its HTTPS API endpoint; leave it empty for Amazon S3.

DATABASE=app
S3_BUCKET=team-postgres-backups
S3_PREFIX=postgres/app
AWS_DEFAULT_REGION=eu-west-1
S3_ENDPOINT=

Save the credential template as /etc/postgres-backup/credentials, also owned by root with permission mode 0600, and replace its values privately. Temporary credentials also require aws_session_token and a working renewal mechanism. Keep these files outside the application repository and apply the same secret-handling rules used for agent access.

[backup]
aws_access_key_id = REPLACE_WITH_BACKUP_ACCESS_KEY
aws_secret_access_key = REPLACE_WITH_BACKUP_SECRET_KEY

Write a backup job that fails visibly

Create /usr/local/sbin/pg-backup with the script below. It writes a custom-format database archive and a separate globals file, then uploads a checksum manifest last. A remote directory without that manifest is incomplete and must not be selected for recovery.

The globals file preserves role definitions without copying password hashes. Keep application passwords in your separate secret-recovery process, and document extension packages and any custom tablespace layout. The script uses local PostgreSQL peer authentication, so it needs no database password in cron.

#!/bin/bash
set -euo pipefail
umask 077
export PATH=/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
cd /

exec 9>/run/lock/postgres-backup.lock
flock -n 9 || { printf 'Another backup is running\n' >&2; exit 1; }

source /etc/postgres-backup/env
: "${DATABASE:?Database is required}"
: "${S3_BUCKET:?Bucket is required}"
: "${S3_PREFIX:?Prefix is required}"
: "${AWS_DEFAULT_REGION:?Region is required}"
export AWS_DEFAULT_REGION
export AWS_SHARED_CREDENTIALS_FILE=/etc/postgres-backup/credentials
export AWS_CONFIG_FILE=/dev/null
export AWS_PROFILE=backup
export AWS_PAGER=
export AWS_EC2_METADATA_DISABLED=true

s3=(/usr/local/bin/aws)
if [[ -n ${S3_ENDPOINT:-} ]]; then
  s3+=(--endpoint-url "$S3_ENDPOINT")
fi

work=$(mktemp -d /var/lib/postgres-backup/job.XXXXXXXX)
run_id="$(date -u +%Y%m%dT%H%M%SZ)-${work##*.}"
remote="s3://${S3_BUCKET}/${S3_PREFIX%/}/${run_id}"
trap 'rc=$?; if (( rc != 0 )); then printf "BACKUP_FAILED %s scratch=%s\n" "$run_id" "$work" >&2; fi' EXIT

runuser -u postgres -- pg_dump \
  --host=/var/run/postgresql --port=5432 --username=postgres \
  --no-password --format=custom --dbname="$DATABASE" \
  > "$work/database.dump"

runuser -u postgres -- pg_dumpall \
  --host=/var/run/postgresql --port=5432 --username=postgres \
  --no-password --database=postgres \
  --globals-only --no-role-passwords \
  > "$work/globals.sql"

test -s "$work/database.dump"
pg_restore --list "$work/database.dump" >/dev/null
(
  cd "$work"
  sha256sum database.dump globals.sql > SHA256SUMS
)

"${s3[@]}" s3 cp "$work/database.dump" "$remote/database.dump" --only-show-errors
"${s3[@]}" s3 cp "$work/globals.sql" "$remote/globals.sql" --only-show-errors
"${s3[@]}" s3 cp "$work/SHA256SUMS" "$remote/SHA256SUMS" --only-show-errors

printf '%s\n' "$run_id" > /var/lib/postgres-backup/last-success
printf 'BACKUP_OK %s\n' "$run_id"
rm -f -- "$work/database.dump" "$work/globals.sql" "$work/SHA256SUMS"
rmdir -- "$work"

Apply ownership and permissions, then run the job manually with your real configuration. The archive-list check catches obvious format problems but does not restore the database. Failed jobs retain their scratch directory for investigation, so monitor free disk space and remove those directories after resolving the failure.

set -euo pipefail
sudo chown root:root /etc/postgres-backup/env /etc/postgres-backup/credentials /usr/local/sbin/pg-backup
sudo chmod 0600 /etc/postgres-backup/env /etc/postgres-backup/credentials
sudo chmod 0700 /usr/local/sbin/pg-backup
sudo /usr/local/sbin/pg-backup

Allow enough local space for a complete dump while production continues writing. Avoid scheduling the export alongside maintenance that heavily uses the same disk. If your storage service requires explicit encryption headers or key-management permissions, configure those before accepting the first successful upload.

Schedule the export and alert on missing backups

Save this configuration as /etc/cron.d/postgres-backup, owned by root with permission mode 0644 and a final newline. The example runs daily according to the server's local timezone. Check that timezone and choose a quiet period that fits the recovery window you recorded.

SHELL=/bin/bash
PATH=/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
MAILTO=""
17 2 * * * root /usr/bin/timeout --signal=TERM 6h /usr/local/sbin/pg-backup >> /var/log/postgres-backup.log 2>&1

The timeout is an example operating limit, not an estimate of how long your dump should take. Adjust it after observing your own backup duration, while keeping it shorter than the scheduling interval. The file lock prevents overlapping jobs and makes an attempted overlap fail explicitly.

Save this log rotation configuration as /etc/logrotate.d/postgres-backup. Each scheduled invocation opens the log again, so rotation needs no service restart. Keep both the logs and retained failure directories in your disk-space monitoring.

/var/log/postgres-backup.log {
    weekly
    rotate 8
    compress
    missingok
    notifempty
    create 0600 root root
}

set -euo pipefail
sudo install -o root -g root -m 0600 /dev/null /var/log/postgres-backup.log
sudo chown root:root /etc/cron.d/postgres-backup /etc/logrotate.d/postgres-backup
sudo chmod 0644 /etc/cron.d/postgres-backup /etc/logrotate.d/postgres-backup
sudo systemctl enable --now cron

Set up an external monitor that checks the age of the newest completed backup, allowing your expected upload time after the scheduled start. Checking only a local success file will not alert when the entire VPS disappears. Follow the small-server monitoring procedure and route a missing-backup alert to someone who will act on it.

Treat the external alert as part of setup, since this cron entry intentionally disables mail and only records command output locally. During a controlled maintenance window, pause the schedule long enough to trigger the missing-backup alert, then restore it and confirm delivery resumes. Do not accept an alert configuration that nobody has seen fire.

Download a completed backup onto an isolated server

Use a disposable recovery VPS with no production application credentials or access to production services. Install matching PostgreSQL server and client packages there, plus the AWS CLI using the earlier installation steps. Ensure PostgreSQL is running locally and install any extension packages required by the source database.

Create the same protected configuration paths on this recovery server, but put the read-only recovery credential in its backup profile. Do not copy the production writer credential. Retrieve those credentials from your recovery store so the drill also exercises access after losing the original VPS.

Run the following in a root Bash shell on the recovery server. Replace the example backup identifier with one that actually exists, taken from the storage console or backup log. Keep this shell open for the restore step, which uses the downloaded files and variables.

set -euo pipefail
umask 077
cd /
source /etc/postgres-backup/env
export AWS_DEFAULT_REGION
export AWS_SHARED_CREDENTIALS_FILE=/etc/postgres-backup/credentials
export AWS_CONFIG_FILE=/dev/null
export AWS_PROFILE=backup
export AWS_PAGER=
export AWS_EC2_METADATA_DISABLED=true

s3=(/usr/local/bin/aws)
if [[ -n ${S3_ENDPOINT:-} ]]; then
  s3+=(--endpoint-url "$S3_ENDPOINT")
fi

BACKUP_ID='20261010T021700Z-a1b2c3d4'
remote="s3://${S3_BUCKET}/${S3_PREFIX%/}/${BACKUP_ID}"
restore_dir=$(mktemp -d /var/tmp/postgres-restore.XXXXXXXX)

"${s3[@]}" s3 cp "$remote/SHA256SUMS" "$restore_dir/SHA256SUMS" --only-show-errors
"${s3[@]}" s3 cp "$remote/database.dump" "$restore_dir/database.dump" --only-show-errors
"${s3[@]}" s3 cp "$remote/globals.sql" "$restore_dir/globals.sql" --only-show-errors
(
  cd "$restore_dir"
  sha256sum --check SHA256SUMS
)
printf 'Downloaded backup to %s\n' "$restore_dir"

These checks detect changed or damaged bytes relative to the stored manifest. They do not establish authenticity if an attacker can replace both the archive and its manifest. Protected object versions and independent recovery credentials address a different failure than transfer corruption.

Restore into an empty database and verify application data

Restore into a newly created database, never over the application's existing database. The command below generates a fresh name and stops if creation fails. It ignores source ownership and grants so a basic data-recovery drill does not depend on recreating every production account.

Before restoring, inspect globals.sql for roles or tablespaces the application depends on. Row-security policies and other definitions can still reference role names even when ownership and grants are skipped. Create required roles on this isolated cluster; do not blindly execute a globals file against an existing production cluster.

set -euo pipefail
cd /
: "${restore_dir:?Run the download step in this shell first}"
drill_db="restore_drill_$(date -u +%Y%m%d%H%M%S)_${RANDOM}"

runuser -u postgres -- createdb \
  --host=/var/run/postgresql --port=5432 --username=postgres \
  --no-password --template=template0 "$drill_db"

runuser -u postgres -- pg_restore \
  --host=/var/run/postgresql --port=5432 --username=postgres \
  --no-password --dbname="$drill_db" \
  --exit-on-error --single-transaction \
  --no-owner --no-acl --no-tablespaces \
  < "$restore_dir/database.dump"

runuser -u postgres -- psql \
  --host=/var/run/postgresql --port=5432 --username=postgres \
  --no-password --dbname="$drill_db" --no-psqlrc \
  --set=ON_ERROR_STOP=1 \
  --command="SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;"

printf 'RESTORE_COMPLETED %s\n' "$drill_db"

The restore runs in a transaction and stops on errors rather than continuing through a partially rebuilt database. Skipping tablespace assignments places restored objects in the recovery database's default storage. These options are documented in the pg_restore reference.

• Check a known business record and its related rows. Use a record that existed before the dump began, and verify values that matter to the application.
• Run application-specific integrity queries. For example, confirm required relationships and compare stored totals with the rows from which they are calculated.
• Start an isolated application instance against the restored database with outbound integrations disabled. Exercise a representative read and a disposable write to expose missing functions or broken sequences.
• Record the backup identifier and elapsed recovery time, including download and database preparation. Compare that result with the recovery deadline you chose.

A successful data drill does not validate production permissions, because this command intentionally omits them. Before accepting the full recovery runbook, recreate the application login and intended grants, then repeat the application checks using that login. Keep email delivery and payment integrations disabled throughout.

Book the next drill and preserve the recovery instructions

Put the next restore drill on your calendar now and name its owner. Repeat it after changes to PostgreSQL or extensions that could affect recovery, and periodically select an older retained backup. Always restoring yesterday's copy leaves the rest of your retention policy unexamined.

Store the recovery instructions outside the production VPS, alongside a reference to the independently stored recovery credentials. Include the required packages and application-specific validation queries. Keep database passwords and encryption-key recovery instructions in the secret store, not in the runbook.

Claude Code can maintain the script or investigate a failed drill with bounded access. Give it sanitized logs and an isolated recovery target, while keeping production deletion and bucket retention changes behind human approval. Apply these production agent guardrails before granting access to backup data.