DEV Community

Mukesh
Mukesh

Posted on

Zero-Downtime Postgres Failover on Vultr: A Patroni + HAProxy Runbook for Solo Developers

#postgres #vultr #infrastructure #highavailability

The single-instance Postgres problem nobody budgets for

You provisioned a Vultr instance, installed Postgres, pointed your app at it, and shipped. It's been fine for eight months. Then the instance hits a kernel panic during a routine host migration, and you spend forty minutes SSH-ing into a dead box while your app throws connection-refused errors and your on-call phone won't stop buzzing.

Managed Postgres with automatic failover exists -- RDS, Cloud SQL, Vultr's own Managed Database product -- but it costs 2-4x a comparable self-managed instance, and at the point you're running a single primary with maybe one replica, that premium buys you very little you can't build yourself in an afternoon. This is a runbook for exactly that: three small Vultr instances, Patroni for orchestration, etcd for consensus, and HAProxy as the routing layer, wired up so a dead primary is a 10-15 second blip instead of a 3 AM page.

Architecture: three boxes, three roles, one job each

You need three Vultr Cloud Compute instances (2 vCPU / 4GB is enough to start, in the same region for latency):

  • pg1, pg2, pg3 -- each runs Postgres 16 and a Patroni agent
  • All three also run an etcd node, forming the distributed consensus store Patroni uses to agree on who's primary
  • One instance (or a fourth, cheap $6/mo box) runs HAProxy as the single entry point your app connects to

Patroni doesn't do consensus itself -- it delegates leader election to etcd, then reconfigures Postgres (promote, demote, follow-the-new-leader) based on what etcd says. This split is why Patroni is safer than hand-rolled scripts checking pg_is_in_recovery() on a cron: etcd handles the actual hard problem (distributed agreement under network partitions), Patroni just reacts to it.

Step 1: bootstrap etcd

On each of the three nodes:

apt install etcd
Enter fullscreen mode Exit fullscreen mode

Edit /etc/default/etcd (or the systemd env file) with each node's own IP and the cluster peer list:

ETCD_NAME=pg1
ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.1.0.11:2380
ETCD_LISTEN_PEER_URLS=http://10.1.0.11:2380
ETCD_LISTEN_CLIENT_URLS=http://10.1.0.11:2379,http://127.0.0.1:2379
ETCD_ADVERTISE_CLIENT_URLS=http://10.1.0.11:2379
ETCD_INITIAL_CLUSTER=pg1=http://10.1.0.11:2380,pg2=http://10.1.0.12:2380,pg3=http://10.1.0.13:2380
ETCD_INITIAL_CLUSTER_STATE=new
ETCD_INITIAL_CLUSTER_TOKEN=pg-cluster-1
Enter fullscreen mode Exit fullscreen mode

Use Vultr's private networking (VPC 2.0) for these addresses, not public IPs -- etcd traffic is unencrypted by default and there's no reason to expose consensus chatter to the internet. Bring up all three, then confirm quorum:

etcdctl member list
Enter fullscreen mode Exit fullscreen mode

You want three healthy members. With three nodes, etcd tolerates one failure and still has quorum -- that's the whole reason to use three instances instead of two.

Step 2: configure Patroni

Install Patroni and Postgres on all three nodes, then write /etc/patroni.yml on pg1 (adjust name and the local IP per host):

scope: pg-cluster
namespace: /db/
name: pg1

restapi:
  listen: 10.1.0.11:8008
  connect_address: 10.1.0.11:8008

etcd3:
  hosts: 10.1.0.11:2379,10.1.0.12:2379,10.1.0.13:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 5
        max_replication_slots: 5

postgresql:
  listen: 10.1.0.11:5432
  connect_address: 10.1.0.11:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: <use a real secret, not this>
    superuser:
      username: postgres
      password: <use a real secret, not this>
Enter fullscreen mode Exit fullscreen mode

ttl: 30 and loop_wait: 10 control how fast Patroni notices a dead leader -- lower these and you fail over faster, but risk flapping on a slow network blip. 30/10 is a reasonable default for a same-region cluster; don't set ttl below 20 unless you've tested it against your actual network jitter.

Start Patroni (patronictl or via systemd) on pg1 first -- it bootstraps a fresh Postgres cluster. Then start it on pg2 and pg3 -- Patroni sees pg1 already holds the leader lock in etcd and configures them as streaming replicas automatically. No manual pg_basebackup needed.

Check cluster state:

patronictl -c /etc/patroni.yml list
Enter fullscreen mode Exit fullscreen mode

You should see one Leader and two Replica rows, all running.

Step 3: HAProxy as the single connection point

Your app should never connect directly to pg1, pg2, or pg3 by name -- it connects to HAProxy, which routes to whichever node Patroni currently says is the leader. Patroni exposes a REST health endpoint (:8008/primary) specifically for this.

listen postgres_primary
    bind *:5432
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 10.1.0.11:5432 maxconn 100 check port 8008
    server pg2 10.1.0.12:5432 maxconn 100 check port 8008
    server pg3 10.1.0.13:5432 maxconn 100 check port 8008
Enter fullscreen mode Exit fullscreen mode

/primary returns 200 only on the current leader and 503 everywhere else, so HAProxy's health check naturally routes all traffic to whoever holds the lock -- no separate VIP or keepalived setup required. on-marked-down shutdown-sessions is the detail people skip: without it, existing connections to a demoted node stay open and your app keeps writing to what's now a replica until the connection eventually times out.

Testing it before you trust it

Don't take failover on faith -- kill it on purpose:

patronictl -c /etc/patroni.yml stop pg1
Enter fullscreen mode Exit fullscreen mode

Watch patronictl list on pg2 -- it should promote within roughly one ttl cycle (under 30 seconds, usually faster). Confirm HAProxy re-routed with curl against port 5432 or by watching your app's connection logs. Then bring pg1 back:

patronictl -c /etc/patroni.yml start pg1
Enter fullscreen mode Exit fullscreen mode

It should rejoin as a replica automatically via pg_rewind, not require a manual re-clone. If it doesn't rejoin cleanly, that's a sign your WAL retention (wal_keep_size or replication slots) is too small for the gap it fell behind during the outage -- widen it before you consider the cluster production-ready.

What this costs and what it doesn't solve

Three 2vCPU/4GB Vultr instances plus a small HAProxy box runs somewhere around $60-80/month at current Vultr pricing -- a fraction of managed HA Postgres at comparable spec, and one you fully control (extensions, exact Postgres version, tuning).

It doesn't solve everything a managed service does: you're still responsible for backups (pgBackRest or wal-g against Vultr Object Storage is the natural pairing), OS patching, and monitoring etcd's own health, since a broken etcd cluster takes Patroni's decision-making down with it. But for the specific failure mode that actually happens most often -- one node dying and needing a fast, correct promotion -- this setup gets you there without a fourth SaaS bill.

Top comments (0)