Back Up PostgreSQL with Restic: Secure Raff Object Storage

Introduction

Back up PostgreSQL to Raff Object Storage with Restic by creating a consistent PostgreSQL dump, storing it in an encrypted Restic repository, scheduling the backup with systemd, and testing a full restore on Ubuntu 24.04. This tutorial shows you how to build an off-server database backup workflow on Raff Technologies so your PostgreSQL data can survive server deletion, disk issues, bad deployments, and accidental data loss.

Restic is an encrypted backup tool that stores deduplicated snapshots in repositories, including S3-compatible object storage. PostgreSQL backups need special care because a live database is not just a folder of files; you should create a consistent archive with pg_dump, then let Restic encrypt and upload that archive to object storage. Raff Object Storage works with S3-compatible tools through the endpoint s3.raffusercloud.com, which makes it a clean destination for database backup repositories.

In this tutorial, you will install Restic and PostgreSQL client tools, create a Raff Object Storage bucket, configure Restic credentials securely, initialize an encrypted repository, create a PostgreSQL dump, upload it to object storage, automate the process with a systemd timer, apply retention rules, and verify the backup by restoring it into a test database.

Step 1 — Install Restic and PostgreSQL Client Tools

Start by installing Restic and the PostgreSQL client utilities. Restic handles the encrypted backup repository, while pg_dump and pg_restore handle PostgreSQL export and restore.

bashsudo apt update
sudo apt install -y restic postgresql-client

Verify both tools are available:

bashrestic version
pg_dump --version
pg_restore --version

Expected output will look similar to this:

textrestic 0.x.x compiled with go...
pg_dump (PostgreSQL) 16.x
pg_restore (PostgreSQL) 16.x

The exact patch versions can differ depending on your Ubuntu package updates. What matters is that all three commands return a version without errors.

Step 2 — Confirm Your PostgreSQL Database Name

Before writing the backup script, confirm which PostgreSQL database you want to back up. List databases as the postgres system user:

bashsudo -u postgres psql -c "\l"

You should see output similar to this:

text                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype 
-----------+----------+----------+-----------------+---------+-------
 appdb     | appuser  | UTF8     | libc            | C.UTF-8 | C.UTF-8
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8

For the rest of this tutorial, replace appdb with your real database name.

Test a manual dump using PostgreSQL's custom archive format:

bashsudo -u postgres pg_dump -Fc -d appdb -f /tmp/appdb-test.dump

Verify that the dump file exists:

bashls -lh /tmp/appdb-test.dump

Expected output:

text-rw-r--r-- 1 postgres postgres 1.2M May  3 10:30 /tmp/appdb-test.dump

Now inspect the dump archive:

bashsudo -u postgres pg_restore -l /tmp/appdb-test.dump | head

If you see a list of database objects, the dump is valid. Remove the temporary test file:

bashsudo rm -f /tmp/appdb-test.dump

This test matters because Restic should back up a valid PostgreSQL archive, not a random database directory copied while PostgreSQL is running.

Step 3 — Create a Raff Object Storage Bucket

Restic needs a destination repository. For this tutorial, use a Raff Object Storage bucket dedicated to database backups.

You can create the bucket from the Raff dashboard:

1. Log in to the Raff dashboard.
2. Open Object Storage.
3. Create a new bucket.
4. Use a descriptive bucket name such as my-project-postgres-backups.
5. Generate or copy your S3 access key and secret key.

If you already followed How to Use Raff S3 Object Storage with AWS CLI, you can also create the bucket from the command line:

bashaws s3 mb s3://my-project-postgres-backups \
  --endpoint-url https://s3.raffusercloud.com \
  --profile raff

Expected output:

textmake_bucket: my-project-postgres-backups

The bucket is only the outer container. Restic will create its own encrypted repository structure inside that bucket in a later step.

Step 4 — Create a Secure Restic Environment File

Restic reads repository settings and credentials from environment variables. Keep those variables in a root-owned file so they do not appear directly in your backup script.

Create the environment file:

bashsudo nano /etc/restic-postgres.env

Add the following content, replacing the placeholder values:

