Patroni is among the most popular open-source tools for managing replication and failover of Postgres clusters. It relies on the Postgres streaming replication and hot standby capabilities. These capabilities allow DBAs to simplify the process of setting up and managing clusters that have high availability and read scalability requirements. EDB has tested Patroni for use with PostgreSQL, EDB Postgres Advanced Server, and EDB Postgres Extended Server.
For the Patroni documentation, see https://patroni.readthedocs.io.
Main high-availability components:
- Postgres cluster — The database cluster, usually consisting of a primary and two or more replicas.
- Patroni — Used as the failover management utility.
- etcd — Used as a distributed configuration store (DCS) containing cluster information, such as configuration, health, and current status.
- HAProxy — Used as a proxy to connect to the read-write and read-only nodes.
EDB currently supports using Patroni and Postgres physical streaming replication with a primary server and two replicas, replicating directly from the primary. We support using either PostgreSQL, EDB Postgres Advanced Server, or EDB Postgres Extended Server.
Install Patroni on the Postgres nodes. The latest release of Patroni is supported.
Etcd is a strongly consistent, distributed key-value store. It provides a reliable way to store data that a distributed system or cluster of machines needs to access.
For more information, see the etcd documentation.
For production environments, we recommend setting up an etcd cluster of three nodes. One etcd cluster can serve multiple Postgres/Patroni clusters. Install etcd nodes on different hosts from the Postgres nodes.
See Installing and configuring etcd to install and set up etcd.
To avoid split-brain situations, Patroni supports watchdog devices.
Watchdog devices are software or hardware mechanisms that reset the whole system when they don't get a keepalive heartbeat within a specified timeframe. This device adds a layer of failsafe in case usual Patroni split-brain protection mechanisms fail.
While the use of a watchdog mechanism with Patroni is optional, we recommend using it for production environments.
Instead of connecting directly to the database server, you can set up HAProxy so the application connects to the proxy instead. The proxy then forwards the request to Postgres. When you use HAProxy for this purpose, you can route read-only requests to one or more replicas for load balancing.
We strongly suggest using HAProxy to avoid data loss in case of split-brain.
You can install HAProxy as an independent server, but you can also install it on the application server or the database server.
For production environments, we recommend setting up at least two HAProxy servers. Using various ports, one HAProxy server can serve multiple Postgres/Patroni clusters.
Security considerations are beyond the scope of this documentation. You can find more details about the distributed configuration storage (DCS) and the Patroni REST API in the Patroni documentation.
You can use HTTPS with HAProxy, which is not covered in this documentation.