If you’ve ever thought about trust and hope, you’re not likely to have experienced it anything as much as you have a database management system. Well, really, this is what a database is! The title conveys the whole point — a place where data is kept, the main task is to STORE. And the saddest thing, as always, once these beliefs crash into the ruins of a downed production database. And what do I do — you ask? Do not deploy anything on the servers — we answer. Nothing unless it can recover itself, at least temporarily but reliably and quickly. In this article, I will try to talk about my experience of configuring a built to last a lifetime Postgresql cluster within another failover solution from Google — Kubernetes (aka k8s). Task Almost any application needs to have a data store. This is a necessity. Making this data store resistant to faults in a network or on physical servers is common courtesy of a good system architect. Another aspect is high availability of a service, even with a number of heavy competing service requests, which means easy scalability upon necessity. So here are our problems to solve: distributed service Physically Load balancing Unlimited by adding new nodes scaling from failures, deleting, and loss of connection between the nodes Automatic recovery Design with no single point of failure Additional points dictated by the religious beliefs of the author: (the most academic and consistent solution for RDBMS among the free available tools) Postgres wrapper Docker infrastructure description Kubernetes In the diagram, it will look like this: master (primary node1) --\ |- slave1 (node2) ---\ / balancer \ | |- slave2 (node3) ----|---| |----client |- slave3 (node4) ---/ \ balancer / |- slave4 (node5) --/ Assume the input data: Increased number of read requests (in relation to a database record) Linear load growth up to 2x of the average Googled solution Having some experience in solving IT problems, I decided to ask the collective mind of Google, “postgres cluster kubernetes” — what I got was a bunch of information junk, then I tried “postgres cluster docker” — not much better, and, finally, “postgres cluster”, which showed up a few options I have chosen to work with. What got me upset is the lack of decent Docker images along with descriptions of any options for clustering, not to mention Kubernetes. By the way, there weren’t many options for Mysql either, but still one of them is worth mentioning. Like this example in the official k8s repository for Galera(Mysql cluster) Google has made it clear that I have to look for a solution myself and more than that, do it manually (sigh, at least I have a bunch of scattered articles to browse through). Bad and unacceptable solutions Let me make it clear, all the points in this paragraph are based on my subjective opinion and therefore might seem quite viable to you. However, based on my experience, I had to cut them off. Pgpool. Why Pgpool is not always good? Whenever somebody proposes a universal solution (for anything), I always think that such things are cumbersome and hard to maintain. Well, same with Pgpool, which hasn’t become an exception because it can do almost everything: Load balancing to optimize database connectivity and access speed. Keeping a bunch of connections Support for (stream, slony) various replication options for recording, which is important in reorganization of roles in a cluster Auto-determination of Primary server Support for failover/failback Proprietary master-master replication Coordinated work of multiple Pgpool nodes to eliminate single point of failure. I only found the first four points useful. Speaking of the remaining three, these are the problems they present: Recovery with the help of Pgpool2 offers no decision-making system for choosing the next master — the entire logic has to be described in the failover/failback commands. With the master-master replication, write time doubles regardless of the number of nodes. Well, at least it doesn’t grow linearly. How to build a cascading cluster (when a slave is reading from the previous slave) is a total riddle. Of course it’s cool that Pgpool connects the neighboring nodes and can quickly become the master node in case a neighboring one has failed, but for me this problem is perfectly solved by Kubernetes, which ensures the same behavior for literally any service installed on it. Slony. And the elephants go away… Actually, having read and compared with , which is already well-known and runs out-of-the-box, the decision to not even think about the elephants came easily. this (www.slony.info) Streaming Replication On top of that, on the very first page of the website the guys state that “PostgreSQL 9.0 includes streaming replication, which, for a number of use cases, is likely to be simpler and more convenient than Slony-I”, going on to say, “There are, however, three characteristic kinds of cases where you’ll need something like Slony-I”, which are the following: partial replication interaction with other systems (like Londiste and Bucardo) additional behavior when replicating To put it simply, I don’t think Slony is good enough, unless you need to take care of those three specific tasks. Master-master replication. Not all replications are good enough for ideal bidirectional replication, it appeared to me that the efforts it takes to implement such replication are simply incompatible with crucial functions of some applications. Not to mention the speed, there is a limitation in handling transactions and complex queries (SELECT FOR UPDATE, etc.). After sorting out the options Perhaps I’m not as versed in this matter, but the drawbacks of the solution that I’ve seen happened to be enough to leave the idea. And yet, after putting my thinking-caps on I figured out that a system with enhanced write operation will need a completely different technology rather than relational databases. Consolidating and assembling the solution In the examples below you will see what a solution should ideally look like, while the code will show my own implementation. To create a cluster, you don’t necessarily have to have Kubernetes (there’s a docker-compose example) or Docker at all. Just then all this post will be useful not as a CPM solution (copy-paste-modify) but as an installation manual with snippets. Primary and standby instead of master and slave So why did colleagues from Postgresql refuse to use terms “master” and “slave”? Hmm, I could be wrong but there was a rumor that it’s because of non-Political Correctness, we all know that . Well, right. slavery is the worst thing that ever happened The first thing you need to do is start the primary server, followed by the first standby layer, then the second standby layer — all according to the task at hand. Hence we get a simple procedure for the start of a typical Postgresql server in the primary/standby mode with the configuration to enable Streaming Replication What you should pay attention to in the configuration file wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 5001 hot_standby = on All parameters have short description in comments, but all you need to know is that with this configuration we enable access to read for clients and allow execute queries during recovery. You can find interesting article about it on . WAL logs Postgresql Wiki As soon as the primary server of the cluster starts up, we move on to start the standby one, which “knows” where its primary is located. My goal here has boiled down to the assembly of a universal , which would work differently depending on the mode, namely the following way: Docker Image : For primary Configures Repmgr (more about it later) Creates a database and a user for the application Creates a database and a user for monitoring and replication support Updates the config (postgresql.conf) and provides access to external users(pg_hba.conf) Runs the Postgresql service in the background Registers as master in Repmgr Starts repmgrd — a Repmgr demon for monitoring the replication (about it later, too) : For standby Clones the primary server via Repmgr (by the way, with all configs as it simply copies the $PGDATAdirectory) Configures Repmgr Runs the Postgresql service in the background. After cloning, the standby server identifies itself as a standby and starts to obediently follow the primary server. Registers as a slave in Repmgr Starts repmgrd Sequence is of utmost importance when performing all these operations, thats why project helps a lot here. dockerize The difference between the first and the second standby layers is that ANY server from the first layer can act as master for a second layer server. Do not forget that the second layer should start after the first with a time delay. Split-brain and selection of a new leader in the cluster — a situation in which different segments of a cluster can create/elect a new master and think that the problem is solved. Split brain This is one but not the only problem that helped me to solve. Repmgr Essentially, this is a manager that can: (in Repmgr terms) and automatically configure a newborn slave. Clone master Help the cluster if master dies. recover and reconfigure all slave services to follow the new leader, automatically or manually. Elect a new master Remove nodes from a cluster Monitor the health of a cluster on Execute commands events in a cluster What comes to the rescue in our case is repmgrd, which gets started by the primary process to monitor the integrity of the cluster. In situations when you have lost access to master, Repmgr attempts to parse the current structure of the cluster and determine which will be the next master. Certainly, Repmgr is intelligent enough to elect the only correct master and avoid creating a Split Brain situation. Pgpool-II — swiming pool of connections The last component of the system is Pgpool. As I stated in the section about bad decisions, the service still does its job: between all nodes in a cluster Load-balances to optimize database access speed Stores connect descriptors In this Streaming Replication case, PgPool-II automatically locates master and uses it for write requests. As a conclusion, I have created quite a simple , which at startup configures itself to work with a set of nodes and users who will be able to be authorized through MD5 authorization via Pgpool ( ) Docker Image this bit happened to be a bit more complicated than I thought Far too often you need to get rid of a single point of failure to suffice, and in our situation this point is a pgpool service that is proxying all requests and thus can become the weakest chain in the path to data access. Fortunately, k8s comes handy allowing us to create as many replications of the service as needed. Sadly that this is missing the part about creating replications, but if you are aware of how and/or works, it’ll be no trouble to pull the configuration off. Kubernetes example Replication Controller Deployment Result This article is not a retelling of the scripts to solve the problem, rather, it is a description of the structure of the solution. The former means that to better understand and optimize the solution, you will have to read the code, at least , which is step-by-step and meticulously explains how to start up the cluster for docker-compose and Kubernetes. In addition, for those who will appreciate what I wrote and decide to move beyond the proposed solution, I am willing to lend a . README.md on github virtual helping hand Sources and documentation on Github Docker Image for cluster-ready Postgresql from the example Docker Image for Pgpool with configuration through ENV variables Documentation and used material Streaming replication in postgres Repmgr Pgpool2 Kubernetes PS: I hope you have found the article helpful. Have a positive summer everyone! Good luck and good mood, colleagues ;) PPS: was written in Russian, so thanks to (Technical Writer, )and (Technical Project Manager, Infrastructure Department, ) for help to translate it! The original article Sergey Rodin Lazada Tom Wickings Lazada