bashexport RESTIC_REPOSITORY="s3:https://s3.raffusercloud.com/my-project-postgres-backups/postgres-restic"
export AWS_ACCESS_KEY_ID="<your-raff-s3-access-key>"
export AWS_SECRET_ACCESS_KEY="<your-raff-s3-secret-key>"
export AWS_DEFAULT_REGION="us-east-1"
export RESTIC_PASSWORD="<create-a-long-random-restic-password>"
export PGDATABASE="appdb"

Save and close the file. Then lock down its permissions:

bashsudo chown root:root /etc/restic-postgres.env
sudo chmod 600 /etc/restic-postgres.env

Verify the permissions:

bashsudo ls -l /etc/restic-postgres.env

Expected output:

text-rw------- 1 root root 320 May  3 10:40 /etc/restic-postgres.env

The RESTIC_PASSWORD encrypts the repository. Store it in a password manager or another secure location outside the VM. If you lose this password, you cannot restore the backups.

Step 5 — Initialize the Restic Repository

Now initialize the encrypted Restic repository inside the Raff Object Storage bucket.

Load the environment file for your current shell:

bashsudo bash -c 'source /etc/restic-postgres.env && restic init'

Expected output:

textcreated restic repository <repository-id> at s3:https://s3.raffusercloud.com/my-project-postgres-backups/postgres-restic

Please note that knowledge of your password is required to access
the repository. Losing your password means that your data is irrecoverably lost.

Check that the repository is reachable:

bashsudo bash -c 'source /etc/restic-postgres.env && restic snapshots'

For a new repository, you should see:

textrepository <repository-id> opened successfully, password is correct
no snapshots found

If you see Access Denied, check the access key, secret key, and bucket name. If you see a connection error, confirm the endpoint is exactly https://s3.raffusercloud.com.

Step 6 — Create the Local Backup Directory

The backup script will create a PostgreSQL dump locally before Restic uploads it. Create a dedicated directory owned by the postgres user so pg_dump can write archive files safely.

bashsudo install -d -m 700 -o postgres -g postgres /var/backups/postgresql-restic

Verify the directory:

bashls -ld /var/backups/postgresql-restic

Expected output:

textdrwx------ 2 postgres postgres 4096 May  3 10:45 /var/backups/postgresql-restic

The directory is intentionally private. PostgreSQL dumps may include sensitive data, so do not make this directory world-readable.

Step 7 — Write the PostgreSQL Restic Backup Script

Create a backup script in /usr/local/sbin. This script will create a PostgreSQL custom-format dump, verify that the dump can be read, back it up with Restic, apply retention, and remove the local dump after a successful upload.

bashsudo nano /usr/local/sbin/postgres-restic-backup.sh

Add the following script:

bash#!/usr/bin/env bash
set -euo pipefail

ENV_FILE="/etc/restic-postgres.env"
DUMP_DIR="/var/backups/postgresql-restic"
LOG_FILE="/var/log/postgres-restic-backup.log"

source "$ENV_FILE"

TIMESTAMP="$(date -u +'%Y-%m-%dT%H-%M-%SZ')"
DUMP_FILE="${DUMP_DIR}/${PGDATABASE}_${TIMESTAMP}.dump"

{
  echo "[$(date -u +'%Y-%m-%dT%H:%M:%SZ')] Starting PostgreSQL backup for database: ${PGDATABASE}"

  echo "Creating PostgreSQL custom-format dump..."
  sudo -u postgres pg_dump -Fc -d "$PGDATABASE" -f "$DUMP_FILE"

  echo "Validating dump archive..."
  sudo -u postgres pg_restore -l "$DUMP_FILE" > /dev/null

  echo "Uploading encrypted backup to Restic repository..."
  restic backup "$DUMP_FILE" \
    --tag postgresql \
    --tag "$PGDATABASE"

  echo "Applying retention policy..."
  restic forget \
    --tag postgresql \
    --tag "$PGDATABASE" \
    --keep-daily 7 \
    --keep-weekly 4 \
    --keep-monthly 6 \
    --prune

  echo "Checking repository metadata..."
  restic check

  echo "Removing local dump file..."
  rm -f "$DUMP_FILE"

  echo "[$(date -u +'%Y-%m-%dT%H:%M:%SZ')] Backup completed successfully."
} 2>&1 | tee -a "$LOG_FILE"

Save the file, then make it executable:

