PostgreSQL backup and recovery with WAL shipping and basebackup on disk‑limited VPS: low local disk footprint

By · Published on · 9 min read

low buckets and small stools lined up near windows, lit by natural light
Clean image without text, suggests storage and limited space Photo: Jimmy Liao / Pexels

A practical PostgreSQL backup and recovery guide for VPS with limited disk. You’ll find concise configs, scripts and pragmatic decisions to save local space. The focus is small basebackups, compressed WAL shipping to external storage, and testable point‑in‑time recovery (PITR) — no unrealistic promises.

Scenario: small VPS disks and what really matters

On small VPS instances, disk is often the bottleneck. Full local backups quickly fill the volume and make restores harder.

The solution is to reduce the local footprint. Ship WAL history off‑host and keep basebackups compact.

Goal: a recent basebackup plus a continuous stream of archived WALs — both kept off the main disk.

Signs you need this: out‑of‑space failures, growing pg_wal, or slow restores due to very large copies.

In the sections that follow we cover practical choices: compression, destinations, retention and automation with safety in mind.

  • Avoid long retention on the local disk
  • Ship WALs compressed to external storage
  • Run basebackup with WAL excluded (-X none)
  • Regularly test restores and PITR

Recommended architecture: basebackup + compressed WAL shipping

Use two streams: 1) periodic basebackup and 2) continuous, compressed WAL shipping. Both go to external storage.

Keep only essentials on the local disk. The WAL history does not need long local retention beyond the minimum.

Bucket lifecycle policies control costs and retention safely.

If you already use S3‑compatible storage, integrate this with snapshots and incremental uploads as in LVM snapshots + Restic to S3. For setups that require fine‑grained PITR, this WAL flow complements that approach.

This design meets the goals of keeping basebackups small and enabling reliable recovery on a VPS with limited disk.

  • Periodic basebackup: the restoration starting point
  • Continuous WAL shipping: covers the interval between basebackups
  • Retention governed by bucket policies
  • Automatic alerts on archive failures

Configure PostgreSQL for efficient archiving (without filling the disk)

Enable archiving and set an archive_command that compresses and transfers WAL files off‑host. On PostgreSQL 13+, prefer a low wal_keep_size.

On older versions, tune wal_keep_segments. Avoid aggressive local retention if your goal is to save disk.

Use a wrapper script for archive_command. It handles compression, upload and logging, and simplifies maintenance.

# postgresql.conf (excertos)
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/pg-archive-wal %p %f'
max_wal_size = 1GB                # ajuste conforme sua carga
# Em PG >= 13
wal_keep_size = 64MB              # manter o mínimo necessário
# Em PG <= 12 use wal_keep_segments apropriadamente baixo
# Opcional, se disponível na sua versão:
# wal_compression = on            # reduz tamanho do WAL gerado

archive_command wrapper: compression + upload (S3 or SSH)

archive_command must be idempotent and fast. If it fails, PostgreSQL retries.

Never remove local files before confirming upload. Below is a wrapper using zstd and two destinations: S3‑compatible and a remote server via SSH.

Adjust variables and paths. Log actions and always check exit codes.

#!/usr/bin/env bash
# /usr/local/bin/pg-archive-wal
# Uso: pg-archive-wal <path_absoluto_wal> <nome_arquivo_wal>
set -euo pipefail
PATH=/usr/local/bin:/usr/bin:/bin
SRC_PATH="$1"
WAL_NAME="$2"
LOG=/var/log/postgresql/wal-archive.log
COMP_LEVEL=${COMP_LEVEL:-3}          # trade-off CPU x compactação
DEST_MODE=${DEST_MODE:-s3}           # s3|ssh

case "$DEST_MODE" in
  s3)
    : "${S3_BUCKET:?Defina S3_BUCKET}"
    # Envia fluxo comprimido (stdin) para bucket S3-compatível
    zstd -T0 -${COMP_LEVEL} -c -- "$SRC_PATH" \
      | aws s3 cp - "s3://${S3_BUCKET}/wal/${WAL_NAME}.zst" --only-show-errors \
      && echo "$(date -Is) OK S3 ${WAL_NAME}" >> "$LOG"
    ;;
  ssh)
    : "${SSH_HOST:?Defina SSH_HOST}" "${SSH_PATH:?Defina SSH_PATH}"
    zstd -T0 -${COMP_LEVEL} -c -- "$SRC_PATH" \
      | ssh -o StrictHostKeyChecking=yes "$SSH_HOST" \
        "cat > '${SSH_PATH}/wal/${WAL_NAME}.zst'" \
      && echo "$(date -Is) OK SSH ${WAL_NAME}" >> "$LOG"
    ;;
  *)
    echo "Modo de destino inválido: $DEST_MODE" >&2; exit 2;;
esac

# Importante: NÃO remover o arquivo de origem; o PostgreSQL gerencia.

Slim basebackup (-X none) and direct upload to external storage

Run a CHECKPOINT before the basebackup and exclude WALs from the basebackup (-X none). You avoid duplicating WAL data.

Compress in a stream and send directly to the external destination. Test your PostgreSQL version: some flags change between releases.

If you prefer creating the archive locally first, compress after the backup finishes and then upload. This reduces the inconsistency window.

# 1) Checkpoint para estabilizar o backup
psql -U postgres -d postgres -c "CHECKPOINT;"

# 2) Basebackup local, formato tar, sem WAL incluso; compacta depois
pg_basebackup -U postgres -Ft -X none -D /backups/base/$(date +%F) -v --progress
find /backups/base -type f -name "*.tar" -print0 | xargs -0 -I{} zstd -T0 -3 {}
aws s3 cp /backups/base/ s3://$S3_BUCKET/base/ --recursive --exclude "*" --include "*.zst"

