PostgreSQL Backup Guide

A walk-through for backing up a PostgreSQL stack to a remote SFTP target, with a pre-backup pg_dump hook so the volume snapshot is always consistent.

Scenario

• A stack named analytics with a postgres:16 service
• Off-site backup target: SFTP to backup.example.com
• Daily backup at 02:00, keep last 14 days

Step 1 — Create the SFTP target

1. Backups → Targets → New target
2. Type: SFTP
3. Fill in:
   • Host: backup.example.com
   • Port: 22
   • Username: dockmesh-backups
   • Authentication: SSH key — paste the private key content, or Generate and copy the public key to the SFTP server’s ~/.ssh/authorized_keys
   • Base path: /backups/dockmesh
4. Click Test connection — dockmesh opens a real SFTP session and lists the base path; failures show the actual SSH error
5. Save

Step 2 — Create the backup job

1. Backups → Jobs → New job

2. Name: analytics-nightly

3. Scope: Single stack → analytics

4. Target: SFTP: backup.example.com

5. Pre-backup hook: pick the PostgreSQL preset

   The preset expands to:

   docker exec {{postgres_container}} \
     pg_dumpall -U {{user}} > /var/lib/postgresql/data/dump.sql

   dockmesh fills in {{postgres_container}} and {{user}} from the stack’s service definition. Override if your service isn’t named postgres or the user isn’t postgres.

6. Schedule: visual cron → Daily → 02:00

7. Retention: Keep last 14

8. Encryption passphrase: paste a strong passphrase and store it in your password manager — you cannot recover backups without it

9. Save

Step 3 — Verify the first run

Click Run now on the job. The UI streams progress:

[02:00:01] Running pre-backup hook...
[02:00:04] pg_dumpall completed: 24.7 MB
[02:00:05] Streaming volume: analytics_pgdata (1.1 GB)
[02:00:58] Encrypting with age...
[02:00:59] Uploading to sftp://backup.example.com/backups/dockmesh/analytics-2026-04-17T02-00.tar.age
[02:01:12] ✓ Backup complete — 412 MB, 1m11s

Step 4 — Test the restore (important!)

A backup you haven’t tested isn’t a backup. Pick a test host and:

1. Backups → Browse → analytics-2026-04-17T02-00.tar.age → Restore
2. Destination: test-host, new stack name analytics-restored
3. Enter the encryption passphrase
4. Wait for the stack to come up, then connect to postgres on the test host and verify a few rows

Schedule this drill quarterly.

Troubleshooting

• pg_dumpall fails with “role postgres does not exist” — change the hook’s -U flag to your actual superuser
• SFTP connection timeout — check fail2ban on the backup host; dockmesh retries with backoff and can trip rate limits
• Archive is tiny — the hook ran but the volume path was empty; confirm {{postgres_container}} matched the right container

See also

• Backup & Restore — reference for all target types and schedules
• Alerts — raise an alert if a backup job fails or is skipped