bashsudo chown root:root /usr/local/sbin/postgres-restic-backup.sh
sudo chmod 750 /usr/local/sbin/postgres-restic-backup.sh

Verify the script permissions:

bashls -l /usr/local/sbin/postgres-restic-backup.sh

Expected output:

text-rwxr-x--- 1 root root 1250 May  3 10:50 /usr/local/sbin/postgres-restic-backup.sh

The script uses pg_dump -Fc because PostgreSQL's custom archive format works with pg_restore, supports selective restore, and is more flexible than plain SQL for most production recovery workflows.

Step 8 — Run the Backup Manually

Run the script manually before scheduling it. This catches credential, permission, and database-name issues immediately.

bashsudo /usr/local/sbin/postgres-restic-backup.sh

Expected output:

text[2026-05-03T10:55:00Z] Starting PostgreSQL backup for database: appdb
Creating PostgreSQL custom-format dump...
Validating dump archive...
Uploading encrypted backup to Restic repository...
repository <repository-id> opened successfully, password is correct
Files:           1 new,     0 changed,     0 unmodified
Dirs:            3 new,     0 changed,     0 unmodified
Added to the repository: 1.234 MiB
processed 1 files, 1.234 MiB in 0:02
snapshot <snapshot-id> saved
Applying retention policy...
Checking repository metadata...
Removing local dump file...
[2026-05-03T10:55:04Z] Backup completed successfully.

List the Restic snapshots:

bashsudo bash -c 'source /etc/restic-postgres.env && restic snapshots --tag postgresql'

Expected output:

textID        Time                 Host        Tags                 Paths
--------------------------------------------------------------------------------
a1b2c3d4  2026-05-03 10:55:02  raff-vm     postgresql,appdb     /var/backups/postgresql-restic/appdb_2026-05-03T10-55-00Z.dump
--------------------------------------------------------------------------------
1 snapshots

Check that the local dump was removed after upload:

bashsudo ls -lh /var/backups/postgresql-restic

Expected output:

texttotal 0

That is the desired state. The encrypted backup lives in Raff Object Storage, not permanently on the VM disk.

Step 9 — Schedule the Backup with systemd

Use a systemd timer instead of cron for better logging, missed-run handling, and service visibility.

Create the service unit:

bashsudo nano /etc/systemd/system/postgres-restic-backup.service

Add this content:

ini[Unit]
Description=Back up PostgreSQL to Raff Object Storage with Restic
Wants=network-online.target
After=network-online.target postgresql.service

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/postgres-restic-backup.sh

Now create the timer:

bashsudo nano /etc/systemd/system/postgres-restic-backup.timer

Add this content:

ini[Unit]
Description=Run PostgreSQL Restic backup every night

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true
RandomizedDelaySec=15m
Unit=postgres-restic-backup.service

[Install]
WantedBy=timers.target

Reload systemd and enable the timer:

bashsudo systemctl daemon-reload
sudo systemctl enable --now postgres-restic-backup.timer

Verify the timer:

bashsystemctl list-timers postgres-restic-backup.timer

Expected output:

textNEXT                        LEFT     LAST PASSED UNIT                         ACTIVATES
Mon 2026-05-04 02:30:00 UTC 15h      -    -      postgres-restic-backup.timer postgres-restic-backup.service

Run the service once through systemd to verify the unit works:

bashsudo systemctl start postgres-restic-backup.service

Check the logs:

bashsudo journalctl -u postgres-restic-backup.service -n 50 --no-pager

You should see the same successful backup messages from the manual test.

Step 10 — Test a Restore into a New PostgreSQL Database

The final step is the most important: prove that the backup can be restored. A green backup job only means the command ran. A restore test proves the backup is useful.

Create a temporary restore directory:

bashsudo rm -rf /tmp/restic-postgres-restore
sudo mkdir -p /tmp/restic-postgres-restore

Restore the latest PostgreSQL dump from Restic:

bashsudo bash -c 'source /etc/restic-postgres.env && restic restore latest --target /tmp/restic-postgres-restore --tag postgresql'

Find the restored dump file:

bashsudo find /tmp/restic-postgres-restore -name "*.dump" -type f

Expected output:

