Automate PostgreSQL Backups on a VPS with Cloudflare R2

Running PostgreSQL on a single VPS is convenient until you realize that the database and its Docker volume live on that same machine. A failed disk, an accidental docker compose down -v, or one bad deployment can take the data with it.

I wanted a PostgreSQL backup process that was simple enough to understand without introducing another backup platform. The result uses familiar Linux tools:

• Cloudflare R2 as off-site, S3-compatible object storage
• rclone to upload and manage backup files in R2
• a systemd service to define the backup job
• a systemd timer to schedule the job
• flock to prevent two copies of the job from running at once

This article walks through the setup I use for PostgreSQL running in Docker. It creates a transaction-consistent logical dump plus an additional archive of the Docker volume, then uploads both files to Cloudflare R2.

The Backup Flow

Each scheduled run follows the same sequence:

1. systemd starts a one-shot service.
2. flock acquires a lock or exits if the previous run is still active.
3. A shell script creates the backup in a temporary directory.
4. The script validates the generated dump and volume archive.
5. rclone uploads it to a private R2 bucket.
6. Old remote backups are pruned only after the new upload succeeds.
7. The temporary directory is removed, even if the script fails.

The VPS only holds the temporary working copy. R2 holds the retained backups under a dedicated postgres/ prefix.

Before You Start

This guide assumes that:

• Docker and Docker Compose are already installed
• your containers and volumes are already running
• the VPS uses systemd
• you can run commands with sudo

Install rclone and flock on Debian or Ubuntu. flock is provided by the util-linux package and is normally already installed.

1
sudo apt update
2
sudo apt install rclone util-linux

Inspect the exact data source before writing a backup script. For a Compose project, start with:

1
docker compose config
2
docker volume ls
3
docker inspect your-container

Do not guess a named volume from the short name in compose.yaml. Compose normally prefixes it with the project name. For example, postgres_data may become your_project_postgres_data.

Set Up Cloudflare R2 and rclone

Create a private R2 bucket in the Cloudflare dashboard. I store PostgreSQL backups under a dedicated prefix:

1
your-r2-bucket/
2
└── postgres/

Then create an R2 API token with Object Read & Write permission, scoped to that bucket. Read permission is needed for listing and restore operations, while write permission is needed for uploads and pruning. Keep the Access Key ID and Secret Access Key safe. Cloudflare only shows the secret once.

Because the systemd service in this guide runs as root, configure rclone as root too:

1
sudo rclone config

Create a new remote called r2, select the S3 storage type, choose Cloudflare as the provider, then enter the access key, secret, and endpoint:

1
https://<ACCOUNT_ID>.r2.cloudflarestorage.com

If the token has object-level permissions for a specific bucket, set no_check_bucket = true in the remote configuration. This prevents rclone from performing bucket-level checks that the scoped token cannot authorize.

You can compare your setup with the official Cloudflare rclone guide. Test the remote before building any automation:

1
sudo rclone lsf r2:your-r2-bucket

Object storage does not require you to create the postgres/ prefix in advance. It appears when the first backup is uploaded.

Keep R2 credentials in rclone’s protected configuration instead of repeating them in every service environment file. To see which file root uses, run:

1
sudo rclone config file

Backup File Structure

Keep the backup configuration beside the PostgreSQL Compose project. The working example uses this structure:

1
postgres/
2
├── backup-postgres.sh
3
├── docker-compose.yml
4
├── postgres-backup.env.example
5
├── postgres-backup.service
6
└── postgres-backup.timer

Example PostgreSQL Docker Compose Setup

If PostgreSQL is not running yet, create docker-compose.yml inside the postgres/ directory. Treat this as a starting point rather than a configuration you must copy exactly: choose the PostgreSQL version, database name, user, networks, and resource settings that fit your application.

1
services:
2
  db:
3
    image: postgres:18
4
    restart: unless-stopped
5
    environment:
6
      POSTGRES_USER: replace_with_database_user
7
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
8
      POSTGRES_DB: replace_with_database_name
9
    healthcheck:
10
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
11
      interval: 10s
12
      timeout: 5s
13
      retries: 5
14
      start_period: 10s
