{"id":92570,"date":"2020-08-28T19:42:21","date_gmt":"2020-08-28T17:42:21","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/modelirovanie-otkazoustojchivyh-klasterov-na-baze-postgresql-i-pacemaker"},"modified":"2020-08-28T19:42:21","modified_gmt":"2020-08-28T17:42:21","slug":"modelirovanie-otkazoustojchivyh-klasterov-na-baze-postgresql-i-pacemaker","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/modelirovanie-otkazoustojchivyh-klasterov-na-baze-postgresql-i-pacemaker","title":{"rendered":"Modeling fault-tolerant clusters based on PostgreSQL and Pacemaker","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Introduction<\/h1>\n

<\/p>\n

Some time ago, I was tasked with developing a fault-tolerant cluster for PostgreSQL<\/a><\/noindex>, operating across multiple data centers connected by fiber optics within a single city, and able to withstand the failure (for example, power outage) of a single data center. For the software responsible for fault tolerance, I chose Pacemaker<\/a><\/noindex>, because it is the official solution from RedHat for creating fault-tolerant clusters. It is advantageous because RedHat provides support for it, and it is a universal (modular) solution. With it, resilience can be ensured not only for PostgreSQL but also for other services, either using standard modules or creating them tailored to specific needs.<\/p>\n

<\/p>\n

A reasonable question arose regarding this solution: how resilient will the fault-tolerant cluster be? To investigate this, I developed a test setup that simulates various failures on the cluster nodes, waits for recovery, restores the failed node, and continues testing in a loop. Initially, this project was called hapgsql, but over time, I got bored with a name that had only one vowel. Therefore, I started naming the fault-tolerant databases (and the floating IPs pointing to them) krogan<\/strong> (a character from a video game whose vital organs are all replicated), while the nodes, clusters, and the project itself are called tuchanka<\/strong> (the planet where krogan live).<\/p>\n

<\/p>\n

Currently, management has allowed to open the project to the open-source community under the MIT license<\/a><\/noindex>. A README will soon be translated into English (since it is expected that the main users will be developers of Pacemaker and PostgreSQL), and I decided to present the old Russian version of the README (partially) in the form of this article.<\/p>\n

<\/p>\n

\"Modeling<\/p>\n

<\/a><\/noindex><\/p>\n

The clusters are deployed on virtual machines VirtualBox<\/a><\/noindex>. A total of 12 virtual machines will be deployed (a total of 36GiB), which will form 4 fault-tolerant clusters (different configurations). The first two clusters consist of two PostgreSQL servers located in different data centers, and a common server witness<\/em> c quorum device<\/strong> (hosted on a cheap virtual machine in a third data center), which resolves uncertainty 50%\/50%<\/strong>, casting its vote for one side. The third cluster spans three data centers: one master, two slaves, without quorum device<\/strong>The fourth cluster consists of four PostgreSQL servers, two for each data center: one master and the other replicas, and it also uses witness<\/em> c quorum device<\/strong>The fourth can withstand the failure of two servers or one data center. This solution can be scaled up to accommodate more replicas if necessary.<\/p>\n

<\/p>\n

Time Synchronization Service ntpd<\/a><\/noindex> is also reconfigured for fault tolerance, but it uses the method of ntpd<\/code> (orphan mode<\/em>). The main server witness<\/em> acts as the central NTP server, distributing its time to all clusters, thereby synchronizing all servers with each other. If witness<\/em> fails or becomes isolated, then one of the servers in the cluster (within the cluster) will start to distribute its time. A supporting caching HTTP proxy<\/strong> is also set up on witness<\/em>, allowing the other virtual machines to access Yum repositories. In reality, services such as time synchronization and proxy will likely be hosted on dedicated servers, while in this setup they are placed on witness<\/em> only for the sake of saving on the number of virtual machines and space.<\/p>\n

<\/p>\n

Versions<\/h1>\n

<\/p>\n

v0. It works with CentOS 7 and PostgreSQL 11 on VirtualBox 6.1.<\/p>\n

<\/p>\n

Cluster Structure<\/h1>\n

<\/p>\n

All clusters are designed to be hosted across multiple data centers, unified into one flat network, and must withstand the failure or network isolation of one data center. Therefore, is not possible<\/strong> use to protect against split-brain<\/strong> the standard technology of Pacemaker, which is called STONITH<\/em> (Shoot The Other Node In The Head) or fencing<\/em>. Its essence: if nodes in the cluster begin to suspect that something is wrong with a certain node, which is unresponsive or behaving incorrectly, they forcibly disable it via \"external\" devices, such as an IPMI management card or UPS. However, this will only work in cases where, during a single server failure, the IPMI or UPS continues to operate. Here, protection against a much more catastrophic failure is planned, where an entire data center fails (for example, loses power). In such a failure, all stonith<\/em>-devices (IPMI, UPS, etc.) will also not work.<\/p>\n

<\/p>\n

Instead, the system is based on the idea of quorum. All nodes have a vote, and only those that can see more than half of all nodes can operate. This number of \"half+1\" is called quorum<\/strong>. If a quorum is not reached, the node decides that it is in network isolation and must disable its resources, i.e. it is a kind of protection against split-brain<\/strong>If the software that controls such behavior is not functioning, a watchdog, for example based on IPMI, should take action.<\/p>\n

<\/p>\n

If the number of nodes is even (a cluster in two data centers), a so-called uncertainty may arise. 50%\/50%<\/strong> (fifty-fifty<\/em>), when network isolation splits the cluster exactly in half. Therefore, for an even number of nodes, a quorum device<\/strong> \u2014 lightweight daemon that can run on the cheapest virtual machine in the third data center. It casts its vote for one of the segments (that it can see), thereby resolving the 50%\/50% uncertainty. The server on which the quorum device will be running, I named witness<\/em> (terminology from repmgr, which I liked).<\/p>\n

<\/p>\n

Resources can move from one place to another, for example, from defective servers to functioning ones, or by the command of system administrators. To inform clients where their needed resources are located (where to connect?), floating IP addresses<\/em> (float IP<\/strong>). These are IPs that Pacemaker can move across nodes (everything is in a flat network). Each of them symbolizes a resource (service) and will be where clients need to connect to access this service (in our case, the database).<\/p>\n

<\/p>\n

Tuchanka1 (squeeze scheme)<\/h2>\n

<\/p>\n

Structure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

The idea was that we have many small databases with low load, for which it is not cost-effective to maintain a dedicated slave server in hot standby mode for read-only transactions (there's no need for such resource waste).<\/p>\n

<\/p>\n

In each data center, there is one server. Each server hosts two instances of PostgreSQL (in PostgreSQL terminology, they are called clusters, but to avoid confusion, I will refer to them as instances (similarly to other databases), and will only call the groups Pacemaker clusters). One instance operates in master mode, and only it provides services (only it has the float IP). The second instance functions as a slave for the second data center and will only provide services if its master fails. Since most of the time only one instance (the master) will provide services (execute queries), all server resources are optimized for the master (memory is allocated for shared_buffers cache, etc.), while still ensuring that there are enough resources for the second instance (even for less optimal operation via the file system cache) in case one of the data centers fails. The slave does not provide services (does not perform read-only queries) during normal cluster operation to avoid resource contention with the master on the same machine.<\/p>\n

<\/p>\n

In the case of two nodes, fault tolerance is only possible with asynchronous replication, as synchronous failure of the slave will result in the master stopping.<\/p>\n

<\/p>\n

Witness failure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

Witness failure (quorum device<\/em>) I will only consider this for the Tuchanka1 cluster; the same situation applies to all others. In the event of a witness failure in the cluster structure, there will be no changes, everything will continue to operate as it did before. However, the quorum will become 2 out of 3, meaning any subsequent failure will be fatal for the cluster. It will still need to be urgently fixed.<\/p>\n

<\/p>\n

Tuchanka1 failure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

Failure of one of the data centers for Tuchanka1. In this case, witness<\/em> it gives its vote to the second node in the second data center. There, the former slave becomes the master, resulting in both masters operating on one server, and both of their float IPs point to them.<\/p>\n

<\/p>\n

Tuchanka2 (classical)<\/h2>\n

<\/p>\n

Structure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

The classical configuration consists of two nodes. One operates as the master, the other as the slave. Both can execute queries (the slave is only read-only), so both are assigned float IPs: krogan2 \u2014 for the master, krogan2s1 \u2014 for the slave. There will be fault tolerance for both the master and the slave.<\/p>\n

<\/p>\n

In the case of two nodes, fault tolerance is only possible with asynchronous replication because the synchronous failure of the slave will lead to the stoppage of the master.<\/p>\n

<\/p>\n

Tuchanka2 failure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

In the event of a failure of one of the data centers witness<\/em> votes for the second. A master will be set up at the only functioning data center, and both float IPs\u2014master and slave\u2014will point to it. Naturally, the instance must be configured in such a way that it has sufficient resources (connection limits, etc.) to simultaneously handle all connections and requests from both the master and slave float IPs. Thus, under normal operation, it should have a substantial buffer in the limits.<\/p>\n

<\/p>\n

Tuchanka4 (many slaves)<\/h2>\n

<\/p>\n

Structure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

It's a different extreme. There are databases with a high volume of read-only requests (a typical case of a high-load website). Tuchanka4 is a situation where there can be three or more slaves processing such requests, but still not too many. With a very large number of slaves, a hierarchical replication system will need to be devised. In the minimal case (as shown in the image), each of the two data centers has two servers, each with an instance of PostgreSQL.<\/p>\n

<\/p>\n

Another feature of this scheme is that it\u2019s possible to organize synchronous replication here. It is configured to replicate to another data center whenever possible, rather than to a replica in the same data center as the master. A float IP points to both the master and each slave. Ideally, requests should be load-balanced among the slaves using some method sql proxy<\/em>, for example, on the client side. Different types of clients may require different types sql proxy<\/em>, and only client developers know what each one needs. This functionality can be implemented as an external daemon, a client library (connection pool), etc. All of this goes beyond the scope of a fault-tolerant database cluster (fault tolerance SQL proxy<\/em> can be implemented independently, along with client fault tolerance).<\/p>\n

<\/p>\n

Failure of Tuchanka4<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

In the event of a failure of one data center (i.e., two servers), the witness votes for the second. As a result, two servers operate in the second data center: one is the master with the master float IP (for handling read-write requests); and the second server has a slave with synchronous replication, which is pointed to by one of the slave float IPs (for read-only requests).<\/p>\n

<\/p>\n

The first thing to note is that not all slave float IPs will be operational, but only one. For correct operation with it, it will be necessary for sql proxy<\/em> redirected all requests to the only remaining float IP; if not, sql proxy<\/em> then you can list all float IP slaves in the URL for connection, separated by commas. In that case, the connection will be to the first working IP, as implemented in the automatic testing system. However, this may not work in other libraries, such as JDBC, and it is necessary libpq<\/em> for the connection to the first working IP, as designed in the automatic testing system. It may not function the same way in other libraries, such as JDBC, and additional sql proxy<\/em>. This is implemented because the float IP for the slaves is restricted from running on the same server simultaneously, ensuring they are evenly distributed across the slave servers when multiple are in operation.<\/p>\n

<\/p>\n

Second: even in the event of a data center failure, synchronous replication will be maintained. Even if a secondary failure occurs, meaning one of the two servers in the remaining data center fails, the cluster, although it will stop providing services, will still retain information about all committed transactions for which it has acknowledged a commit (there will be no data loss during the secondary failure).<\/p>\n

<\/p>\n

Tuchanka3 (3 data centers)<\/h2>\n

<\/p>\n

Structure<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

This cluster is for situations where there are three fully operational data centers, each with a fully functioning database server. In this case, quorum device<\/em> is not required. One data center operates the master, while the other two operate the slaves. Replication is synchronous, of the ANY type (slave1, slave2), meaning the client will receive a commit acknowledgment as soon as any of the slaves responds that it has received the commit. The resources are pointed out by one float IP for the master and two for the slaves. Unlike Tuchanka4, all three float IPs are fault-tolerant. For load balancing read-only SQL queries, one can use sql proxy<\/em> (with separate fault tolerance), or assign one slave float IP to half of the clients and the other half to the second.<\/p>\n

<\/p>\n

Failure of Tuchanka3<\/h3>\n

<\/p>\n

\"Modeling<\/p>\n

<\/p>\n

In the event of a failure of one of the data centers, two remain. One is running the master and the master\u2019s float IP, while the other is running a slave and both slave float IPs (the instance must have double the resource reserve to accept all connections from both slave float IPs). There is synchronous replication between the master and the slave. Additionally, the cluster will retain information about committed and confirmed transactions (there will be no data loss) in the event of the destruction of two data centers (if they are not destroyed simultaneously).<\/p>\n

<\/p>\n

I decided not to include a detailed description of the file structure and deployment. Those who want to experiment can read all about it in README. I am only providing a description of the automated testing.<\/em><\/p>\n

<\/p>\n

Automated Testing System<\/h1>\n

<\/p>\n

To test the fault tolerance of clusters with simulated various failures, an automated testing system has been created. It is launched via a script test\/failure<\/code>. The script can accept as parameters the numbers of the clusters that you want to test. For example, this command:<\/p>\n

<\/p>\n

test\/failure 2 3<\/code><\/pre>\n
<\/p>\n
will test only the second and third clusters. If no parameters are specified, all clusters will be tested. All clusters are tested in parallel, and the results are displayed in the tmux panel. Tmux uses a dedicated tmux server, so the script can be run from the default tmux, which results in nested tmux sessions. I recommend using a terminal in a large window with a small font. Before testing begins, all virtual machines are rolled back to a snapshot taken at the moment the script finishes. setup<\/code>.<\/p>\n
<\/p>\n
\"Modeling<\/p>\n
<\/p>\n
The terminal is divided into columns based on the number of tested clusters; by default (as shown in the screenshot), there are four. I will describe the contents of the columns using Tuchanka2 as an example. The panels in the screenshot are numbered:<\/p>\n
<\/p>\n
    \n
  1. Here, statistics for the tests are displayed. Columns:\n