For organizations who want to simplify PostgreSQL clustering, Patroni is a popular option. In this blog, we look at the PostgreSQL Patroni combination, how it works, how to configure it, and commands and tips to help you along the way.
Patroni is a Python-based open-source PostgreSQL template and controller, in which you can run high-availability Postgres clusters.
Patroni is technically a Python daemon, in which single Python daemons manage single PostgreSQL instances. It operates on the same system as PostgreSQL and was created out of a need for an automatic failure option for Postgres, while it leverages the permissive MIT license. It uses a distributed configuration store to track which Postgres instance is the master. Likewise, it can be configured to work with Zookeeper, etcd, or Consul configuration stores.
Using Patroni with Postgres implementations can simplify the overall cluster management lifecycle significantly.
Approximately every 10 seconds, Patroni checks every Postgres node in the cluster for the presence of an existing primary in the Distributed Configuration Store (DCS). If no primary is found, it inserts a key in the DCS to claim the primary location. If this server happens to be the primary, it informs HAProxy to use this as the new master. On the other hand, if a primary is found, it performs a variety of checks and attempts to transform the current server into a replica version. It’s truly that simple.
In the example above, we have 3 Postgres instances (node A, B, C) running on the same box. On the right is the Distributed Configuration Store (DCS) running on etcd. In this example there’s one server, with the DCS clustered together. Keep in mind, etcd (the DCS) is simply storing information about who is the primary, the members in the cluster, the cluster name, and the status of members. Above, Postgres is not talking directly to etcd. Again, Patroni runs on a bot within the same box and acts as the mediator to Postgres.
Within the 10 second span, the bots on Node B and Node C will begin asking where the leader has gone.
The leader key will expire and signal Node B and Node C that they need to activate as the leader. This will create a leader race.
Node A will then disappear. Node B will query node C to figure out where its’ write-ahead log position is. This will inform the node how caught up each remaining node is and was when the failure took place. If one node is more caught up than another, that will become the new leader. If not, the node that makes the first call to etcd becomes the new leader.
Above, node C becomes the primary.
Node B will then start replicating from Node C. And then when HAPROXY hits each nodes’ REST API ports, it will figure out that node A is no longer available, and which of the remaining nodes is the leader. /
Patroni is relatively straightforward to install. Here’s a quick step-by-step guide:
Configuration is also relatively straightforward with Patroni. After you have installed Patroni, the bin_dir (bin directory) of the potgres0.yml, postgres1.yml, and postgres2.yml will need to be adjusted to the Postgres server installation in the following manner:
While the general installation and configuration process is relatively easy, there are a few nuances you will need to be aware of in order to keep Patroni functioning properly. There are three configuration types including dynamic, local, and environment configuration. We walk through each configuration in detail below.
Dynamic configuration (etcd) is the global configuration of the cluster. When you change a value in etcd, you are effectively altering the configuration of every PostgreSQL node in the cluster in a global fashion. Changes are applied asynchronously during the next available wake cycle. Here are some of the common configuration settings in etcd that can be changed based on cluster-wide needs:
Your local configuration (patroni.yml) will override anything set in a cluster-wide dynamic configuration (etcd). You can apply local changes using patronictl reload or by sending a POST / reload REST API request.
If you are running Postgres in containers, environment configurations can be useful in allowing you to override local configurations. If you are not running Postgres in a containerized environment, you likely will not need to worry about the environment configurations.
There are a variety of Patroni commands you may leverage on a regular basis. Here are some of the most important:
There are two PostgreSQL streaming replication modes, including asynchronous and synchronous. By default Patroni configures PostgreSQL for asynchronous replication. The replication mode you select will depend heavily on your business needs and technology implementation.
This is Patroni’s default replication mode. The cluster is allowed to lose some transactions to remain highly available. Any non-replicated transactions will be ported to a standby to remain in a “forked timeline” on the primary and will not be recovered. maximum_lag_on_failover controls the amount of transactions can be lost by a standby.
Synchronous replication confirms that writes have been committed to a secondary before returning to the client with a success confirmation. In general, you will get less throughput on writes with synchronous replication. This can cause issues with Postgres instances running in separate data centers due to network latency. If follower nodes are not accessible by the master, then the master defaults to read-only. It is still possible to lose transactions in this mode. To prevent transaction loss, you can enable synchronous_mode, or synchronous_mode_strict.
The Patroni REST API allows you to perform all of the tasks that you would perform within Patroni via an external API. The REST API can be use by HAProxy, or any other kind of load balancer, such as F5 for performing HTTP health checks. This API could also be used as a monitoring proxy, such as Prometheus. This is worth checking out, because you simply get a huge wealth of data with the Patroni REST API. Finally, note that all data within the Patroni REST API is stored as JSON data.
As a cluster management tool for automating deployment and management of PostgreSQL high-availability clusters, Patroni can really streamline your Postgres footprint. While getting up and running may take a bit of time, the long-term benefits are well worth it.
No matter your technical challenge or needs, OpenLogic can provide a solution. With support for over 400 open source packages, we can help you with all things PostgreSQL. Still having trouble understanding how to get Patroni up and running? No problem. Reach out to OpenLogic for support and we can have you up in running in no time.
Talk to an Expert
Enterprise Architect, OpenLogic by Perforce
Bill has over 25 years of experience working in various software roles related to full stack development including user interface, middleware, databases (RDBMS and NoSQL), security, DevOps, training, and mentorship. His primary focus is applying open source in the enterprise.