15
    volumes:
16
      - postgres_data:/var/lib/postgresql
17
    secrets:
18
      - postgres_password
19

20
secrets:
21
  postgres_password:
22
    file: ./secrets/postgres_password.txt
23

24
volumes:
25
  postgres_data:

This template uses the official PostgreSQL 18 image, persists its data in a named volume, reads the password from a Compose secret, and adds a health check. Replace the placeholder user and database name before starting it. If you choose a different PostgreSQL major version, check that version’s image documentation for the correct data mount path.

Create the password file before starting PostgreSQL:

1
mkdir -p secrets
2
openssl rand -base64 32 > secrets/postgres_password.txt
3
chmod 600 secrets/postgres_password.txt

Start the container and wait until its health status is healthy:

1
docker compose up -d
2
docker compose ps

This example does not publish port 5432 to the host. Services declared in the same Compose file can connect to PostgreSQL with the hostname db. For services in another Compose project, attach both services to a shared Docker network. If a host process needs database access, add an explicit port mapping bound to a private or loopback address rather than exposing PostgreSQL publicly.

Before configuring the backup script, retrieve the actual container and volume names:

1
docker compose ps
2
docker volume ls --filter name=postgres_data

Use the values from your final Compose configuration and command output as follows:

• POSTGRES_CONTAINER is the container name shown by docker compose ps.
• POSTGRES_USER is the POSTGRES_USER value.
• POSTGRES_DB is the POSTGRES_DB value.
• VOLUME_NAME is the complete name shown by docker volume ls, including the Compose project prefix.

Create the PostgreSQL Backup Script

PostgreSQL needs more care. A tar.gz of a live PostgreSQL data directory is not guaranteed to be transaction-consistent, even if Docker mounts the volume read-only for the backup container.

I create two complementary files:

• a pg_dump in PostgreSQL’s custom format, which is the primary, consistent database backup
• a compressed archive of the complete Docker volume, which is an additional best-effort copy

The database container remains online during both operations.

Create backup-postgres.sh and adjust the first four values for your environment:

1
#!/usr/bin/env bash
2
set -Eeuo pipefail
3

4
# Full-volume and logical backup for PostgreSQL.
5
# The container remains running while both backups are created.
6
# Configure the Rclone destination in /etc/postgres-backup.env.
7

8
readonly VOLUME_NAME="REPLACE_WITH_DOCKER_VOLUME_NAME"
9
readonly POSTGRES_CONTAINER="REPLACE_WITH_POSTGRES_CONTAINER_NAME"
10
readonly POSTGRES_USER="REPLACE_WITH_POSTGRES_USER"
11
readonly POSTGRES_DB="REPLACE_WITH_POSTGRES_DATABASE"
12
readonly CONFIG_FILE="${POSTGRES_BACKUP_CONFIG:-/etc/postgres-backup.env}"
13
readonly RETENTION_COUNT=3
14

15
if [[ ! -r "${CONFIG_FILE}" ]]; then
16
  echo "Missing backup configuration: ${CONFIG_FILE}" >&2
17
  exit 1
18
fi
19

20
# shellcheck source=/dev/null
21
source "${CONFIG_FILE}"
22

23
: "${RCLONE_DEST:?Set RCLONE_DEST, for example r2:my-bucket/postgres}"
24

25
if [[ -n "${RCLONE_CONFIG:-}" ]]; then
26
  export RCLONE_CONFIG
27
fi
28

29
readonly REMOTE_DIR="${RCLONE_DEST%/}"
30
readonly STAGING_DIR="$(mktemp -d /tmp/postgres-backup.XXXXXX)"
31
readonly TIMESTAMP="$(date -u +%Y-%m-%dT%H-%M-%SZ)"
32
readonly ARCHIVE_NAME="postgres-${TIMESTAMP}.tar.gz"
33
readonly ARCHIVE_PATH="${STAGING_DIR}/${ARCHIVE_NAME}"
34
readonly DUMP_NAME="postgres-${POSTGRES_DB}-${TIMESTAMP}.dump"
35
readonly DUMP_PATH="${STAGING_DIR}/${DUMP_NAME}"
36

