This article is contributed. See the original author and article here.

Postgres is an amazing RDBMS implementation. Postgres is open source and it’s one of the most standard-compliant SQL implementations that you will find (if not the most compliant.) Postgres is packed with extensions to the standard, and it makes writing and deploying your applications simple and easy. After all, Postgres has your back and manages all the complexities of concurrent transactions for you.

In this post I am excited to announce that a new version of pg_auto_failover has been released, pg_auto_failover 1.4.

pg_auto_failover is an extension to Postgres built for high availability (HA), that monitors and manages failover for Postgres clusters. Our guiding principles from day one have been simplicity, and correctness. Since pg_auto_failover is open source, you can find it on GitHub and it’s easy to try out. Let’s walk through what’s new in pg_auto_failover, and let’s explore the new capabilities you can take advantage of.


 


road-with-pg-auto-failover-painted-in-yellow-on-asphalt.png 


 


Fault Tolerance and Graceful Degradation


 


As soon as your application depends on Postgres in production, many of you want to implement a setup that is resilient to the most common failures we know about. That’s what Fault Tolerance is all about:


 



Fault tolerance is the property that enables a system to continue operating properly in the event of the failure of (or one or more faults within) some of its components. If its operating quality decreases at all, the decrease is proportional to the severity of the failure, as compared to a naively designed system, in which even a small failure can cause total breakdown. Fault tolerance is particularly sought after in high-availability or life-critical systems. The ability of maintaining functionality when portions of a system break down is referred to as graceful degradation.



 


If you are running your application in the cloud and you’re using a managed database service—such as Azure Database for PostgreSQL—then you’re probably relying on your database service provider to implement HA for you. But what if you are managing your Postgres database yourself, and you need your application to be highly available?


 


What if you are managing your Postgres database yourself and you need HA?


 


We developed the open source pg_auto_failover to implement fault tolerance for Postgres architectures in production. The first design of pg_auto_failover that we introduced last year—which gave you high availability and automated failover, as well as Postgres 12 support—already allowed you to address production incidents, and already implemented graceful degradation when either the primary or the secondary is unavailable.


 


With the latest release of pg_auto_failover 1.4.0 we are able to deliver high availability for your apps running on Postgres in even more failure scenarios—and we offer more options to implement graceful degradation in common use cases.


 


The new 1.4 release of pg_auto_failover for Postgres introduces a major new feature in the design referred to as “multiple standby” support by the development team.


 


While the term “multiple standby” may not seem that impressive, multiple standby support in pg_auto_failover for Postgres completely redefines what you can give your application in terms of high availability and automated failover and fault tolerance.


 


Single Standby for Failover Capabilities (what pg_auto_failover could already do before 1.4)


 


Before pg_auto_failover 1.4, a single Postgres standby node was already supported.


 


Postgres High Availability Architecture in pg_auto_failover with Single Standby. Depicted in this diagram are 1  primary & 1 standby (called a “secondary” node)Postgres High Availability Architecture in pg_auto_failover with Single Standby. Depicted in this diagram are 1 primary & 1 standby (called a “secondary” node)


 


Implementing single standby support for Postgres with pg_auto_failover was (and still is) as simple as running the following commands:


 


 

admin@monitor $ pg_autoctl create monitor ...
admin@monitor $ pg_autoctl show uri --monitor

admin@node1 $ pg_autoctl create postgres --monitor <uri> ...
admin@node2 $ pg_autoctl create postgres --monitor <uri> ...

 


 


The commands shared above use pg_autoctl which comes with the pg_auto_failover package install, and is the only entry point you need for all your pg_auto_failover usage: initial setup, on-going maintenance, system integration etc. See also the complete reference documentation for pg_autoctl within our pg_auto_failover docs.


 


Having automated failover to a single standby node in Postgres is a good and solid starting point to have. That said, our options to implement a graceful degradation are severely limited when it comes to the loss of the single standby node. This is because—prior to version 1.4 of pg_auto_failover—pg_auto_failover would always use synchronous commit.


 


Here is what happened before pg_auto_failover 1.4, back when we always used synchronous commit:



  • Using synchronous_commit for each transaction that Postgres commits locally, the client connection is put on hold until the commit is known to have made it to the standby node.


  • Postgres is very good at waiting, and very patient: If the standby node becomes unavailable, then the client connection stays on-hold for an infinite period of time (or at least until a restart).


  • This means that when the standby node is unavailable, all the PostgreSQL write traffic is put on hold on the primary. And this might not be the graceful service degradation that you want.



Postgres HA with a single standby: Service Availability or Data Availability, pick one


 


If we want to implement a Postgres failover mechanism that is safe for your data, we need to use synchronous_commit. But then when the single standby node is unavailable, all the SQL write traffic would be put on hold on the primary. Which might not be what you want: what if your customers are waiting for their transactions to process?


 


That’s why pg_auto_failover switches synchronous_commit off when the only known standby node is unavailable, and signals the currently waiting transactions. That’s a good implementation of the notion of a graceful degradation here: we can’t provide data security anymore, nor the failover capability, and we accept the situation and open the write traffic again on the primary node.


 


In this context “data security” means that after the standby node has become unavailable, a single copy of the data is maintained, on a single machine. The degradation implemented by pg_auto_failover in that case is graceful in terms of the service: writes are allowed to be processed again. It’s a real degradation for the data security though, and one that you might not be able to accept. Well if that is your situation, keep reading, as you can now create a second standby with pg_auto_failover 1.4.


 