# 3) Alternativa: enviar em stream direto para o bucket
pg_basebackup -U postgres -Ft -X none -D - -v --progress \
  | zstd -T0 -3 \
  | aws s3 cp - s3://$S3_BUCKET/base/$(date +%F).tar.zst --only-show-errors

# 4) Opcional: limitar taxa de leitura para proteger o I/O do servidor.

How to restore and perform PITR using archived WALs (step by step)

The process: restore the basebackup, prepare the data directory and set restore_command to fetch archived WALs.

To recover to a specific instant (PITR), set a recovery_target. Warning: these steps remove data from the current directory. Test first.

For a practical case of recovering an accidentally dropped table, see Recover a Dropped PostgreSQL Table with PITR.

# 1) Parar o PostgreSQL e preparar diretório
sudo systemctl stop postgresql
sudo rm -rf "$PGDATA"/*   # ATENÇÃO: isso apaga dados atuais

# 2) Restaurar o basebackup
aws s3 cp s3://$S3_BUCKET/base/2023-12-01.tar.zst - \
  | zstd -d \
  | tar -x -C "$PGDATA"

# 3) Configurar restore_command e (opcional) PITR
cat >> "$PGDATA/postgresql.auto.conf" <<'EOF'
restore_command = 'aws s3 cp s3://BUCKET/wal/%f.zst - | zstd -d -c > %p'
recovery_target_time = '2023-12-01 14:32:00+00'   # comente para recuperar até o fim do WAL
EOF

# 4) Criar recovery.signal (PG >= 12)
touch "$PGDATA/recovery.signal"
chown -R postgres:postgres "$PGDATA"
chmod 700 "$PGDATA"

# 5) Subir e acompanhar logs
sudo systemctl start postgresql
# Acompanhe logs até o término do replay e remoção de recovery.signal

Retention, compression and costs: practical trade‑offs

With limited disk, prioritize compression in archive_command and short retention in the bucket. Compression uses CPU; choose moderate levels like zstd -3.

Set lifecycle policies in the bucket to expire older WALs after a new valid basebackup. Cheaper storage classes lower costs but increase restore latency.

For an incremental, encrypted pipeline of mixed files, see Encrypted Incremental S3 Backups for Linux VPS.

  • Suggested routine: weekly basebackup + continuous WAL shipping
  • WALs should only cover the interval between basebackups, nothing more
  • Policy: expire WALs after a confirmed new basebackup
  • Compression: moderate levels to avoid competing with CPU
Option Disk impact CPU impact Risk When to use
No compression Higher disk usage Low Higher storage cost and slower restores When CPU is extremely constrained
gzip medium level Medium Medium Slower uploads if CPU saturates Balanced environments
zstd level 3 Low Low/Medium Additional CPU during peaks Good compression/performance balance
Cold storage class Lower cost N/A Higher retrieval latency Rarely accessed backups

Monitor archiving, validate restores and final checklist

Without monitoring, archiving can silently fail for days. Watch pg_stat_archiver, pg_wal size and dates in the bucket.

Schedule test restores in an isolated environment and integrity checks. Combine with a VPS hardening checklist in VPS Linux Security Checklist.

Use the checklist below to finalize the setup with a focus on disk usage and reliable recovery.

  • archive_mode=on and archive_command tested under network failure
  • Compression enabled at a moderate level and validated under peak load
  • Periodic basebackup with -X none and a real recovery test
  • Expiration policies in the bucket aligned with your PITR window
  • Alerts for archiving delays and pg_wal growth
  • Documented, versioned and reviewed restore procedure
-- Verificar atividade do archiver
SELECT now() AT TIME ZONE 'utc' AS now_utc,
       last_archived_wal,
       last_archived_time AT TIME ZONE 'utc' AS last_archived_utc,
       failed_archives, stats_reset
FROM pg_stat_archiver;

-- Estimar volume de WAL local (via shell)
-- du -sh $PGDATA/pg_wal
-- Listar objetos no bucket por data

When this approach is not enough and possible alternatives

If write throughput is very high, upload and retention costs can rise quickly. Reduce basebackup intervals or apply more aggressive compression outside peak times.

If a bucket is not an option, use a dedicated backup server over SSH. Combine that with periodic verification and automated rotation.

Integrate with snapshots and incremental uploads when it makes sense, as shown in LVM snapshots + Restic to S3 (Debian).

Frequently asked questions

What is the ideal frequency for basebackup on a VPS with limited disk?

It depends on write volume and the retention window you need. In many cases, a weekly basebackup plus continuous WAL shipping balances disk usage and recovery time. Adjust according to bucket costs and your RPO/RTO targets.

Does WAL shipping consume a lot of CPU because of compression?

With moderate compression like zstd level 3, the impact is usually acceptable. During peaks, lower the compression level or serialize uploads. Monitor CPU saturation and tune to your application's profile.

Is it safe to use -X none with pg_basebackup?

Yes, provided archiving works and you retain enough WAL to cover from the basebackup start to the intended recovery point. Without archived WALs the restore is limited to the basebackup snapshot.

How to handle failures when uploading to the bucket?

archive_command should return an error so PostgreSQL retries. Log failures, monitor pg_stat_archiver and set alerts. Prolonged failures will grow pg_wal; make sure you have temporary disk headroom and a recovery procedure.

Can I encrypt files before sending them?

Yes. You can encrypt the stream before upload or use server‑side encryption in the bucket. Consider CPU impact and key management. For S3‑compatible storage, enable default encryption policies where possible.

Want to validate your backup and recovery strategy?

If you run PostgreSQL on VPS with limited disk and need reliable PITR, the next step is reviewing your scripts and performing restoration tests.

Discuss your scenario

See also