37
cleanup() {
38
  rm -rf "${STAGING_DIR}"
39
}
40
trap cleanup EXIT
41

42
if ! docker volume inspect "${VOLUME_NAME}" >/dev/null; then
43
  echo "PostgreSQL volume does not exist: ${VOLUME_NAME}" >&2
44
  exit 1
45
fi
46

47
# pg_dump creates a transaction-consistent logical backup while PostgreSQL is
48
# online. The custom format is compressed and supports selective restoration.
49
docker exec "${POSTGRES_CONTAINER}" \
50
  pg_dump \
51
  --username="${POSTGRES_USER}" \
52
  --dbname="${POSTGRES_DB}" \
53
  --format=custom >"${DUMP_PATH}"
54

55
# Confirm that pg_restore can read the completed archive before uploading it.
56
docker exec -i "${POSTGRES_CONTAINER}" pg_restore --list \
57
  <"${DUMP_PATH}" >/dev/null
58

59
# Use a temporary container so the archive contains the mounted volume contents
60
# rather than the PostgreSQL container filesystem.
61
docker run --rm \
62
  --mount "type=volume,source=${VOLUME_NAME},target=/data,readonly" \
63
  --mount "type=bind,source=${STAGING_DIR},target=/backup" \
64
  alpine:3.20 \
65
  tar czf "/backup/${ARCHIVE_NAME}" -C /data .
66

67
tar -tzf "${ARCHIVE_PATH}" >/dev/null
68

69
# Upload must succeed before any old remote backup is deleted.
70
rclone copyto "${ARCHIVE_PATH}" "${REMOTE_DIR}/${ARCHIVE_NAME}" \
71
  --checksum \
72
  --retries 3 \
73
  --low-level-retries 10
74

75
rclone copyto "${DUMP_PATH}" "${REMOTE_DIR}/${DUMP_NAME}" \
76
  --checksum \
77
  --retries 3 \
78
  --low-level-retries 10
79

