postgres-ha

#Introduction

The postgres-ha Package Skill creates three identical droplets in one region's default VPC — discovered, never configured — and runs PostgreSQL 17 across them as one primary and two hot standbys. Patroni 4.1.5 owns postgresql.conf, pg_hba.conf, the replication slots, the leader lock and the promotion decision, over a three-member etcd 3.5.33 colocated on the same machines. Patroni 4 removed the raft DCS, so an external store is not optional — but it does not have to be a fourth tier of hardware.

#Quick start

npx skills add getcolors/postgres-ha
cp .agents/skills/package-postgres-ha-green/green ./green
./green build
./green create --dry-run
# after approval:
./green create
./green status

The root ./green in a deployment is a copy of the payload launcher, not a symlink. npx skills update rewrites the payload and leaves the root file alone, so the copy is the step that actually moves the pin.

#Workflow architecture

create  start ─ infrastructure ─ dns ─┬─ ansible-local ─ cluster ─ acceptance

delete  start ─ load-infrastructure ─ cluster ─ ansible-local ─ dns
                ─ infrastructure ─ generated-cleanup

Stage names are remote-state keys — <profile>/postgres-ha-infrastructure.tfstate and <profile>/postgres-ha-dns.tfstate — so they must not move once a deployment exists.

Delete begins by reading node addresses out of remote state, because it cannot re-derive them once the droplets are gone.

#Replication and failover

Physical streaming replication with replication slots and quorum synchronous commit (ANY 1): an acknowledged transaction is durable on at least two machines, so a failover loses nothing, and the cluster still accepts writes after losing one node.

etcd is pinned by release tag and by the SHA-256 of its release tarball, so a tampered or truncated download is a failed task rather than a cluster that will not form for reasons nobody can see.

#The client endpoint

cluster-host resolves to all three nodes as DNS-only A records. Every node runs an HAProxy that health-checks Patroni's REST API and forwards port 5432 to whichever node currently holds the leader lock; 5433 reaches the standbys. Every address is therefore a correct answer while its node is up, and libpq tries each resolved address in turn.

Nothing is rewritten during a failover — no DNS call, no cloud API call, and no credential needed at the moment the cluster is degraded.

psql "host=pg-ha.example.com port=5432 user=postgres dbname=appdb connect_timeout=5"
psql "host=pg-ha.example.com port=5433 user=postgres dbname=appdb connect_timeout=5"

A reserved IP was considered and rejected: it puts a provider token on every database node and needs an API call at exactly the moment the cluster is degraded.

#Backups and point-in-time recovery

pgBackRest 2.59 writes to an R2 bucket: a daily full backup plus continuous WAL archiving. Both are leader-gated, so the schedule follows a failover by itself rather than needing to be moved.

WAL-G was evaluated and rejected: it has no check and no alternate-path restore, both of which the verified restore below depends on. pg_dump is not point-in-time recovery at all.

Retention is expressed as a number of full backups. WAL older than the oldest retained full is expired with it, so that number is also roughly the length of the recovery window.

#The verified restore

Every day, on a standby, the package restores the newest backup into a scratch directory, replays every archived segment, starts the result on a spare port, and requires the newest heartbeat row in the restored copy to be inside the configured lag budget.

Replaying to the end is the point. A drill using recovery_target=immediate would restore the base backup and stop — and would pass with WAL archiving completely broken. Replaying forward is the only assertion that distinguishes the backup restored from the backup restored and the point-in-time material on top of it was real.

The heartbeat is written only by the leader, once a minute, and read over a unix socket with peer authentication — which is why the check needs no third credential.

#Desired state

Validation reports every problem at once and exits 2. It refuses every VPC identifier — the regional default VPC is discovered by an OpenTofu data source — and it refuses cloudflare-proxied: true, because Cloudflare's proxy speaks HTTP and not the PostgreSQL wire protocol.

#Command reference

Operator verbs dispatch over SSH and take --node N to pick a live node when the cluster is degraded. The default is node 1, which is the wrong choice precisely when node 1 is the one that died.

#State and output

.colors/<profile>/
├── postgres-ha-infrastructure/  backend.tf.json  main.tf
├── postgres-ha-dns/             backend.tf.json  main.tf
├── postgres-ha-ansible-local/   local playbook and inventory
├── postgres-ha-cluster/         playbooks, templates, files/
└── postgres-ha-acceptance/      live assertions run after converge

Remote state is <profile>/postgres-ha-infrastructure.tfstate and <profile>/postgres-ha-dns.tfstate. The work directory is generated output: never edit it, never read it as source, never commit it.

#Credentials and safety

• DigitalOcean: COLORS_PAR_DO_TOKEN — droplets and firewall. No provider token is placed on a database node.
• Cloudflare: COLORS_PAR_CLOUDFLARE_API_TOKEN.
• State bucket and backup bucket use separate R2 key pairs. A leaked backup key must not be able to rewrite the infrastructure state that describes where the cluster is.
• Database: COLORS_PAR_POSTGRES_ADMIN_PASSWORD and COLORS_PAR_POSTGRES_REPLICATION_PASSWORD, and nothing else. The heartbeat and the verified restore use peer authentication over a unix socket, which is how the design avoids a third credential.
• Never export COLORS_PAR_PROFILE. The profile keys both the remote state and the backup repository path, so overlaying it would point one deployment at another's history.
• Keep compute-prevent-destroy: true in committed desired state. Lift it for one authorized delete with COLORS_PAR_COMPUTE_PREVENT_DESTROY=false; never edit the committed flag.
• The PostgreSQL port is a genuinely public port. Keep digitalocean-client-sources narrow — validation refuses 0.0.0.0/0 on it.

#Recovery procedure

Three cases, in increasing order of how much you have to do.

1. A lost standby is re-cloned by Patroni with no operator action.
2. A lost primary is promoted automatically within roughly patroni-ttl seconds. The old node rewinds and follows when it returns, fetching whatever it missed from the R2 repository through its restore_command.
3. A lost cluster is rebuilt from R2 with pgbackrest restore, optionally to a chosen point in time.

pgbackrest --stanza=main info
pgbackrest --stanza=main restore
# or, to a point in time:
pgbackrest --stanza=main --type=time --target='2026-08-17 09:14:00+00' restore

The full procedure is in the configuration reference. The heartbeat table is the quickest way to confirm you landed where you meant to: it carries the writing node's name and a timestamp, once a minute, written only by whichever node held the leader lock at the time.