https://www.binwang.me/2024-12-02-PostgreSQL-High-Availability-Solutions-Part-1.html Computer Science Algorithm Ren Yi Jin Zhi Shu De Bu Ma (2011) Kuai Pai Sha Shou (2011) How to Estimate Max TPS from TPM (2020) Data Processing Use Docker to Submit Spark Jobs (2015) The Proper Way to Use Spark Checkpoint (2015) Use Redis Instead of Spark Streaming to Count Statistics (2015) Digital Life Move from Twitter to Mastodon (2020) What Is Wrong about Recommendation System (2020) Use RSS and Kindle to Read News (2020) Matrix: A Self Hosted Instant Messaging Solution with End to End Encryption (2020) An Overview of China's Internet Censorship Strategy (2020) Deploy Matrix for Users in China (2020) Random Playlists for Self Hosted Videos (2024) Distributed System Spanner and Open Source Implementations (2018) Great Resources for Learning Database and Distributed System (2019) Aurora Database (2020) Understand Liveness and Fairness in TLA+ (2020) Use TLA+ to Verify Cache Consistency (2020) Keep Data Consistency During Database Migration (2020) Redis Implementation for Cache and Database Consistency (2020) Jepsen Test on Patroni: A PostgreSQL High Availability Solution (2024) Distributed System Infrastructure Infrastructure Setup for High Availability (2023) Upgrade Kubernetes from 1.23 to 1.24 (2023) How to Cleanup Ceph Filesystem for Deleted Kubernetes Persistent Volume (2023) Introduce K3s, CephFS and MetalLB to My High Avaliable Cluster (2023) Machine Learning Backpropagation Algorithm (2015) My Recent Work About Neural Networks (2015) Install BLAS Library for MXNet (2015) How to Put RNN Layers Into Neural Network Model (2016) Build A Computer for Deep Learning (2016) Operating System Android The Permission Management of Android Becomes A Bigger Problem When It Comes to Wearable Devices and TV (2016) Root And Optimize MiBox 3S (2018) Linux Duan Kou 666:Hui Mie ! (2011) Chroot Jian Jie (2012) Compile And Install Kernel (2012) Backup My Dotfiles (2012) Comparison Between Linux Desktop Environments (2012) How Kernel's Makefile Specify Output Directory (2013) Xbmc on Raspberry Pi with Archlinux (2013) Setup SSH Authentication with YubiKey (2021) In Defence of Disabling Swap (2021) Build a Linux Virtual Machine for Windows Apps (2023) Linux Full Disk Encryption with Yubikey (2023) A Review of Linux on Surface Pro 4 (2024) MacOS My MacOS Essentials (2024) Tizen Tizen,Jia You (2012) Windows Build a Unix Like Environment on Windows (2016) iOS DNS Resolving Bug in iOS 14 (2020) Handle Apple In-App-Purchase Server Notification with Scala/Java (2022) Programming Language C++ Ye Tan C++ (2012) Erlang Build Erlang the Rebar Way (2013) Fetch Popular Erlang Modules by Coffee Script (2013) Why I Come Back to Erlang (2014) Experiment On Combining OOP With Erlang's Actor Model (2014) Go Notes On Go Scheduler (2014) Scala Config sbt to Use Both Proxy and Self Hosted Repositories (2016) Compare Task Processing Approaches in Scala (2023) A Boring JVM Memory Profiling Story (2023) Scala 2 Macro Tutorial (2023) SBT Task to Build Frontend Components (2024) Scheme SICPDi San Zhang Zong Jie (Shang )----Ke Bian Liang Yu Huan Jing (2012) SICPDi San Zhang Zong Jie (Xia )----Liu Bian Cheng (2012) Type System RESTful API with Type System (2014) Powerful Type System (2020) Software Engineering Call Program Like A Function (2012) More About Program In Shell And Function (2013) Server Logic of Level Based Games (2013) Languages Should Have Database Built In (2013) How About Translate IMAP And SMTP Into HTTP API? (2015) The Things You Need to Know When Using Apache Sentry (2018) Define Infrastructure as Code (2021) Why Big Companies Need to Adopt Open Source (2021) Storage Change Root File System from Ext4 to Xfs on Archlinux (2013) Migrate Arch Linux to ZFS (2020) Personal ZFS Offsite Backup Solution (2021) ZFS Profiling on Arch Linux (2023) UI Flutter Make Flutter Web Apps More Native Like (2024) Javascript Beautiful Math with MathJex (2012) HTML + CSS + JS is Good (2014) What is Wrong about HTML and CSS (2014) Prevent htmx Lazy Loaded Content From Reloading (2024) Create a Checkbox That Returns Boolean Value for htmx (2024) Virtualization Create A Virtual Machine Network (2012) Fedora Virt-manager Guest Connect to Host (2013) Docker Is the One Scaffolding to Rule Them All (2014) Life Life in Guangzhou (2013) Recent Works (2013) Dong Jing Zhi Lu (2014) My 2017 Year in Review (2018) My 2020 in Review (2021) Shi San Nian Qian Bei Ge Chi De Jing Li (2022) A Travel to Montreal (2022) My 2022 in Review (2023) Travel Back to China (2024) Projects Bard The Thoughts Behind Bard Framework (2014) Why Use Reflections to Write A Web Framework (2014) Blog My New Blog Website (2012) Comment And Search Are Available (2012) Remove Categories (2012) Add Index to My Blog (2021) Jekyll Plugin to Load Asciinema Recordings Locally (2023) Add Index Sidebar to My Blog (2023) RSS Brain RSS Brain: Yet Another RSS Reader, With More Features (2022) How RSS Brain Shows Related Articles (2022) Update on RSS Brain to Find Related Articles with Machine Learning (2023) Source Code of RSS Brain is Available (2024) Scala2grpc A Library to Make It Easier to Use Scala with gRPC (2022) Migrate Scala2grpc to Cats Effect 3 (2023) Comment Everywhere (2013) Fetch Popular Erlang Modules by Coffee Script (2013) Psychology Ye Lu Da Xue Xin Li Xue Dao Lun (2012) Thoughts Chinese Guan Yu Ren De Si Xiang (2008) Guan Yu Ren De Si Xiang (Xu ) (2008) Lan <> You Gan (2011) Wei Lai Ren Men Zen Yang Dui Dai Pi Ren :Du <
  • > Za Xiang (2011) Hao Wan De Sheng Ming You Xi (2011) Nian Tian Di Zhi You You (2012) Bing Qi Xian Dai Ke Ji De Yin Shi Sheng Huo (2015) Du <> You Gan (2016) You "Fei Qing "Zhe Ge Cheng Hu Suo Xiang Dao De (2019) Sheng Tang Shi Ren He Yuan You (2020) English Tired of Programming (2013) The Tragic Talented Programmer (2020) * Home * Snippets * Read * Travel * Search * About * RSS Table of Contents 1. Jepsen Test Setup 2. A Known Issue of PostgreSQL Replication 3. Patroni Setup for Testing 4. Reproduce Read Committed Violation 5. Failed to Recover the Cluster When Only 1 Out of 3 Nodes is Lost 6. Ways to Improve? 7. Minor Issue: Wrong Role Label for Kubernetes Pod 8. What's Next? Jepsen Test on Patroni: A PostgreSQL High Availability Solution Posted on 02 Dec 2024, tagged databasejepsentestdistributed systemconsistencyHA Note: code used in this article can be found on the Github repo jepsen-postgres-ha. I've used Cockroach DB for a few my side projects. I enjoyed it overall. But since it announced license change and require mandatory telemetry collection for free version, I started to look for alternatives. The most nature chose is to just use the plain old PostgreSQL since my data size is not that big and even a less powerful machine can handle it without any problem. One of my important requirements for the database is to have good high availability setup so that I can just shutdown a machine for maintenance from time to time. This series of blog posts will focus on PostgreSQL's HA solutions instead of why do we need that. Not saying why is not important enough but I'll save that discussion for another blog post out of this series. PostgreSQL doesn't come with native high availability solution. Instead, it has features like replication to support you build your own HA solution. But we all know distributed system is hard to build and error-prone. So I'm planning to test different solutions before I trust my data with them: mainly using Jepsen to test the correctness at first and if it passes the test, benchmark it to make sure it's usable in real world. In the first part of this series, I'll introduce the basic Jepsen test setup. Then use my early test result from Patroni, a very popular PostgreSQL HA solution, as an example. In the test with Patroni, I'm able to: * Reproduce a known issue that causes violation of read committed isolation. This is related to a fundamental flaw in PostgreSQL's replication implementation. * Observe the cluster failed to recover with 1 node lose out of 3 nodes in total. Ideally I would like to do more tests and deeper digging into it, but I may not have enough free time in the coming 1 or 2 months so I'd like to record some result here and maybe have some updates later. Jepsen Test Setup The tool used in the tests is Jepsen. For the ones who are not familiar with it, it's a tool to test the correctness of distributed systems. I highly recommend anyone interested in distributed systems to read its analyses, which have found bugs in almost every system it has tested, including PostgreSQL 12.3 with single machine setup. On a high level, it runs queries and check if the data is consistent at the end, at the same time it has many built-in failures (nemesis in Jepsen's term) can be introduced during the query, like node crash, network partition, network slowdown and so on. The analyses of PostgreSQL 12.3 already does an excellent job to explain how Jepsen tests PostgreSQL, so I'll not repeat it here. I borrowed the append and read workload from that test but with 2 differences on other parts : 1. The database is setup in a different way. In the original test, it only tests a single machine PostgreSQL but the bugs are already fixed. So we are going to test a HA setup. 2. In the original test, it was able to find bugs without import any failures. But since that bug has been fix, we are going to enable different built-in failures like node crash and network slowness to test if the PostgreSQL cluster can still behave correctly or not. To be more specific, I use Vagrant to create a 3 nodes virtual machine cluster and install Kubernetes (with k3s)on it. The Vagrantfile is here. It's mostly from a previous project I created to test k3s, as described in the blog post Introduce K3s, CephFS and MetalLB to My High Avaliable Cluster. This setup makes future tests for different HA solutions convenient since most of them supports Kubernetes, so that I can just create yaml files for different systems, while only need to implement Jepsen's interface once to define database setup, tear down, kill and recover: * For setup, just use kubectl create -f . * For tear down, just use kubectl delete -f to delete the whole thing. * To kill the db, find the root k3s process and kill -9 it along with all its children process. Make sure to also stop the systemd service so it will not be automatically started again. * To recover the db, simply start the k3s service again so that the pods will be scheduled on the node again. It just makes sure the k3s service is started. More health checks are needed if really want to wait for the db to be really recovered but it's good for now. Related code is at here. The code is meant to support any HA setup as long as it can be defined with a Kubernetes manifest. It supports --cluster flag so that can specify which manifest to test. I created a single node PostgreSQL setup and a Patroni setup for now. But in reality, Patroni has some special things that need to be taken care of, like delete PV and endpoints. I'll clean those things up when my focus is moved to other HA solutions. For the introduced failures, ideally we should test all the supported failures combined randomly. But the state space is large and need a long time to run. So I just created a specific combination to reproduce a known issue. A Known Issue of PostgreSQL Replication When I searched for PostgreSQL HA solutions and whether any of them is tested by Jepsen, I found some comments on Hackernews that says Patroni doesn't guarantee consistency under some scenarios, which lead me to the Twitter discussion, which stated there is a fundamental flaw in PostgreSQL's replication that makes it really hard to implement HA without data lose. Here is the problem: usually with synced replication, a transaction should only be committed and visible after the replica db has persisted the transaction. So that if the primary db failed over to replica, there will be no data lose. But in a special scenario, where the query is cancelled after client sent commit command, PostgreSQL will consider it as committed even the transaction is not replicated yet. So when a failover happens at this time, this "committed data" will be lost from clients' point of view. Here is an example: Time Node 1 Node 2 C1 C2 Role: Role: Primary Replica 1 Visible data: k -> [1] Visible data: k -> [ 1 ] 2 Role: Primary T1 start Visible data: k -> [1] 3 Role: Primary T1 append 2 to Visible data: k -> [1] k 4 Role: Primary T1 commit Visible data: k -> [1] 5 T1 replication started T1 aborted(conn 6 close? client kill?) T1 replication not Role: finished, but T1 is Replica T2 read k, 7 visible to other clients Visible result = data: k -> [1,2] Role: Primary [ 1] Visible data: k -> [1,2] Role: Primary 8 Node crash Visible data: k -> [1] T3 read k, 9 result = [1] 10 T4 append 3 to k Role: Primary 11 Visible data: k -> [1,3] T5 read k, 12 result = [1, 3] In the example above, the value of k is [1] at the beginning. C1 will append monotonically increasing values to k. (It tracks the value locally instead of query k every time). T1 is aborted before it's replicated. But even so, the primary node still treat this transaction as committed. So when C2 queries with T2, it get results with [1, 2]. Then at time 8, the primary is failed over from node 1 to node 2, so when T3 queries k, it returns [1] instead of [1,2]. This is an obvious data lose in our point of view because we know exactly the order of events. But one can argue it miss linearizable guarantee since technically, T3 can be ordered before T2 or even T1, and it will produce a consistent history, thus violates linearizable but not serializable. However, with T5 that has the result of [1,3], it creates a situation that conflict with T2: * If T2 is before T5, T5 should has 2 in the result. * If T5 is before T2, T2 should has 3 in the result. This is not only a violation of serializable, but also read committed because T2 has read the uncommitted data from the client's point of view. Patroni Setup for Testing Even this is a known issue and is documented, I still try to reproduce it in my test for a few reason: first I want to make sure my test is good enough to actually be able to reproduce it. Second, I want to see it happens in real world: the Patroni auto failover makes manually triggering this problem hard because there is only a short time for the commit to be replicated. In my test, I try to setup Patroni to make it prioritize consistency the most. The config is at here for the Docker's entrypoint script and here for the config in Kubernetes. The PostgreSQL version is 16 and Patroni version is v4.0.3. The key configurations are about replication mode. The description of each parameter below is copied from Patroni document about replication modes: * synchronous_mode is set to on: When synchronous_mode is turned on Patroni will not promote a standby unless it is certain that the standby contains all transactions that may have returned a successful commit status to client. Turning on synchronous_mode does not guarantee multi node durability of commits under all circumstances. When no suitable standby is available, primary server will still accept writes, but does not guarantee their replication. * synchronous_mode_strict is set to on: When it is absolutely necessary to guarantee that each write is stored durably on at least two nodes, enable synchronous_mode_strict in addition to the synchronous_mode. This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. * synchronous_node_count is left to default as 1: The parameter synchronous_node_count is used by Patroni to manage the number of synchronous standby databases. It is set to 1 by default. It has no effect when synchronous_mode is set to off. When enabled, Patroni manages the precise number of synchronous standby databases based on parameter synchronous_node_count and adjusts the state in DCS & synchronous_standby_names in PostgreSQL as members join and leave. If the parameter is set to a value higher than the number of eligible nodes it will be automatically reduced by Patroni. In PostgreSQL: synchronous_commit: "on" synchronous_standby_names: "*" max_connections: 500 As stated in the Patroni doc, even with this setup it still has the known issue described above: Note: Because of the way synchronous replication is implemented in PostgreSQL it is still possible to lose transactions even when using synchronous_mode_strict. If the PostgreSQL backend is cancelled while waiting to acknowledge replication (as a result of packet cancellation due to client timeout or backend failure) transaction changes become visible for other backends. Such changes are not yet replicated and may be lost in case of standby promotion. This is the thing I want to reproduce. Reproduce Read Committed Violation The reproduce of this failure is harder than I thought, even I knew exactly the requirement to trigger it at the beginning. There are a few factors contributed to this: First, if client doesn't abort the connection itself, it's hard to reproduce this scenario: Client not aborting the connection means it's aborted by the primary node, which need to introduce some failures to primary node and that most likely makes it to failover immediately before the failure scenario is triggered. Jepsen's built-in failures/nemesis are mostly on the server side. While not familiar with Clojure, it took me some time to figure out how to abort the connection just after sending the commit command. The code is at here: (if (and break-conn (not read-only?)) (let [result-chan (chan) close-chan (chan) ] (go (>! result-chan (try (run) (catch Throwable e e)))) (go (! close-chan true)) ( V\) needs to be true where \(V\) is the number of all nodes, so that \(V_w\) and \(V_r\) has at least 1 node overlapped. That means when a client reads data from \(V_r\) nodes, at least 1 node has the latest data. In our case, for the normal read transactions, \(V_r\) doesn't matter since it only reads from the primary so it's guaranteed to have the latest data. But when doing a failover, we need to make sure having \(V_r\) nodes available because primary is lost and we need to determine which node has the latest data. In the case of Patroni, with synchronous_node_count can be auto reduced and synchronous_mode_strict only guarantees data writes to at least 2 nodes, \(V_w\) is essentially set to 2 which means in order to maintain consistency, \(V_r > V - V_w = V - 2\) needs to be true, which means it only tolerates 1 node lose no matter the cluster size. But even with only 1 node lose in our test above, Patroni didn't implemented auto failover. So to make it better tolerate node lose, there should be an option similar to synchronous_node_count but actually enforce the minimal synced replication count instead of reduce it based on node availability. And if the available nodes meets the requirement of \ (V_r\), do the auto failover by comparing the largest LSN on each node. Minor Issue: Wrong Role Label for Kubernetes Pod At last there is a minor issue but also the first issue I found during the test: in the Patroni doc, it uses the command kubectl get pods -L role -o wide to show the role of each Patroni pod. However, it is inaccurate as confirmed in the Github issue. It's not a big deal but something need to be aware when operate Patroni. I think theoretically it may be able to be fixed by let the primary pod set the k8s labels for all the other pods. What's Next? Ideally, I still want to dig deeper into Patroni's test since it's a very popular PostgreSQL HA solution. The test above is only a carefully create scenario based on a known issue. Running larger scale tests with more combined failure scenarios may be able to find more failure modes. However, because the fundamental PostgreSQL replicat flaw described above and the effort needed to run the large scale tests, I may want to setup and test another solution first. The solution is what I had in mind even before I started Patroni's Jepsen test, which is setup replication with DRBD: instead of using PostgreSQL's replication, DRBD replicas the whole disk instead. With modern hardware, the performance with replication overhead should be acceptable but it remains to be tested, along with the correctness of it. Prev: SBT Task to Build Frontend Components Copyright @ 2008 - 2024 Bin Wang Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.