Introduction

Some time ago, I was tasked with developing a fault-tolerant cluster for PostgreSQL, 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, 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.

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 (a character from a video game whose vital organs are all replicated), while the nodes, clusters, and the project itself are called tuchanka (the planet where krogan live).

Currently, management has allowed to open the project to the open-source community under the MIT license. 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.

The clusters are deployed on virtual machines VirtualBox. 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 c quorum device (hosted on a cheap virtual machine in a third data center), which resolves uncertainty 50%/50%, casting its vote for one side. The third cluster spans three data centers: one master, two slaves, without quorum deviceThe fourth cluster consists of four PostgreSQL servers, two for each data center: one master and the other replicas, and it also uses witness c quorum deviceThe fourth can withstand the failure of two servers or one data center. This solution can be scaled up to accommodate more replicas if necessary.

Time Synchronization Service ntpd is also reconfigured for fault tolerance, but it uses the method of ntpd (orphan mode). The main server witness acts as the central NTP server, distributing its time to all clusters, thereby synchronizing all servers with each other. If witness 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 is also set up on witness, 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 only for the sake of saving on the number of virtual machines and space.

Versions

v0. It works with CentOS 7 and PostgreSQL 11 on VirtualBox 6.1.

Cluster Structure

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 use to protect against split-brain the standard technology of Pacemaker, which is called STONITH (Shoot The Other Node In The Head) or fencing. 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-devices (IPMI, UPS, etc.) will also not work.

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. 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-brainIf the software that controls such behavior is not functioning, a watchdog, for example based on IPMI, should take action.

If the number of nodes is even (a cluster in two data centers), a so-called uncertainty may arise. 50%/50% (fifty-fifty), when network isolation splits the cluster exactly in half. Therefore, for an even number of nodes, a quorum device — 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 (terminology from repmgr, which I liked).

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 (float IP). 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).

Tuchanka1 (squeeze scheme)

Structure

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).

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.

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.

Witness failure

Witness failure (quorum device) 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.

Tuchanka1 failure

Failure of one of the data centers for Tuchanka1. In this case, witness 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.

Tuchanka2 (classical)

Structure

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 — for the master, krogan2s1 — for the slave. There will be fault tolerance for both the master and the slave.

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.

Tuchanka2 failure

In the event of a failure of one of the data centers witness votes for the second. A master will be set up at the only functioning data center, and both float IPs—master and slave—will 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.

Tuchanka4 (many slaves)

Structure

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.

Another feature of this scheme is that it’s 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, for example, on the client side. Different types of clients may require different types sql proxy, 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 can be implemented independently, along with client fault tolerance).

Failure of Tuchanka4

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).

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 redirected all requests to the only remaining float IP; if not, sql proxy 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 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. 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.

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).

Tuchanka3 (3 data centers)

Structure

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 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 (with separate fault tolerance), or assign one slave float IP to half of the clients and the other half to the second.

Failure of Tuchanka3

In the event of a failure of one of the data centers, two remain. One is running the master and the master’s 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).

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.

Automated Testing System

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. The script can accept as parameters the numbers of the clusters that you want to test. For example, this command:

test/failure 2 3

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.

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:

Here, statistics for the tests are displayed. Columns:
- failure — the name of the test (function in the script) that simulates the failure.
- reaction — the average time in seconds that the cluster took to restore its functionality. The measurement is taken from the start of the script that simulates the failure until the cluster restores its operational capability and can continue providing services. If the time is very short, for example, six seconds (this happens in clusters with multiple slaves such as Tuchanka3 and Tuchanka4), it means that the failure occurred on an asynchronous slave and did not affect the operational capacity; there were no changes in the state of the cluster.
- deviation — indicates the range (accuracy) of the value reaction by the method of 'standard deviation.'
- count — how many times this test was executed.
The brief log allows you to assess what the cluster is currently doing. The iteration number (test), timestamp, and operation name are displayed. An execution that takes too long (> 5 minutes) indicates some issue.
heart (heart) — current time. For visual assessment of performance master its table continuously logs the current time using the master's float IP. If successful, the result is displayed in this panel.
beat (pulse) — the 'current time' that was previously recorded by the script heart in the master, is now read from the slave through its float IP. This allows for a visual assessment of the performance of the slave and replication. There are no slaves with float IP in Tuchanka1 (no slaves providing services), but there are two instances (DB), so it will show not beat, and heart the second instance.
Cluster state monitoring using the utility pcs mon. It shows the structure, resource distribution across nodes, and other useful information.
Here is the system monitoring output from each virtual machine in the cluster. There can be more such panels — as many as the virtual machines in the cluster. Two graphs CPU Load (with two processors in each VM), the name of the virtual machine, System Load (named Load Average, because it is averaged over 5, 10, and 15 minutes), process data, and memory distribution.
Script tracing for testing. In case of failure — sudden interruption of work or infinite waiting cycle — the reason for such behavior can be seen here.