80
prune_backups() {
81
  local include_pattern="$1"
82
  local -a backups
83

84
  mapfile -t backups < <(
85
    rclone lsf "${REMOTE_DIR}" \
86
      --files-only \
87
      --include "${include_pattern}" | sort -r
88
  )
89

90
  if (( ${#backups[@]} > RETENTION_COUNT )); then
91
    for old_backup in "${backups[@]:RETENTION_COUNT}"; do
92
      [[ -n "${old_backup}" ]] || continue
93
      rclone deletefile "${REMOTE_DIR}/${old_backup}"
94
    done
95
  fi
96
}
97

98
# Keep exactly the three newest copies of each backup type.
99
prune_backups 'postgres-*.tar.gz'
100
prune_backups "postgres-${POSTGRES_DB}-*.dump"
101

102
echo "Uploaded ${ARCHIVE_NAME} and ${DUMP_NAME} to ${REMOTE_DIR}; retained ${RETENTION_COUNT} of each."

The database user needs enough permission to dump the selected database. This example does not pass a password because docker exec runs pg_dump inside the already configured PostgreSQL container.

Create the Environment File Template

Create postgres-backup.env.example in the same directory:

1
# Rclone remote and bucket/prefix. The remote must already be configured.
2
# Example: r2:my-r2-bucket/postgres
3
RCLONE_DEST="r2:REPLACE_WITH_YOUR_BUCKET/postgres"
4

5
# If the systemd job runs as root and the Rclone config is not in root's
6
# default location, uncomment and set this to the config file used by root.
7
# RCLONE_CONFIG="/etc/rclone/rclone.conf"

Create the systemd Service

A systemd service describes how to run the job. Create postgres-backup.service beside the script:

1
[Unit]
2
Description=Back up PostgreSQL volume and database to R2
3
Requires=docker.service
4
After=docker.service network-online.target
5
Wants=network-online.target
6

7
[Service]
8
Type=oneshot
9
User=root
10
Group=root
11
ExecStart=/usr/bin/flock -n /var/lock/postgres-backup.lock /usr/local/sbin/backup-postgres
12
TimeoutStartSec=6h

Type=oneshot means the command runs to completion instead of staying alive as a daemon. flock -n tries to acquire the lock without waiting. If a previous backup still holds it, the new run exits immediately instead of uploading two backups concurrently.

The job runs as root because it needs access to Docker and root’s rclone configuration.

Create the systemd Timer

Create postgres-backup.timer beside the service:

1
[Unit]
2
Description=Daily PostgreSQL R2 backup timer
3

4
[Timer]
5
OnCalendar=*-*-* 03:00:00 UTC
6
Persistent=true
7
Unit=postgres-backup.service
8

9
[Install]
10
WantedBy=timers.target

Persistent=true tells systemd to catch up after boot if the VPS was off at the scheduled time.

Configure the Backup

After configuring the r2 remote, copy the environment template into /etc, edit its destination, and restrict its permissions:

1
sudo cp postgres-backup.env.example /etc/postgres-backup.env
2
sudoedit /etc/postgres-backup.env
3
sudo chmod 600 /etc/postgres-backup.env

Set the R2 destination in /etc/postgres-backup.env:

1
RCLONE_DEST="r2:your-r2-bucket/postgres"

If root’s rclone configuration is stored somewhere other than its default location, add:

1
RCLONE_CONFIG="/etc/rclone/rclone.conf"

Do not store R2 secrets in this environment file. Keep them in rclone’s protected configuration.

Install the systemd Job

Install the script, service, and timer from the postgres/ directory:

1
sudo install -m 0750 backup-postgres.sh /usr/local/sbin/backup-postgres
2
sudo install -m 0644 \
3
  postgres-backup.service \
4
  postgres-backup.timer \
5
  /etc/systemd/system/
6
sudo systemctl daemon-reload
7
sudo systemctl enable --now postgres-backup.timer

The install commands copy the files to their runtime locations and apply their required modes. The timer gives this cron-like workflow clear service dependencies, centralized logs in the journal, missed-run handling, and easy manual execution.

Run and Inspect a Backup

Do not wait until 03:00 to learn that the volume name or R2 path is wrong. Start the service manually:

1
sudo systemctl start postgres-backup.service

Check the timer and logs:

1
sudo systemctl status postgres-backup.timer
2
sudo journalctl -u postgres-backup.service

Finally, confirm that the expected objects exist in R2:

1
sudo rclone lsf r2:your-r2-bucket/postgres

A successful PostgreSQL run uploads two files with the same UTC timestamp:

1
postgres-2026-08-10T03-00-00Z.tar.gz
2
postgres-your_database-2026-08-10T03-00-00Z.dump

Restore

A backup is only useful if it can be restored. Download a PostgreSQL dump to a safe test machine or staging server:

1
rclone copyto \
2
  r2:your-r2-bucket/postgres/postgres-your_database-TIMESTAMP.dump \
3
  ./postgres.dump

After creating an empty test database, restore the dump:

1
docker exec -i your_postgres_container pg_restore \
2
  --username=your_postgres_user \
3
  --dbname=your_restore_test_database \
4
  --clean \
5
  --if-exists \
6
  < postgres.dump

Verify important tables, record counts, and application behavior. Do not test a restore over your production database.

Restoring a raw PostgreSQL volume is more sensitive: stop PostgreSQL, restore into an empty volume, and use the same PostgreSQL major version that created the archive. The logical dump should remain the preferred restore source.

Improvements Worth Adding

This setup intentionally stays small, but a production backup plan should also consider:

• monitoring failed systemd services and missing recent R2 objects
• keeping more than three backups or using daily, weekly, and monthly retention
• configuring an R2 lifecycle rule instead of pruning in the script
• encrypting sensitive backups with rclone crypt before upload
• creating a separate backup in another provider or account
• documenting and rehearsing the full restore procedure

Three copies of a broken archive are still broken. Local validation catches obvious corruption, but only a real restore test proves that the data is usable.

Wrapping Up

This approach has very few moving parts: a script knows how to produce and validate one backup, rclone moves it off the VPS, systemd schedules and logs the job, and flock prevents overlap.

The most important lesson is that a database needs a database-aware backup. For PostgreSQL, that means treating the verified pg_dump as the reliable recovery artifact and the live Docker volume archive only as an additional copy.