serverhabit.com

Practical server tutorials and cost-conscious self-hosting for indie hackers

PostgreSQL Backup Strategy for Small Servers

by David Park
PostgreSQL Backup Strategy for Small Servers

What You'll End With

By the end of this guide you'll have a working PostgreSQL backup strategy for small servers that runs on Ubuntu 24.04: nightly logical dumps via pg_dump, compressed and timestamped, rotated locally, and uploaded automatically to an S3-compatible bucket (Backblaze B2, Hetzner Object Storage, or AWS S3). You'll also have a tested restore procedure so the backups are actually useful.

Prerequisites:

  • Ubuntu 24.04 VPS with PostgreSQL 16 installed (postgresql-16 package)
  • A database user with at least pg_read_all_data privileges
  • An S3-compatible bucket and credentials (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, endpoint URL)
  • awscli v2 installed (/usr/local/bin/aws)
  • Root or a user with sudo and cron access
  • At least 2× your database size in free disk space on /var/backups

Why Logical Dumps Over Physical Backups on Small Servers

Physical backups (base backup + WAL streaming) are powerful but add operational overhead: you need enough disk for WAL segments, a standby or archive server, and tools like pgBackRest or Barman. On a $5–$20 VPS that's often overkill.

pg_dump produces a self-contained, compressed SQL or custom-format file you can restore on any Postgres version equal to or newer than the source. For databases under ~50 GB, a nightly dump with offsite upload gives you an RPO (recovery point objective) of 24 hours at near-zero cost. If you need a lower RPO, the WAL archiving section at the end extends this guide.

Step 1 — Create a Dedicated Backup User

Never run backups as postgres superuser in scripts. Create a restricted role.

1.1 Open a psql session as the postgres system user:

sudo -u postgres psql

1.2 Create the role and grant read access:

CREATE ROLE backup_user WITH LOGIN PASSWORD 'change_this_strong_password';
GRANT pg_read_all_data TO backup_user;
\q

1.3 Store the password in a .pgpass file so scripts never expose it in process lists:

sudo mkdir -p /var/backups/postgres
sudo bash -c 'echo "localhost:5432:*:backup_user:change_this_strong_password" > /var/backups/postgres/.pgpass'
sudo chmod 600 /var/backups/postgres/.pgpass
sudo chown root:root /var/backups/postgres/.pgpass

The .pgpass file tells libpq to authenticate without prompting or exposing credentials in shell history.

Step 2 — Write the Backup Script

Create /usr/local/bin/pg_backup.sh. This script dumps every non-template database, compresses with gzip, and deletes local copies older than 7 days.

2.1 Create the script:

sudo tee /usr/local/bin/pg_backup.sh > /dev/null << 'EOF'
#!/usr/bin/env bash
set -euo pipefail

### Configuration ###
BACKUP_DIR="/var/backups/postgres"
PGPASS_FILE="${BACKUP_DIR}/.pgpass"
RETENTION_DAYS=7
S3_BUCKET="s3://your-bucket-name/postgres"
S3_ENDPOINT="https://s3.us-west-004.backblazeb2.com"  # change for your provider
DATE=$(date +%Y-%m-%dT%H-%M-%S)
PGUSER="backup_user"
PGHOST="localhost"
PGPORT="5432"

export PGPASSFILE="${PGPASS_FILE}"

### Create today's backup directory ###
MKDIR_PATH="${BACKUP_DIR}/${DATE}"
mkdir -p "${MKDIR_PATH}"

