PostgreSQL High Availabilty Solutions
/ home / computer / postgresql / postgresql for mysql admins / .
Table of Contents
Details
-
Overview
-
Cluster manager / management software
- Patroni (leader election via etcd/Consul/Zookeeper)
- Patroni, Spock (pgEdge), etcd → fail-safe redundancy, instant failover without hiccups, multi-master system
- repmgr (basic failover management)## Overview
- pg_auto_failover (simplified two-node setups)
- Pgpool-II (basic HA + connection pooling)* Failover solutions
-
Other PostgreSQL high availability setups
- Shared Disk Failover / Active/passive fail-over Cluster
- File System or Block Device Replication (DRBD)
- With shared disk (SAN)
- With DRBD
- pg_stream* Write-Ahead Log (WAL) Shipping
- pgactive (Amazon): PostgreSQL active-active replication:
- Spock multi-master, CloudNativePG or Crunchy PGO on Kubernetes
Overview
- Failover solutions
- Shared Disk Failover
- File System or Block Device Replication (DRBD)
- Write-Ahead Log (WAL) Shipping
- built-in
- sync = on/off
- read-only replicas: hot standby
- continous archiving -> warm standby or log shipping
- file shipping vs. streaming replication
- Logical Replication
- Trigger-Based Replication
- Middleware Based Repllication
- Pgpool-II
- Continuent Tungsten
- Asynchronous Multimaster Replication
- Synchronous Multimaster Replication
Terminology:
- Primary (MySQL: Master/Source)
- Standby (MySQL: Slave/Replica)
Mechanism is through WAL:
- Archived WAL shipping
- Recent WAL streaming
Source: https://www.postgresql.org/docs/current/different-replication-solutions.html
Use cases
- High Availability (HA)
- Disaster failover (remote)
- Redundancy (HA)
- Backup Standby
- Cascaded Replication (Relay)
- Security (WAL shipping)
- Delayed replication (WAL shipping)
- Full-Synchronous replication
- Read-only Standby (read scaleout)
Trigger based replication
Trigger-based replication systems such as Slony, Londiste and Bucardo
PostgreSQL high availability setups
- PostgreSQL high availability setups
- Reasons for high availability
- Availability / fail-over
- Reporting replica
- Backup replica?
- Read scale-out?
- Upgrade path
See also Overview on the very top!!!
Cluster manager
t watches the Distributed Configuration Store (DCS) / leader info (patroni, etcd, Consul, ZooKeeper, etc.).
Patroni
Based on PostgreSQL (physical) Streaming Replication (async or sync).
- Mouhamadou Diaw, dbi services, 2021-02-03: PostgreSQL High Availability: Patroni, Ectd , HAProxy , Keepalived
- Mehman Jafarov, 2025-11-14: PostgreSQL High Availability in Action
- Patroni: Replication modes
- Ibrar Ahmed, pgEdge, 2024-05-22: Achieving PostgreSQL High Availability: Strategies, Tools, and Best Practices
vip-manager
Patroni + etcd HaProxy PgBouncer
etcd or Zookeeper
https://www.dbi-services.com/blog/setup-a-two-node-postgres-ha-cluster-using-edb-advanced-server-and-efm/
https://indico.cern.ch/event/1471762/contributions/6280213/attachments/2998599/5283395/P-DBI-E-20240323-PGconf-Architecture-for-Seamless-PostgreSQL-Upgrades.pdf
https://www.dbi-services.com/blog/the-dbi-services-postgresql-reference-architecture-1-the-commercial-approach/
https://www.dbi-services.com/blog/the-dbi-services-postgresql-reference-architecture-2-the-community-approach/
Where external_pid_file still earns its place is clusterware, the Pacemaker and Red Hat Cluster resource agents that predate that model and still expect a PID file at a known path. It’s just a small piece of glue between PostgreSQL and whatever is responsible for starting and stopping it. https://thebuild.com/blog/all-your-gucs-in-a-row-external_pid_file/