text/tmp/restic-postgres-restore/var/backups/postgresql-restic/appdb_2026-05-03T10-55-00Z.dump

Set a variable for the restored file:

bashRESTORED_DUMP="$(sudo find /tmp/restic-postgres-restore -name '*.dump' -type f | head -n 1)"
echo "$RESTORED_DUMP"

Create a clean test database:

bashsudo -u postgres dropdb --if-exists appdb_restore_test
sudo -u postgres createdb appdb_restore_test

Restore the archive into the test database:

bashsudo -u postgres pg_restore \
  --clean \
  --if-exists \
  -d appdb_restore_test \
  "$RESTORED_DUMP"

Inspect the restored database:

bashsudo -u postgres psql -d appdb_restore_test -c "\dt"

Expected output depends on your application, but you should see your restored tables:

text              List of relations
 Schema |        Name        | Type  | Owner
--------+--------------------+-------+--------
 public | users              | table | appuser
 public | orders             | table | appuser
 public | schema_migrations  | table | appuser

Clean up the test database and restore directory:

bashsudo -u postgres dropdb appdb_restore_test
sudo rm -rf /tmp/restic-postgres-restore

You now have evidence that your backup is not just uploaded — it can be restored into PostgreSQL.

Step 11 — Monitor Backups and Repository Health

After the first successful restore, add simple operational checks. Start by reviewing the latest systemd status:

bashsystemctl status postgres-restic-backup.timer
systemctl status postgres-restic-backup.service

Review recent backup logs:

bashsudo tail -n 100 /var/log/postgres-restic-backup.log

List recent snapshots:

bashsudo bash -c 'source /etc/restic-postgres.env && restic snapshots --tag postgresql'

Run a deeper repository check occasionally:

bashsudo bash -c 'source /etc/restic-postgres.env && restic check --read-data-subset=1/10'

The regular backup script runs restic check, which verifies repository metadata. The --read-data-subset=1/10 option reads a portion of stored data and takes longer, so it is better as a weekly or monthly maintenance command rather than a daily backup step.

You can add a monthly systemd timer for deeper checks later, but start with manual checks until you know the backup size and runtime.

Step 12 — Roll Back or Disable the Backup Job

If you need to disable the scheduled backup, stop and disable the timer:

bashsudo systemctl disable --now postgres-restic-backup.timer

The manual backup script remains available. To remove the systemd units completely:

bashsudo rm -f /etc/systemd/system/postgres-restic-backup.service
sudo rm -f /etc/systemd/system/postgres-restic-backup.timer
sudo systemctl daemon-reload

To remove local configuration files from the VM:

bashsudo rm -f /usr/local/sbin/postgres-restic-backup.sh
sudo rm -f /etc/restic-postgres.env
sudo rm -f /var/log/postgres-restic-backup.log
sudo rm -rf /var/backups/postgresql-restic

This does not delete the Restic repository from Raff Object Storage. Keeping the repository is usually the safer choice until you are certain no restore is needed.

To remove the object storage data, delete the repository path or bucket from the Raff dashboard or with an S3-compatible tool. Be careful: deleting the repository deletes your backup history.

Conclusion

You have built an encrypted PostgreSQL backup workflow on Ubuntu 24.04 using Restic and Raff Object Storage. The setup creates a consistent pg_dump archive, validates it, uploads it to an encrypted Restic repository, applies retention, schedules daily execution with systemd, and proves recovery by restoring into a clean test database.

This gives you a stronger recovery path than local-only backups. Raff snapshots and automated backups help protect the VM state, while Restic gives you off-server, encrypted PostgreSQL restore points stored in object storage. Together, they create separate recovery options for server rollback, database restore, and clean-machine rebuilds.

From here, you can:

• Read Cloud Server Backup Strategies: Snapshots, RPO, and Recovery Planning to define your recovery objectives.
• Compare Cloud Snapshots vs Backups so you know when to use each layer.
• Use How to Use Raff S3 Object Storage with AWS CLI to inspect buckets, upload files, and manage object storage workflows.
• Extend this script to back up multiple PostgreSQL databases or send alerts to email, Slack, or your monitoring system.

This tutorial was tested on a Raff Ubuntu 24.04 VM with PostgreSQL running locally and Raff Object Storage as the Restic repository destination.