Self-Hosted PostgreSQL HA with pg_auto_failover and WAL-G

How do you set up PostgreSQL high availability on a self-hosted server?

The short answer: run a pg_auto_failover cluster — a dedicated monitor node plus one or more streaming replicas. When the primary dies, the monitor picks the replica with the freshest WAL and promotes it. Apps connect through a stable hostname that updates automatically.

The longer answer is that wiring all of this up by hand (container networking, DNS, WAL archiving, promotion scripts) takes days and breaks in subtle ways. Temps v0.1.0 ships it as a built-in feature: click "New Service → Postgres → Topology: Cluster" and you get a production-ready HA cluster managed from the same UI you use for deploys.

TL;DR: Temps v0.1.0 makes self-hosted Postgres HA a one-click operation. You get pg_auto_failover clusters, automatic and manual failover, role-aliased DNS (primary.<cluster>.temps.local) that follows the primary across failovers, WAL-G v3.0.8 continuous WAL backups to any S3-compatible bucket, and in-place restore. Coolify and Dokploy run a single Postgres container with no HA story. AWS RDS has HA but runs at cloud-provider prices — see the AWS RDS pricing page to compare against Hetzner or DigitalOcean bare-metal rates.

What does PostgreSQL high availability actually require?

Production HA is more than running two Postgres containers. You need:

1. A health monitor that detects primary failure — not just a load balancer health check, but something that understands Postgres streaming replication state.
2. Automatic promotion — when the primary is gone, promote the most up-to-date replica and block the old primary from accepting writes (split-brain prevention).
3. Connection routing that updates — your app's connection string must point to the new primary after failover, without a redeploy.
4. Continuous WAL archiving — streaming replication alone doesn't protect against accidental DROP TABLE. You need WAL shipped to object storage for point-in-time recovery.

Each of these is solvable. The challenge is integrating all four into something that survives control-plane restarts, node failures, and operator error. That's what Temps v0.1.0 ships.

How Temps compares to other self-hosted options

Coolify and Dokploy run a Postgres container and stop there. AWS RDS Multi-AZ is mature and battle-tested, but it's cloud-only, priced per instance-hour, and locked to AWS infrastructure. Temps closes the gap for teams running on Hetzner, DigitalOcean, or their own hardware.

pg_auto_failover — the engine under the hood

pg_auto_failover is a PostgreSQL extension that manages a small cluster through a dedicated monitor node. The monitor tracks every data member's health (replication state, LSN position, heartbeat freshness) and triggers state transitions when members deviate from expected states.

The cluster has three roles:

1. Monitor — a Postgres instance running the pg_auto_failover extension. It's the decision-maker: it holds the FSM state for every node, detects failure, and initiates promotion.
2. Primary — the active read-write node.
3. Replica — one or more streaming replicas. Asynchronous by default; the underlying tool supports synchronous too.

When the monitor detects the primary has gone away (configurable timeout, no heartbeat), it selects the replica with the highest LSN and issues a promotion command. The old primary, if it reconnects, re-registers as a replica and starts catching up.

Quotable: "Temps wraps pg_auto_failover — a battle-tested PostgreSQL extension used in production at multiple scales — with a management UI, role-aliased DNS, and WAL-G physical backups. The plumbing is proven; Temps handles the operational overhead."

DNS routing: <cluster>.temps.local follows the primary

The hardest part of self-hosted HA is connection routing. A naïve setup points your app at a fixed IP — which breaks after failover. Managed services solve this with a stable endpoint that always routes to the primary. Temps does the same thing with its internal DNS plane.

Every cluster gets three tiers of DNS records:

• Per-member A records — pg-1.<cluster>.temps.local, pg-2.<cluster>.temps.local, etc. Stable, one per container, never change.
• Role-aliased VIPs — primary.<cluster>.temps.local always resolves to the current primary. replica.<cluster>.temps.local resolves to a replica. <cluster>.temps.local round-robins across all data members.
• Underlay fallback — for single-host setups where no overlay network exists, the record points to the node's underlay address plus host port.

A background reconciler runs every 5 seconds per cluster. It queries the pg_auto_failover monitor for current role assignments and updates the role-aliased VIPs to match. After a failover, DNS is updated within the next reconciler tick — typically within 30 seconds.

Apps connect to <cluster>.temps.local with target_session_attrs=read-write in their libpq connection string. They keep that connection string forever. After failover, they reconnect on their next query and land on the new primary automatically.

Quotable: "Temps's internal DNS reconciler runs every 5 seconds, queries pg_auto_failover for the current primary, and updates role-aliased DNS VIPs. Standard libpq clients reconnect without code changes after a failover."

Adding replicas and triggering failover

Adding a replica: click "Add Replica" on a cluster's storage page. This opens /storage/:id/members/add — a dedicated page (not a modal, because provisioning takes a minute and modals make users nervous about navigating away). A 4-step timeline shows provisioning progress in real time:

1. Allocate ordinal and host port
2. Pull and start the gotempsh/postgres-ha:18-bookworm-walg container
3. Run pg_autoctl create postgres to register the new replica with the monitor
4. Wait for the replica to reach secondary state

If anything fails mid-way, the UI shows the failure step and its error. The failed member row is removable with one click — cleanup removes the container, volume, and DNS record atomically.

Manual failover: every replica row in the cluster table has a Promote button. Clicking it runs pg_autoctl perform promotion inside the container. The monitor then demotes the current primary, and the reconciler updates DNS on its next tick. The UI enforces validation server-side: you cannot promote the monitor, the current primary, or trigger a promotion while another is in flight.

WAL-G physical backups with continuous WAL streaming

