This tutorial builds an automated PostgreSQL backup to S3 workflow on Ubuntu 24.04. You will create a consistent custom-format dump with pg_dump, encrypt it with Restic, store it in Raff Object Storage, apply retention, schedule backups with systemd, and prove recovery by restoring into a separate database.
Raff Object Storage is S3-compatible at https://s3.raffusercloud.com in the us-east region. Its current $7/month bundle includes 100 GB of storage and 1 TB of monthly egress, and the product page publishes a 99.9% uptime commitment. Restic supports S3-compatible repositories, so the workflow uses the same repository model it documents for non-AWS S3 services.
This is a logical backup and restore workflow. It is suitable when your recovery point objective can be met by scheduled dumps. It is not point-in-time recovery (PITR): PostgreSQL uses base backups plus WAL archiving for recovery to a specific point between backups.
Prerequisites:
- A Raff Linux VM running Ubuntu 24.04
- PostgreSQL installed and a database you can dump locally
- SSH and sudo access
- A private Raff Object Storage bucket with a scoped read-write S3 key
- AWS CLI configured with a
raffprofile if you want to run the bucket verification command in Step 3 - Enough temporary disk space for one PostgreSQL dump
- A password manager or another secure off-VM location for the Restic repository password
📌 Recovery rule: A completed backup job is not proof of recoverability. The final step restores the latest recovery point into an isolated test database.
The original workflow was tested on a Raff 2 vCPU / 4 GB RAM Ubuntu 24.04 VM. For this refresh, the PostgreSQL dump/restore behavior, Restic S3 configuration, password-file handling, retention, pruning, and repository checks were re-verified against current PostgreSQL and Restic documentation in September 2026.
Step 1 — Install Restic and PostgreSQL client tools
Update Ubuntu and install the required packages:
sudo apt update sudo apt upgrade -y sudo apt install -y restic postgresql-client
Verify the tools:
restic version pg_dump --version pg_restore --version
Check the PostgreSQL server version:
sudo -u postgres psql -tAc 'SHOW server_version;'
Use a pg_dump client from the same PostgreSQL major version as the server when practical. PostgreSQL documents that pg_dump can dump older servers, but it cannot dump a server newer than its own major version. See the official pg_dump reference.
Verification: restic version, pg_dump --version, and the server-version query should all complete successfully before you continue.
Step 2 — Identify and validate the source database
List the databases on the server:
sudo -u postgres psql -c '\l'
This tutorial uses raffapp. Replace that name everywhere with your real database name.
Create a temporary custom-format dump:
sudo -u postgres pg_dump \ --format=custom \ --file=/tmp/raffapp-test.dump \ raffapp
Validate the archive catalog and confirm the file is non-empty:
sudo -u postgres pg_restore --list /tmp/raffapp-test.dump | head test -s /tmp/raffapp-test.dump && echo 'Dump validation passed'
Remove the temporary archive:
sudo rm -f /tmp/raffapp-test.dump
PostgreSQL separates SQL dumps, filesystem-level backups, and continuous archiving as different backup approaches. For this tutorial, pg_dump creates a portable logical archive that pg_restore can reconstruct later. See PostgreSQL's Backup and Restore documentation.
Verification: the archive catalog should be readable and Dump validation passed should print before the test file is removed.
Step 3 — Prepare S3-compatible Raff Object Storage
Use a dedicated private bucket for database backups. For example:
company-production-postgres-backups
Create a scoped access key that has read-write access only to the backup bucket. Raff Object Storage currently supports per-bucket grants, so the backup process does not need credentials for unrelated buckets.
If your AWS CLI raff profile is already configured, verify access without uploading data:
aws s3api head-bucket \ --bucket company-production-postgres-backups \ --endpoint-url https://s3.raffusercloud.com \ --profile raff
A successful head-bucket command returns no output. If you still need the CLI profile, follow Raff Object Storage with AWS CLI first.
Verification: confirm the bucket is private, the backup key is scoped to it, and head-bucket completes without an access error.
Step 4 — Store Restic credentials without putting the repository password in the environment
Restic supports RESTIC_PASSWORD_FILE for automated jobs. Keeping the repository password in a root-only file avoids placing the password itself in the service environment.
Generate a strong repository password into a protected file:
openssl rand -base64 32 | \ sudo tee /etc/restic-postgres.password >/dev/null sudo chown root:root /etc/restic-postgres.password sudo chmod 600 /etc/restic-postgres.password
Copy that password into a password manager or another secure recovery location outside the VM:
sudo cat /etc/restic-postgres.password
Create the environment file:
sudo install -m 600 -o root -g root \ /dev/null /etc/restic-postgres.env sudo nano /etc/restic-postgres.env
Add the following values and replace the placeholders:
RESTIC_REPOSITORY="s3:https://s3.raffusercloud.com/company-production-postgres-backups/postgresql/raffapp" RESTIC_PASSWORD_FILE="/etc/restic-postgres.password" AWS_ACCESS_KEY_ID="REPLACE_WITH_RAFF_ACCESS_KEY" AWS_SECRET_ACCESS_KEY="REPLACE_WITH_RAFF_SECRET_KEY" AWS_DEFAULT_REGION="us-east" PGDATABASE="raffapp" DUMP_DIR="/var/backups/postgresql-restic"
Restic documents the s3:https://server/bucket form for non-AWS S3-compatible services and supports an explicit region through AWS_DEFAULT_REGION. Raff's current Object Storage documentation identifies the service region as us-east.
Check both files without printing their secrets:
sudo stat -c '%A %U:%G %n' \ /etc/restic-postgres.env \ /etc/restic-postgres.password
Verification: both files should be owned by root:root with -rw------- permissions.
Step 5 — Initialize the encrypted Restic repository
Load the protected environment and initialize the repository:
sudo bash -c ' set -a source /etc/restic-postgres.env set +a restic init '
Do not run restic init again if the same repository path is already initialized.
Verify repository access:
sudo bash -c ' set -a source /etc/restic-postgres.env set +a restic snapshots '
A new repository should open successfully and report that no snapshots exist yet.
Verification: restic snapshots must open the repository without asking for a password or returning an S3 authentication error.
Step 6 — Create a private local staging directory
Create a directory that only PostgreSQL and root can use for the temporary logical dump:
sudo install -d \ -m 700 \ -o postgres \ -g postgres \ /var/backups/postgresql-restic
Verify permissions:
sudo stat -c '%A %U:%G %n' \ /var/backups/postgresql-restic
Expected ownership is postgres:postgres with mode drwx------.
Verification: do not continue if another local user can read the staging directory.
Step 7 — Create the PostgreSQL backup to S3 script
Create the backup script:
sudo nano /usr/local/sbin/postgres-restic-backup.sh
Add:
#!/usr/bin/env bash set -Eeuo pipefail umask 077 ENV_FILE="/etc/restic-postgres.env" LOCK_FILE="/run/lock/postgres-restic-backup.lock" if [[ ! -r "$ENV_FILE" ]]; then echo "Cannot read $ENV_FILE" >&2 exit 1 fi set -a source "$ENV_FILE" set +a : "${RESTIC_REPOSITORY:?Missing RESTIC_REPOSITORY}" : "${RESTIC_PASSWORD_FILE:?Missing RESTIC_PASSWORD_FILE}" : "${AWS_ACCESS_KEY_ID:?Missing AWS_ACCESS_KEY_ID}" : "${AWS_SECRET_ACCESS_KEY:?Missing AWS_SECRET_ACCESS_KEY}" : "${AWS_DEFAULT_REGION:?Missing AWS_DEFAULT_REGION}" : "${PGDATABASE:?Missing PGDATABASE}" : "${DUMP_DIR:?Missing DUMP_DIR}" install -d -m 700 -o postgres -g postgres "$DUMP_DIR" DUMP_FILE="${DUMP_DIR}/${PGDATABASE}.dump" exec 9>"$LOCK_FILE" if ! flock -n 9; then echo "Another PostgreSQL backup is already running" >&2 exit 1 fi cleanup() { rm -f "$DUMP_FILE" } trap cleanup EXIT echo "Creating PostgreSQL archive" runuser -u postgres -- pg_dump \ --format=custom \ --file="$DUMP_FILE" \ "$PGDATABASE" test -s "$DUMP_FILE" runuser -u postgres -- pg_restore \ --list "$DUMP_FILE" >/dev/null echo "Backing up encrypted archive to Restic" restic backup "$DUMP_FILE" \ --tag postgresql \ --tag "$PGDATABASE" echo "Applying snapshot retention" restic forget \ --keep-daily 7 \ --keep-weekly 4 \ --keep-monthly 12 echo "Backup completed successfully"
The dump path stays stable from run to run. That matters because Restic's retention policy groups snapshots by host and path by default; a stable path lets the daily, weekly, and monthly policy operate on the same backup set instead of treating every timestamped filename as a different group.
Make the script root-owned and executable, then check its syntax:
sudo chown root:root /usr/local/sbin/postgres-restic-backup.sh sudo chmod 750 /usr/local/sbin/postgres-restic-backup.sh sudo bash -n /usr/local/sbin/postgres-restic-backup.sh
Verification: bash -n should return no output and exit successfully.
Step 8 — Run and verify the first backup
Run the script manually:
sudo /usr/local/sbin/postgres-restic-backup.sh
List repository snapshots:
sudo bash -c ' set -a source /etc/restic-postgres.env set +a restic snapshots '
Inspect the latest snapshot contents:
sudo bash -c ' set -a source /etc/restic-postgres.env set +a restic ls latest '
Confirm the local dump was removed after Restic committed the snapshot:
sudo find /var/backups/postgresql-restic \ -maxdepth 1 -type f -print
The final command should print no files after a successful run.
Verification: you should see a new Restic snapshot containing raffapp.dump, while the staging directory is empty.
Step 9 — Schedule nightly PostgreSQL backups with systemd
Create the service:
sudo nano /etc/systemd/system/postgres-restic-backup.service
Add:
[Unit] Description=Back up PostgreSQL to Raff Object Storage with Restic Wants=network-online.target After=network-online.target postgresql.service [Service] Type=oneshot EnvironmentFile=/etc/restic-postgres.env ExecStart=/usr/local/sbin/postgres-restic-backup.sh User=root Group=root Nice=10 IOSchedulingClass=best-effort IOSchedulingPriority=7 PrivateTmp=true NoNewPrivileges=true
Create the nightly timer:
sudo nano /etc/systemd/system/postgres-restic-backup.timer
Add:
[Unit] Description=Run PostgreSQL Restic backup nightly [Timer] OnCalendar=*-*-* 02:30:00 Persistent=true RandomizedDelaySec=15m Unit=postgres-restic-backup.service [Install] WantedBy=timers.target
Enable it:
sudo systemctl daemon-reload sudo systemctl enable --now postgres-restic-backup.timer
Verify the schedule and run the service once through systemd:
systemctl list-timers postgres-restic-backup.timer sudo systemctl start postgres-restic-backup.service sudo journalctl -u postgres-restic-backup.service -n 100 --no-pager
Persistent=true causes a missed timer to run after the VM comes back online.
Verification: the timer should show a next-run time and the manual systemd run should finish without an error in the journal.
Step 10 — Schedule prune and repository checks separately
The backup script removes old snapshot references with restic forget, but it deliberately does not prune repository data during every nightly backup. Restic warns that prune can take significant time and locks the repository, so run it during a separate maintenance window. Restic also recommends checking the repository after pruning.
Create a maintenance service:
sudo nano /etc/systemd/system/postgres-restic-maintenance.service
Add:
[Unit] Description=Prune and check PostgreSQL Restic repository Wants=network-online.target After=network-online.target [Service] Type=oneshot EnvironmentFile=/etc/restic-postgres.env ExecStart=/usr/bin/restic prune ExecStart=/usr/bin/restic check User=root Group=root Nice=15 IOSchedulingClass=idle PrivateTmp=true NoNewPrivileges=true
Create a weekly timer:
sudo nano /etc/systemd/system/postgres-restic-maintenance.timer
Add:
[Unit] Description=Run weekly Restic repository maintenance [Timer] OnCalendar=Sun *-*-* 04:00:00 Persistent=true RandomizedDelaySec=30m Unit=postgres-restic-maintenance.service [Install] WantedBy=timers.target
Enable and verify it:
sudo systemctl daemon-reload sudo systemctl enable --now postgres-restic-maintenance.timer systemctl list-timers postgres-restic-maintenance.timer
For larger repositories, schedule maintenance when it cannot overlap a backup window.
Verification: the maintenance timer should be enabled and show its next weekly run.
Step 11 — Restore the latest backup into a clean test database
A PostgreSQL backup and restore process is complete only when the archive can be used by PostgreSQL again. Restore the latest Restic snapshot into a temporary directory:
sudo rm -rf /var/tmp/postgres-restic-restore sudo install -d -m 700 -o root -g root \ /var/tmp/postgres-restic-restore sudo bash -c ' set -a source /etc/restic-postgres.env set +a restic restore latest \ --target /var/tmp/postgres-restic-restore '
Locate the restored dump:
RESTORED_DUMP="$(sudo find /var/tmp/postgres-restic-restore \ -type f -name 'raffapp.dump' -print -quit)" test -n "$RESTORED_DUMP" && test -s "$RESTORED_DUMP" printf '%s\n' "$RESTORED_DUMP"
Inspect the archive before executing it. PostgreSQL warns that restoring a dump executes commands contained in the source archive, so only restore archives from a trusted source:
sudo -u postgres pg_restore \ --list "$RESTORED_DUMP" | head
Create an isolated recovery database:
sudo -u postgres dropdb --if-exists raffapp_restore_test sudo -u postgres createdb raffapp_restore_test
Restore schema and data without requiring the original role ownership and grants:
sudo -u postgres pg_restore \ --exit-on-error \ --no-owner \ --no-privileges \ --dbname=raffapp_restore_test \ "$RESTORED_DUMP"
Verify tables and run an application-specific integrity query:
sudo -u postgres psql \ --dbname=raffapp_restore_test \ --command='\dt'
The structural test above does not recreate cluster-wide PostgreSQL roles or prove application semantics. For a production recovery runbook, also document required roles, extensions, secrets, and application-level checks.
Verification: the restore must complete without pg_restore --exit-on-error stopping, the test database must contain the expected tables, and your application-specific integrity checks must pass.