It should be obvious now that it is impossible to achieve service and data availability with Postgres when using a single standby node. With a single standby the best we can achieve is failover when the primary is not available, and single-node Postgres when the standby is not available.


Multiple Standby Nodes in pg_auto_failover 1.4, for Availability of Service and Data


 


Starting with pg_auto_failover 1.4.0 it is now possible to register three Postgres nodes for the same Postgres service, and then implement a trade-off that is compliant with High Availability of both the service and the data.


 


Postgres High Availability Architecture in pg_auto_failover with Multiple Standbys. Depicted in this diagram are 1 primary and 2 standby (often called “secondary”) nodesPostgres High Availability Architecture in pg_auto_failover with Multiple Standbys. Depicted in this diagram are 1 primary and 2 standby (often called “secondary”) nodes


 


Implementing multiple standby support for Postgres HA with pg_auto_failover 1.4 is as simple as running the following commands:


 


 

admin@monitor $ pg_autoctl create monitor ...
admin@monitor $ pg_autoctl show uri --monitor

admin@nodeA $ pg_autoctl create postgres --monitor <uri> ...
admin@nodeB $ pg_autoctl create postgres --monitor <uri> ...
admin@nodeC $ pg_autoctl create postgres --monitor <uri> ...

 


 


In this classic Postgres HA architecture (as detailed in our docs in the Architectures with two standby nodessection and made possible by pg_auto_failover multiple standby support), we can afford to lose one of our standby nodes and continue to accept writes to the primary with synchronous_commit on, because we still have a functioning standby that can receive and replay all the transactions committed on the primary.


 


But what if we lost both of our standby nodes? Well, that would prevent our HA system to operate properly. If both standbys are lost, some of you will need Postgres to accept writes, and some of you will need Postgres to refuse writes because you can’t accept the risk that comes with maintaining a single copy of your production data. Of course, it is not the place of pg_auto_failover to make such a choice, so version 1.4.0 gives you the control you need to handle the situation in the best possible way for your application and business needs.


 


Our documentation covers the replication settings in the Multi-node Architectures section. Simplifying this documentation down to its essence, pg_auto_failover offers the replication setting named number-sync-standbys to control the degradation behavior when losing all the standby nodes. When set to zero, then you have the graceful behavior that we discussed previously where writes are allowed on the primary server, even though it’s the only node left and no other copy of the production data is maintained for you:


 


 

admin@monitor $ pg_autoctl set formation number-sync-standbys 0

 


 


By default, when you add a second standby that participates in the replication quorum then pg_auto_failover increments its number-sync-standbys from zero to one so that instead, when your primary node is the only one left, then all transactions that are writing data are put on-hold until a standby node is available again.


 


After all, protecting data durability on more than one node by default has been a good Postgres HA strategy for most of the existing HA systems out there, and is still the only available strategy in many HA systems.


 


Implementing the graceful degradation part of fault tolerance requires careful evaluation and advanced understanding of your business rules. With pg_auto_failover you can then implement your business needs as you see fit.


 


Postgres compatibility, including Postgres 13


 


With the new release of pg_auto_failover 1.4.0 our tool is compatible with Postgres versions 10, 11, 12, and 13.


 


The pg_auto_failover monitor runs a Postgres service internally: a part of our code is shipped as a Postgres extension named pgautofailover (without any underscores.) The pgautofailover extension is now compatible with Postgres 13, and the good news is that our binary packages for Postgres 13 will be published within the next month.


 


Getting started with pg_auto_failover for HA, want to implement your first failover?


 


What if you want to try out pg_auto_failover to implement a controlled failover, which some of you might call a switchover? You can test our Postgres failover mechanism with your application running and without having to introduce a low-level fault in your test environment.


 


Being able to orchestrate a failover (or switchover) in Postgres when all nodes are up and running also allows you to select a new primary node in your architecture—and can be used as a mechanism to migrate your Postgres production environment to a new physical setup (such as a new hardware or a new hosting facility).


 


Our pg_auto_failover documentation include developer commands for pg_autoctl with the following easter egg. You can setup a tmux session that runs a primary and two secondary Postgres nodes with only one pg_autoctl developer command:


 


 

$ PG_AUTOCTL_DEBUG=1 pg_autoctl do tmux session 
     --root /tmp/pgaf 
         --first-pgport 9000 
         --nodes 3 
         --layout tiled

 


 


When you try this command, then as soon as your setup is running (it usually takes from 30s up to about a minute on my laptop), then you can implement your first Postgres fully automated failover using pg_auto_failover:


 


 

$ PG_AUTOCTL_DEBUG=1 pg_autoctl perform switchover

 


 


pg_auto_failover 1.4 with multiple standby nodes


 


Now that you have done your first Postgres failover with pg_auto_failover, you can learn more about our open source HA solution by reading the pg_auto_failover documentation and trying the different Postgres HA architectures that are outlined there.


 


I recommend playing with and understanding the 3 replication settings:



  • number-sync-standbys

  • replication-quorum

  • candidate-priority


And then I recommend you play with the pg_autoctl enable maintenance and pg_autoctl disable maintenance commands. And of course, do as many failovers as you feel like you need to in order to understand the impact of those settings on the failover orchestration.


 


Now, if you play with pg_auto_failover and like it, or just like what you’ve read here, give us a star on the pg_auto_failover github repository. This means a lot because that’s how we know we are on track to provide a useful Postgres HA solution to you. And those stars could have an impact on how many people will contribute to this Open Source project!


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.