### Dump each database ###
DATABASES=$(psql -U "${PGUSER}" -h "${PGHOST}" -p "${PGPORT}" -d postgres \
  -t -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;")

for DB in ${DATABASES}; do
  OUTFILE="${MKDIR_PATH}/${DB}.dump.gz"
  echo "[$(date +%H:%M:%S)] Dumping ${DB} -> ${OUTFILE}"
  pg_dump -U "${PGUSER}" -h "${PGHOST}" -p "${PGPORT}" \
    --format=custom --compress=6 "${DB}" | gzip > "${OUTFILE}"
done

### Upload to S3 ###
echo "[$(date +%H:%M:%S)] Uploading to ${S3_BUCKET}/${DATE}"
aws s3 cp "${MKDIR_PATH}" "${S3_BUCKET}/${DATE}/" \
  --recursive \
  --endpoint-url "${S3_ENDPOINT}" \
  --storage-class STANDARD

### Rotate local backups ###
find "${BACKUP_DIR}" -mindepth 1 -maxdepth 1 -type d \
  -mtime +${RETENTION_DAYS} -exec rm -rf {} \;

echo "[$(date +%H:%M:%S)] Backup complete."
EOF

2.2 Make it executable:

sudo chmod 700 /usr/local/bin/pg_backup.sh

2.3 Set your S3 credentials as environment variables for the root cron environment. Edit /etc/environment to add them, or use a dedicated credentials file:

sudo mkdir -p /root/.aws
sudo tee /root/.aws/credentials > /dev/null << 'EOF'
[default]
aws_access_key_id = YOUR_ACCESS_KEY_ID
aws_secret_access_key = YOUR_SECRET_ACCESS_KEY
EOF
sudo chmod 600 /root/.aws/credentials

Step 3 — Schedule with Cron

Run the backup at 02:30 UTC every night. Root's crontab ensures access to the .pgpass file owned by root.

3.1 Open root's crontab:

sudo crontab -e

3.2 Add this line:

30 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

The >> appends both stdout and stderr to a single log file you can tail when troubleshooting.

3.3 Verify the cron entry was saved:

sudo crontab -l

Expected output:

30 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Step 4 — Test a Manual Run and Restore

A backup you've never restored is not a backup. Run the script manually and immediately test a restore.

4.1 Run the script now:

sudo /usr/local/bin/pg_backup.sh

Expected output (example with a myapp database):

[02:30:01] Dumping myapp -> /var/backups/postgres/2025-01-15T02-30-01/myapp.dump.gz
[02:30:04] Uploading to s3://your-bucket-name/postgres/2025-01-15T02-30-01
upload: .../myapp.dump.gz to s3://your-bucket-name/postgres/2025-01-15T02-30-01/myapp.dump.gz
[02:30:07] Backup complete.

4.2 Restore into a test database to verify integrity:

sudo -u postgres createdb myapp_restore_test

zcat /var/backups/postgres/$(ls -t /var/backups/postgres | head -1)/myapp.dump.gz \
  | sudo -u postgres pg_restore --dbname=myapp_restore_test --no-owner --role=postgres

4.3 Spot-check row counts match your production database:

sudo -u postgres psql -d myapp_restore_test -c "\dt"

4.4 Drop the test database when done:

sudo -u postgres dropdb myapp_restore_test

Step 5 — Optional: WAL Archiving for Lower RPO

If 24-hour RPO is too long (e.g., high-transaction e-commerce), enable WAL archiving to ship WAL segments to S3 continuously. This reduces RPO to minutes.

5.1 Edit /etc/postgresql/16/main/postgresql.conf:

sudo nano /etc/postgresql/16/main/postgresql.conf

Set or uncomment these lines:

wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://your-bucket-name/wal/%f --endpoint-url https://s3.us-west-004.backblazeb2.com'
archive_timeout = 300

archive_timeout = 300 forces a WAL segment switch every 5 minutes even on low-traffic servers, capping your RPO at ~5 minutes.

5.2 Reload PostgreSQL:

sudo systemctl reload postgresql@16-main.service

5.3 Confirm archiving is active:

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('archive_mode','wal_level','archive_command');"

Expected output:

     name      |                          setting
---------------+----------------------------------------------------------
 archive_command| aws s3 cp %p s3://your-bucket-name/wal/%f ...
 archive_mode  | on
 wal_level     | replica

Note: WAL archiving works alongside nightly pg_dump. The dumps act as base restore points; WAL segments let you replay forward to any point in time between dumps.

Verify It Works

Run each check after initial setup and after any server change:

# 1. Confirm local dump files exist and are non-zero
ls -lh /var/backups/postgres/$(ls -t /var/backups/postgres | head -1)/

# 2. Confirm S3 upload succeeded
aws s3 ls s3://your-bucket-name/postgres/ --endpoint-url https://s3.us-west-004.backblazeb2.com

# 3. Check last backup log for errors
tail -50 /var/log/pg_backup.log

# 4. Verify .pgpass permissions (must be 600)
stat -c "%a %U" /var/backups/postgres/.pgpass
# Expected: 600 root

Troubleshooting

pg_dump: error: connection to server failed: FATAL: password authentication failed The .pgpass file permissions are wrong or the path in PGPASSFILE doesn't match. Run stat /var/backups/postgres/.pgpass — it must be 600 and owned by the user running the script.

aws: command not found in cron Cron uses a minimal PATH. Either use the full path /usr/local/bin/aws in the script, or add PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin at the top of the cron file.

Dump file is 0 bytes set -euo pipefail will abort on errors, but check /var/log/pg_backup.log for the exact pg_dump error. Common causes: backup_user lacks pg_read_all_data, or the database name contains a space (wrap ${DB} in quotes — the script already does this).

S3 upload fails with SignatureDoesNotMatch Your endpoint URL doesn't match the bucket region. Confirm the endpoint in your storage provider's dashboard and update S3_ENDPOINT in the script.

Local disk fills up before rotation runs Reduce RETENTION_DAYS or move the backup directory to a separate volume. Check current usage with du -sh /var/backups/postgres/*/.

archive_command fails silently PostgreSQL retries failed archive commands indefinitely. Check sudo tail -100 /var/log/postgresql/postgresql-16-main.log for archive command failed lines. The most common cause is missing AWS credentials for the postgres system user — add /var/lib/postgresql/.aws/credentials with the same content as /root/.aws/credentials, owned by postgres:postgres with mode 600.

Next Steps

This PostgreSQL backup strategy for small servers covers the 80% case at minimal cost. From here:

  • Add alerting: Pipe backup failures to a Slack webhook or email using a wrapper script that checks exit codes.
  • Encrypt dumps at rest: Pipe pg_dump output through gpg --symmetric before uploading to S3 if your bucket is not private.
  • Test restores monthly: Schedule a monthly cron job that downloads the latest dump, restores to a throwaway database, runs a row-count query, and logs the result.
  • Upgrade to pgBackRest: Once your database exceeds 50 GB or you need point-in-time recovery with a proper catalog, pgbackrest on a second Hetzner VPS (~€4/month) is the next logical step.