Automating Database Backups with Restic and systemd

Reliable database backups are non-negotiable for any production system. This guide covers our setup using Restic for encrypted, deduplicated backups, orchestrated by systemd timers instead of cron.

Why Restic over traditional tools?

Traditional tools like pg_dump piped into gzip work, but they lack encryption, deduplication, and incremental snapshotting. Restic handles all three natively, supports dozens of storage backends, and has a clean command-line interface.

After a disk failure last year corrupted our compressed SQL dumps, we moved to Restic and never looked back. The ability to mount any snapshot as a FUSE filesystem for quick inspection is invaluable during incident response.

Installation

Restic is a single static binary. Download the latest release for your architecture:

# Download and install Restic
wget https://github.com/restic/restic/releases/download/v0.16.4/restic_0.16.4_linux_amd64.bz2
bunzip2 restic_0.16.4_linux_amd64.bz2
chmod +x restic_0.16.4_linux_amd64
sudo mv restic_0.16.4_linux_amd64 /usr/local/bin/restic
restic version

Repository initialization

We use S3-compatible storage (MinIO in our case, but AWS S3 works identically). Create a dedicated bucket and IAM policy with minimal permissions:

export RESTIC_REPOSITORY="s3:https://s3.example.com/postgres-backups"
export RESTIC_PASSWORD_FILE="/etc/restic/repo-password"
export AWS_ACCESS_KEY_ID="your-key"
export AWS_SECRET_ACCESS_KEY="your-secret"

# Initialize the repository
restic init

Store the repository password in /etc/restic/repo-password with permissions 0600 and ownership by the backup user.

The backup script

Our backup script handles PostgreSQL credentials, temporary file cleanup, and retention policy in one place:

#!/bin/bash
set -euo pipefail

# Environment
export PGHOST="/var/run/postgresql"
export PGUSER="backup"
export PGDATABASE="postgres"
export PGPASSWORD_FILE="/etc/postgres/backup-password"
export RESTIC_PASSWORD_FILE="/etc/restic/repo-password"
export RESTIC_REPOSITORY="s3:https://s3.example.com/postgres-backups"
export TMPDIR="/var/tmp"

# Create temporary directory
WORKDIR=$(mktemp -d)
trap 'rm -rf "$WORKDIR"' EXIT

# Dump all databases
pg_dumpall --clean --if-exists > "$WORKDIR/all-databases.sql"

# Backup to Restic with tags for filtering
restic backup "$WORKDIR/all-databases.sql"   --tag postgres-daily   --tag "host:$(hostname)"   --hostname db-primary

# Apply retention: keep 7 daily, 4 weekly, 6 monthly
restic forget   --tag postgres-daily   --keep-daily 7   --keep-weekly 4   --keep-monthly 6   --prune

# Verify latest snapshot integrity
restic check --read-data-subset=10%

systemd service and timer

Replace cron with systemd for better logging, dependency management, and failure notifications. Create /etc/systemd/system/postgres-backup.service:

[Unit]
Description=PostgreSQL backup to Restic
After=postgresql.service
Requires=postgresql.service

[Service]
Type=oneshot
User=backup
Group=backup
EnvironmentFile=/etc/restic/backup-env
ExecStart=/usr/local/bin/postgres-backup.sh
StandardOutput=journal
StandardError=journal
SyslogIdentifier=postgres-backup

And the timer at /etc/systemd/system/postgres-backup.timer:

[Unit]
Description=Run PostgreSQL backup daily at 02:00

[Timer]
OnCalendar=02:00
RandomizedDelaySec=1800
Persistent=true

[Install]
WantedBy=timers.target

Enable with:

sudo systemctl daemon-reload
sudo systemctl enable --now postgres-backup.timer
systemctl list-timers postgres-backup.timer

Monitoring and alerting

We export Restic metrics via a small wrapper that parses restic snapshots --json and pushes to Prometheus. Key metrics:

• Time since last successful backup (should be < 25 hours)
• Backup size and deduplication ratio
• Snapshot count (to detect retention policy failures)
• Check status (corruption detection)

Disaster recovery testing

A backup you haven't restored is a Schrödinger's backup. We run automated restore tests weekly into a disposable staging instance:

# List available snapshots
restic snapshots --tag postgres-daily

# Restore latest to a test directory
restic restore latest --tag postgres-daily --target /tmp/restore-test

# Verify dump integrity
pg_restore --list /tmp/restore-test/all-databases.sql > /dev/null

Final thoughts

Moving from cron + gzip to Restic + systemd took about two hours of setup and has saved us multiple times during hardware failures and accidental deletions. The deduplication alone reduced our storage costs by 70%.

Questions or improvements? The comments section is read-only by design — open an issue on the blog's repository if you spot an error.