PostgreSQL High Availability¶

PostgreSQL replicates through the WAL (Write-Ahead Log): everything that changes is written to the transaction log first, and it is that log that feeds the standbys, the backup archive and point-in-time recovery. Understanding where the WAL goes is understanding the whole availability strategy of the database.

This page covers HA inside the same datacenter, which solves host, disk, rack and maintenance failures. To reduce latency for applications in another region and distribute data geographically, see Multi-Region PostgreSQL.

InteSys deploys the architecture on Kubernetes, with a PostgreSQL operator, or on virtual machines, with a cluster manager backed by a consensus store. Both run in br-sp-1 (Cirion SAO1) and br-sp-2 (Equinix SP3).

What HA solves — and what it does not¶

The two numbers that define the design:

• RPO — how much data you can afford to lose. With a synchronous standby, zero.
• RTO — how long until you are back. With automatic promotion, tens of seconds.

Topology on Kubernetes¶

For applications already running on Kubernetes, PostgreSQL lives in the cluster itself, under a PostgreSQL operator that treats provisioning, replication, failover, WAL archiving and backup as declarative state.

The operator reconciles the topology; the application uses the -rw and -ro endpoints, and the WAL is archived outside the cluster.

• Separate read and write services — the -rw endpoint always points to the current primary, -ro spreads reads across the standbys. The application reconfigures nothing during a failover.
• Built-in connection pooler — PgBouncer in the path, because PostgreSQL uses one process per connection and hundreds of idle connections cost real memory.
• Mandatory anti-affinity — one database pod per physical node.
• Continuous WAL archiving to object storage, the foundation of point-in-time recovery.
• Low-latency persistent volumes, with encrypted volume snapshots and a defined retention policy.

Topology on virtual machines¶

The cluster manager holds the leader lock in a consensus store; the proxy follows the leader and the application keeps a single address.

• Three database nodes on distinct physical hosts: primary, synchronous standby and asynchronous standby.
• Consensus store with an odd number of members — it decides who the leader is, preventing two nodes from promoting themselves.
• Proxy pair with a VIP — HAProxy or equivalent, routing writes to the leader and reads to the standbys.
• Streaming replication with replication slots, so the primary does not discard WAL a standby has not consumed yet.
• WAL archiving and backup outside the database nodes.

Kubernetes or virtual machines?¶

WAL, RPO and backup¶

The synchronous standby defines the RPO; the WAL archive allows recovery to any point in time; the restore test is what turns a backup into a guarantee.

How failover works¶

1. Detection — the leader lock expires in the consensus store; the decision belongs to the quorum, not to an isolated observer.
2. Fencing — the old primary is demoted and isolated before any promotion, preventing split-brain.
3. Promotion — the synchronous standby takes over; since it already confirmed the transactions, there is no data loss.
4. Redirection — the pooler starts pointing to the new primary, with no application change.
5. Reattachment — the old node returns as a standby after aligning with the new WAL timeline.

Monitoring that comes with the cluster¶

• replication lag in bytes and seconds, per standby;
• leader lock state and promotion history;
• inactive replication slots — the most common cause of a full disk on the primary;
• long transactions, bloat and autovacuum progress;
• connections in use versus the pooler limit;
• backup and test-restore success.

Data sovereignty¶

Every byte in this cluster stays in Brazil. Both InteSys regions are Brazilian — br-sp-1 (Cirion SAO1), in Cotia/SP, and br-sp-2 (Equinix SP3), in the São Paulo metro area — and data only leaves the country if the customer asks for it.

• Data residency in Brazil — primary nodes, replicas, transaction log archives and backups all live in Brazilian datacenters, on infrastructure operated by InteSys.
• LGPD with no international transfer — in the default configuration there is no cross-border transfer of personal data to document, and no additional legal basis to build.
• Single jurisdiction — Brazilian infrastructure, Brazilian contract and a Brazilian operations team, without the exposure to foreign data-access legislation that reaches providers headquartered abroad.
• Latency as a side effect — keeping data close to Brazilian users is a compliance requirement and a performance gain at the same time.
• Sending data abroad is an explicit customer decision — any copy outside the country exists only when requested, and only on an encrypted volume.

Encrypted volume snapshots¶

On top of the base backup plus continuous WAL archiving, a cluster on Kubernetes can use volume snapshots (CSI VolumeSnapshot): a copy of the entire volume, taken in seconds, without scanning the database.

• Consistent snapshots — the operator coordinates the flush and freezes writes before triggering the snapshot, so a restore does not depend on crash recovery.
• Always on an encrypted volume — data is encrypted at rest and the snapshot inherits that encryption; the key is managed by InteSys or supplied by the customer.
• Encrypted in transit — replicating the snapshot to another destination travels encrypted and is stored encrypted at the destination.
• Retention and tested restores — a defined retention policy and restores exercised periodically; a snapshot that has never been restored is a hypothesis, not a backup.
• Fast recovery — a snapshot brings the whole volume back in minutes; the base backup plus the WAL archive is still what rewinds the data to any point in time.

How InteSys deploys it¶

1. Sizing — volume, write profile, extensions in use, RPO/RTO requirements.
2. Topology — Kubernetes or VMs, number of standbys and replication mode.
3. Provisioning — nodes on distinct physical hosts, dedicated storage, PostgreSQL parameters tuned to the workload.
4. Access layer — pooler and separate read and write endpoints.
5. Archiving and backup — continuous WAL and full backup in object storage, outside the nodes.
6. Failure testing — we take the primary down in a controlled environment and measure real RTO.
7. Observability and alerting — metrics, dashboards and alerts wired to the operations team.
8. Runbook — a documented procedure for human intervention when it is needed.

Next Steps¶

• Multi-Region PostgreSQL — Latency, read replicas and logical replication across regions
• MySQL High Availability — The same architecture applied to MySQL
• ClickHouse High Availability — Replicas and shards for analytical workloads
• Kubernetes — Managed clusters where the database can be operated
• InteSys Datacenters — Details of the br-sp-1 and br-sp-2 regions