DEV Community

Elder Fernandes
Elder Fernandes

Posted on Originally published at selfhoststack-8z4.pages.dev

Production-Ready PostgreSQL on a VPS: Automated Backups with pgBackRest, Patroni HA, and Docker

#database #devops #selfhosted #postgres

Production-Ready PostgreSQL on a VPS: Automated Backups with pgBackRest, Patroni HA, and Docker

Managed database cloud services (AWS RDS, Google Cloud SQL, Supabase Cloud) charge eye-watering markups on RAM, storage IOPS, and data egress. A modest 16 GB RAM managed database cluster often costs upwards of $250–$600/month.

On high-performance NVMe cloud VPS providers (like Hetzner, Vultr, or DigitalOcean), the same hardware costs €15–$35/month—a 90%+ infrastructure discount.

However, running production PostgreSQL on your own server requires three non-negotiables:

  1. Automated WAL Archiving & Point-In-Time Recovery (PITR) (pgBackRest)
  2. Connection Pooling & Query Routing (PgBouncer)
  3. Automated Failover & High Availability (Patroni / Raft)

Here is the blueprint for a bulletproof, production-grade self-hosted PostgreSQL setup.

Architecture Blueprint 

[ Application Traffic ]
         │
         ▼
[ PgBouncer (Port 6432) ]  ───> Connection pooling, transaction reuse
         │
         ▼
[ PostgreSQL 17 (Port 5432) ] ───> Primary DB Instance
         │
         ├───> [ WAL Stream (Continuous) ] ───> [ pgBackRest ] ───> [ Encrypted S3 / MinIO / Garage ]
         │
         └───> [ Streaming Replication ] ───> [ Standby Replica ] (Patroni Managed)
Enter fullscreen mode Exit fullscreen mode

1. Automated Point-in-Time Recovery with pgBackRest

Standard pg_dump is insufficient for production databases over 50 GB because it locks tables, impacts CPU, and cannot restore to a specific second before a disastrous DROP TABLE query.

pgBackRest provides:

  • Page-level differential and incremental backups
  • Real-time Write-Ahead Log (WAL) archiving to S3 or object storage
  • Point-In-Time-Recovery (PITR) with zero data loss
  • Parallel compression (Zstandard/LZ4) and encryption at rest

Minimal Production pgbackrest.conf: 

[global]
repo1-type=s3
repo1-s3-endpoint=s3.eu-central-003.backblazeb2.com
repo1-s3-bucket=my-production-pg-backups
repo1-s3-region=eu-central-003
repo1-s3-key=YOUR_S3_ACCESS_KEY
repo1-s3-key-secret=YOUR_S3_SECRET_KEY
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=YOUR_SUPER_SECURE_PASSPHRASE
repo1-retention-full=4
repo1-retention-diff=14
process-max=4
compress-type=zst
compress-level=6
log-level-console=info
log-level-file=detail

[maindb]
pg1-path=/var/lib/postgresql/data
pg1-user=postgres
pg1-port=5432
Enter fullscreen mode Exit fullscreen mode

Automated Backup Cron / Systemd Schedule:

  • Full Backup: Every Sunday at 02:00 UTC (pgbackrest --stanza=maindb --type=full backup)
  • Differential Backup: Daily at 02:00 UTC (pgbackrest --stanza=maindb --type=diff backup)
  • Continuous WAL Archive: Triggered automatically via archive_command = 'pgbackrest --stanza=maindb archive-push %p'

2. Fast Connection Pooling with PgBouncer

PostgreSQL spawns a separate OS process for each client connection (~10 MB RAM per connection). Under spike loads, hundreds of concurrent connections can exhaust memory and trigger catastrophic context-switching latency.

PgBouncer sits in front of Postgres, maintaining a pool of warm database connections and recycling them instantly in transaction mode.

Docker Compose Configuration: 

version: '3.8'

services:
  postgres_master:
    image: postgres:17-alpine
    container_name: postgres_primary
    restart: unless-stopped
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=primary_db_secret_password
      - POSTGRES_DB=app_production
    volumes:
      - ./pgdata:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
    command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]
    networks:
      - db_backend

  pgbouncer:
    image: edoburu/pgbouncer:latest
    container_name: pgbouncer_pool
    restart: unless-stopped
    environment:
      - DB_USER=postgres
      - DB_PASSWORD=primary_db_secret_password
      - DB_HOST=postgres_primary
      - DB_PORT=5432
      - DB_NAME=app_production
      - POOL_MODE=transaction
      - MAX_CLIENT_CONN=1000
      - DEFAULT_POOL_SIZE=50
      - RESERVE_POOL_SIZE=10
    ports:
      - "127.0.0.1:6432:6432"
    depends_on:
      - postgres_master
    networks:
      - db_backend

networks:
  db_backend:
    driver: bridge
Enter fullscreen mode Exit fullscreen mode

3. High Availability with Patroni

For mission-critical production workloads requiring automatic failover, Patroni uses a Distributed Consensus Store (DCS) like etcd or Raft to coordinate health checks and elect a new primary node in under 10 seconds if hardware failure occurs.

Cost Comparison: Cloud Managed DB vs Self-Hosted VPS

Configuration AWS RDS (db.r6g.xlarge - 32GB RAM, Multi-AZ) DigitalOcean Managed DB (32GB RAM, Standby) Self-Hosted on Hetzner CCX33 (8 Dedicated vCPU, 32GB RAM NVMe)
Monthly Compute ~$520.00 ~$360.00 €52.40 (~$57.00)
Backup Storage $0.095/GB Included (limited) $0.005/GB (Backblaze B2 S3)
Data Transfer $0.09/GB Standard limits 20 TB Included Free
Estimated Annual Cost $6,800+/year $4,400+/year ~$720/year
Annual Savings — — Save $3,600 to $6,000+ per year (85-90%)

Ready to Deploy Battle-Tested Infrastructure?

Explore complete hardware requirements and self-hosted database alternatives on SelfHostStack.

To get ready-to-run Docker Compose stacks, automated S3 backup scripts, pgBackRest restore playbooks, and Traefik reverse-proxy configurations:

👉 Get the Self-Hosted Starter Stack Pack ($29) — Instant download, production verified, zero ongoing subscriptions.

Top comments (0)