Testing is conducted in two stages. First, the script goes through all variants of tests, randomly selecting a virtual machine to which this test is applied. Then an infinite testing loop is executed, with virtual machines and failures chosen randomly each time. A sudden termination of the testing script (bottom panel) or an infinite wait for something (> 5 minutes of operation time is visible in the trace) indicates that one of the tests on this cluster has failed.

Each test consists of the following operations:

Launching a function emulating a failure.
Ready? — waiting for restoration of cluster functionality (when all services are restored).
The time waiting for the restoration of the cluster is shown (reaction).
Fix — the cluster is being 'fixed'. After this, it should return to fully functional condition and readiness for the next failure.

Here is a list of tests with descriptions of what they do:

ForkBomb: creates 'Out of memory' using a fork bomb.
OutOfSpace: fills the hard drive. However, the test is rather symbolic; with the minimal load generated during testing, PostgreSQL typically does not fail when the hard drive is full.
Postgres-KILL: kills PostgreSQL with the command killall -KILL postgres.
Postgres-STOP: suspends PostgreSQL with the command killall -STOP postgres.
PowerOff: "powers off" the virtual machine with the command VBoxManage controlvm "virtual_machine" poweroff.
Reset: resets the virtual machine with the command VBoxManage controlvm "virtual_machine" reset.
SBD-STOP: suspends the SBD daemon with the command killall -STOP sbd.
ShutDown: sends a command via SSH to the virtual machine systemctl poweroff, and the system shuts down correctly.
UnLink: network isolation, command VBoxManage controlvm "virtual_machine" setlinkstate1 off.

: finishing the testing either with the standard command tmux "kill-window" Ctrl-b &, or with the command "detach-client" Ctrl-b d: at this point, the testing ends, tmux closes, and the virtual machines are powered off.

Identified issues during testing

Currently, you have to choose either sound or suspend/resume. We are waiting for the author of the bce module to finalize the functionality. the watchdog daemon sbd manages the stopping of monitored daemons but not their hanging. As a result, faults leading to hanging are not handled correctly, Corosync and Pacemaker, but do not suspend sbd. For verification Corosync already have PR#83 (on GitHub under sbd), accepted into the branch master. They promised (in PR#83) that something similar would be done for Pacemaker, I hope it will be ready by RedHat 8 . However, such "malfunctions" are theoretical and can be easily simulated artificially, for example, killall -STOP corosync, but are never encountered in real life.
The Pacemaker in the version for CentOS 7 the sync_timeout is set incorrectly, resulting in at quorum devicewhen one node fails, the second node reboots with some probability, where the master was supposed to move. This was fixed by increasingduring deployment (in the script is set incorrectly, resulting in at quorum device setup/setup1 ). This fix was not accepted by the developers,instead, they promised to redesign the infrastructure in such a way (at some uncertain future point) that this timeout would be calculated automatically. PacemakerIf during the database configuration it is specified that in
LC_MESSAGES (text messages) Unicode can be used, for example, ru_RU.UTF-8 , then when launchedin an environment where the locale is not UTF-8, say, in a blank environment (here postgres pacemaker pgsqlms+(paf) startsinstead of UTF-8 letters, question marks will appear in the log. postgres), then The PostgreSQL developers did not come to a consensus on what to do in this case. It is worked around by settingLC_MESSAGES=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 when configuring (creating) a DB instance.
If wal_receiver_timeout is set (default is 60s), then when testing PostgreSQL-STOP on the master in the tuchanka3 and tuchanka4 clusters there is no reconnection of replication to the new master.. The replication is synchronous there, so not only the worker stops, but also the new master. This can be bypassed by setting wal_receiver_timeout=0 when configuring PostgreSQL.
Occasionally, I observed replication freezing in PostgreSQL during the ForkBomb test (memory overflow). After ForkBomb, sometimes the workers may not reconnect to the new master.. I have only encountered this in the tuchanka3 and tuchanka4 clusters, where due to synchronous replication, the master hung. The issue resolved itself after a while (about two hours). Further investigation is needed to fix this. The symptoms resemble a previous bug caused by a different reason but with similar consequences.