How to configure a High Availability System in PostgreSQL

We configure the replica system using the asynchronous streaming replication technique. At the end of this practical class you will have a replica system working between the main database server and a standby server.

We download, install and configure the repmgr package with which we can configure the failover, a very useful feature that allows a standby server to promote to new master server in the event of a failure of the current master.

All nodes that are part of the replica system must be registered in the repmgr database with their corresponding configuration (connection information, name, role, etc.).

In this practical class we will see how to register the nodes and how to monitor them to know their status at all times.

In this practical class we simulate a serious failure in the main server that causes the service to crash. Thanks to the failover configuration carried out in the previous class, we will see how the standby server promotes to master, thus allowing the continuity of the service.

Once the problem that caused the primary server crash has been solved, it has to be recovered (cloned) and incorporated back into the replica system as a "new replica" that acts as a backup for the "new master" that was promoted after the failover.

In this class we will see how a server is cloned and incorporated into the replica system as a standby.

Switchover means reversing the roles between the two servers.

Often the main server is a much more powerful server regarding the processor, memory, etc. When a failover occurs, a standby server promotes to "new master" which is fine, as it provides that High Availability that we are after. The master can be cloned and incorporated into the replica system as a "new standby" but perhaps we do not want to remain in this state for long and we want the powerful server to be the master again.

This is why we have the switchover process, which we will explain in PostgreSQL.

In this class we configure two virtual IPs in each of the nodes: one for the connection with the master and another for the connection with the standby.

Each server will pick up the corresponding virtual IP and, from that moment, clients can be reconfigured to always connect to a single IP.

We need the virtual IPs to go up and down on the servers as the role changes occur. For example, if one of the two servers is down, the other will assume the two virtual IPs.

In so doing, customers will not need to know how many nodes the replica system has, which nodes are active and which ones are down, or what role each active node has. They always connect to the virtual IP with which they work (IP of the master, or IP of the standby) and do not have to make changes in their connection strings.

In this practical class we do the final failover test: we cause the main server to crash and watch how the failover takes place, and the correct configuration of the virtual IPs.

In this practical class we recover the crashed server (the old master) and check that it is perfectly incorporated into the replica system as a new standby, and acquires the virtual IP of the standby which, in turn, falls from the server that currently acts as "new master".

This time we perform a switchover, a role reversal to return the master role to the main server and the standby role to the secondary server. Virtual IPs are reversed too, as expected ;-)

This theoretical class mentions other high availability systems that could be configured.