How do you do point-in-time recovery for a self-hosted Postgres cluster?

Temps uses WAL-G v3.0.8 baked into gotempsh/postgres-ha:18-bookworm-walg (multi-arch, amd64 + arm64). Clicking the Backup button on a cluster service:

1. Finds the current primary member.
2. Writes walg.env (S3 credentials, bucket prefix, encryption key) to every running data member — so failover doesn't silently break WAL archiving.
3. Runs wal-g backup-push against the primary for a full base backup.
4. Enables continuous WAL streaming via ALTER SYSTEM SET archive_command in postgresql.auto.conf.

postgresql.auto.conf is replicated through streaming replication. A future failover automatically inherits the archive_command on the promoted replica — no manual reconfiguration.

Backups go to any S3-compatible bucket. Configure once via Temps's Default S3 Source feature and reuse across all services.

Quotable: "Temps WAL-G backup setup: one click writes walg.env to every cluster member, takes a base backup via wal-g backup-push, and enables continuous WAL streaming that survives failover because archive_command replicates through streaming replication."

In-place restore

Restore from a cluster backup works as follows:

1. Stop and remove every member container.
2. Delete every data volume.
3. Remove all DNS records and service_members rows.
4. Pre-seed the new primary's pgdata directory via wal-g backup-fetch in a one-shot helper container. Write recovery.signal and restore_command so Postgres replays WAL to consistency on first start.
5. Re-run initialize_cluster so the rebuilt cluster picks up the recovered data.

The cluster comes back with the same names, ordinals, and FQDNs. Apps connecting to <cluster>.temps.local reconnect transparently — no connection string updates, no redeploys.

Current limitation: in-place restore is single-host only in v0.1.0. Multi-host restore requires an agent-side helper-container RPC that didn't make this release. The UI refuses multi-host restore with a clear message rather than silently doing the wrong thing.

Quorum protection and safe member removal

The UI prevents operations that would silently break the cluster:

• Cannot remove the monitor — it's a singleton; removing it ends the cluster.
• Cannot remove the current primary — fail over first, then remove.
• Cannot remove a replica if it would drop the cluster below 2 data members — HA requires at least primary + 1 replica for any meaningful protection.

Validation runs server-side. The UI buttons are disabled when the action would be rejected, but the backend also enforces these rules independently.

What this replaces and what it doesn't

Temps Postgres HA replaces:

• Manual pg_auto_failover setup (container networking, certificates, monitor registration scripts)
• Custom failover notification scripts and DNS update hooks
• Separate WAL-G configuration per node
• Homemade restore playbooks

It doesn't replace:

• Connection pooling — for high-connection workloads, run PgBouncer or pgcat in front. Temps clusters don't bundle a pooler.
• Major version upgrades — v0.1.0 ships PostgreSQL 18. There's no in-place upgrade path from older clusters yet.
• Synchronous replication UI — the underlying pg_auto_failover supports synchronous replication, but the toggle isn't surfaced in v0.1.0. Configure via direct pg_autoctl access for now.
• Multi-region HA — replicas must be reachable from the monitor. Cross-region setups need WireGuard mesh (which Temps supports) but aren't a first-class workflow yet.

Getting started

# Install or update Temps
curl -sSL https://temps.sh/install.sh | bash

# Run migrations (new in v0.1.0)
temps setup

# Create a Postgres HA cluster from the dashboard:
# Storage → New Service → Postgres → Topology: Cluster
# Then: cluster detail → Add Replica → follow the 4-step timeline

For fresh installs, see the getting started guide.

Temps is free to self-host (Apache 2.0). Temps Cloud runs on Hetzner at cost plus 30% — roughly $6/mo for a single-node control plane, no per-seat fees, no bandwidth bills.

FAQ

How many replicas do I need for PostgreSQL high availability?

Two data members (primary + 1 replica) is the minimum for any failover protection. With two, the cluster can survive one node failure. Three members (primary + 2 replicas) lets you tolerate one replica being down during a failover without losing quorum. Beyond three, additional replicas mostly serve read traffic — consider whether logical replication read replicas are more cost-effective for that use case.

How long does a Postgres failover take with pg_auto_failover?

In Temps, the pg_auto_failover promotion runs as soon as the monitor detects primary failure (configurable timeout). DNS role-aliased VIPs update within 30 seconds (the next reconciler tick). Total disruption — from primary going away to new connections landing on the promoted replica — is typically 30–60 seconds for asynchronous replication. Queries in flight at the moment of failover return connection errors; apps should retry on reconnect.

Can I use pgvector or TimescaleDB on a Temps HA cluster?

Yes. The gotempsh/postgres-ha:18-bookworm-walg base image is postgres:18-bookworm plus pg_auto_failover and WAL-G. Any extension you can install via CREATE EXTENSION works normally — both pgvector and TimescaleDB are compatible with pg_auto_failover.

Where do WAL-G backups go?

Any S3-compatible bucket: AWS S3, Cloudflare R2, Hetzner Object Storage, MinIO, etc. Configure credentials once using Temps's Default S3 Source feature. WAL-G handles upload, optional server-side encryption, and backup metadata.

Is self-hosted Postgres HA safe for production?

The underlying components — pg_auto_failover and WAL-G — are production-grade tools used at scale by teams outside of Temps. Temps's wrapper (provisioning, DNS reconciliation, UI) has been tested through scale-up, scale-down, failover, backup, and restore cycles, including reconciler survival across control-plane restarts. For any new infrastructure component, run it on a non-critical workload first and validate your restore procedure before relying on it for critical data.

Related: Read the full v0.1.0 release notes