PostgreSQL Tutorial: High Availability using Virtual IP

February 27, 2025

Summary: In this tutorial, we will learn how to achieve high availability using virtual IP in PostgreSQL.

Table of Contents

Setup a Virtual IP (VIP) on the nodes

A virtual IP is basically a secondary IP configured on the server which can be used to connect to the instance. We will use this VIP to connect to the primary database from the application end. This will be done by rerouting the connections coming over the VIP to point to the network interface of the primary database.

In case of disaster, the repmgr daemon process records the primary node going down. Once it verifies that the primary database is not reachable, it will promote the standby instance as primary. Once the new primary is up, we will update the routing tables to reroute the VIP to connect to the new primary node so that the application continues to fetch data over the same IP address.

To set up a virtual IP, first we need to make sure the IP being used as a VIP is out of the allowed IP range of the underlying VPC or virtual network, so that it does not conflict with any future IP allotments on the VPC. To define the VIP, we will use the netplan utility that governs networking in Linux systems. We will need to create a new netplan configuration for attaching the failover IP on all the servers in the replication cluster as shown below:

Login to the server as a root user.
Create the configuration file /etc/netplan/60-failover-ip.yaml to setup failover IP.
Add below contents in the config file, where virtual.ip denotes the virtual IPv4 address we are using as a failover IP on the nodes:
```
network: version: 2 ethernets: eth0: addresses: - virtual.ip/32
```
Apply the Network configuration using the below command:
```
sudo netplan apply
```

You can verify the new VIP is added to the server using ifconfig to check the network configuration.

Once the new VIP is configured, we need to create routes to make this VIP point to the Network Interface of the primary node in the route tables. You can do this using the AWS console under Route Tables service or using AWS CLI using below command for all the associated route tables:

aws ec2 create-route --route-table-id $route_table_id --destination-cidr-block virtual.ip/32 --network-interface-id $network_interface_id

One of the considerations to check when implementing the VIP failover setup is to disable the Source / Destination check for the EC2 instance on the AWS console. This check makes sure that the EC2 instance is either the Source or Destination of any traffic it sends or receives. By disabling the check we are allowing the EC2 instance to use VIPs to send / receive traffic instead of its original IP.

Here is a quick way to check and update the Source / Destination check for the EC2 instances using AWS CLI:

Check current state of the Source / Destination check attribute:

aws ec2 describe-instance-attribute --instance-id $AWS_INSTANCE_ID --attribute sourceDestCheck

Disable Source / Destination check:

aws ec2 modify-instance-attribute --instance-id $AWS_INSTANCE_ID --no-source-dest-check

Handling repmgr events to perform VIP failover

During a disaster, or a manual switchover, we will need to switch the VIP to point to the new Primary node in the route tables, so that the application can continue to access data. The process of updating Routing tables and/or reattaching Elastic IP to the new Primary can be automated using repmgr events recorded and monitored by the daemon process. We can set up a repmgr hook script to execute the script which will update the routing table to point to the network interface of the new primary node. We can configure the repmgr event hook script in the repmgr config file using below parameters:

event_notifications = 'standby_register, standby_promote, primary_register, node_rejoin, standby_switchover'
event_notification_command = '/etc/repmgr/repmgr-hook.sh %n %e %s "%t" "%d" >/tmp/repmgr-hook.out.$$ 2>&1'

Here, the event_notifications parameter governs the list of events, for which the repmgr daemon can invoke the event_notification_command.

Below is the format of the event_notification_command:

%n - node ID
%e - event type
%s - success (1) or failure (0)
%t - timestamp
%d - details

Using the event notifications, we can invoke AWS CLI commands from a hook script to update the route tables and Elastic IP associations when repmgr observes a standby_promote event. We can also update the Route 53 DNS pointings, if the database is reachable externally, using CLI commands in the hook script.

Below is an example command using AWS CLI to update a route table association, to redirect the VIP to the Network interface of the new primary node:

aws ec2 replace-route --route-table-id $route_table_id --destination-cidr-block virtual.ip/32 --network-interface-id $network_interface_id_primary

Once everything is configured and you are able to connect to the primary database using the virtual IP, we can update the connection strings at the application end to point to the VIP to connect to the database. After this is done, your application will still connect to the primary node using the same VIP, and the repmgr daemon will invoke repmgr hook script to handle the task of pointing the VIP to the current primary node.

Handling Split Brain situations

In case of a split brain scenario, where the old primary database is started again after the failover is complete, the application will still point to the correct, new primary node, as the VIP is routing the connections to the network interface of the new primary node. This prevents the application from connecting to the rebooted failed node and causing data integrity issues.

Using the repmgr checks, we can also monitor such split brain situations, where repmgr detects the replication is broken and both the nodes are working as primary. In such situations, the repmgr service status command in old primary node will show the current state of standby node as “running as primary” as seen below:

postgres@test_node_1:~$ repmgr service status -f /etc/repmgr/repmgr.conf
 ID | Name        | Role    | Status               | Upstream | repmgrd | PID  | Paused? | Upstream last seen
----+-------------+---------+----------------------+----------+---------+------+---------+--------------------
 1  | Test_node_1 | primary | * running            |          | running | 4000 | no      | n/a
 2  | Test_node_2 | standby | ! running as primary |          | running | 2011 | no      | n/a

WARNING: following issues were detected
  - node "Test_node_2" (ID: 2) is registered as standby but running as primary

When the replication cluster comes in such a state, we can use the repmgr node rejoin to force the old primary to rejoin the replication cluster as a standby node. Below is an example using the node rejoin command to reattach the old primary as a standby node:

postgres@test_node_1:~$ repmgr node rejoin --force-rewind -f /etc/repmgr/repmgr.conf -h test_node_2 -U repmgr
NOTICE: rejoin target is node "Test_node_2" (ID: 2)
NOTICE: pg_rewind execution required for this node to attach to rejoin target node 2
DETAIL: rejoin target server's timeline 29 forked off current database system timeline 28 before current recovery point 0/56000028
NOTICE: executing pg_rewind
DETAIL: pg_rewind command is "/usr/lib/postgresql/11/bin/pg_rewind -D '/var/lib/postgresql/11/main' --source-server='host=test_node_2 user=repmgr dbname=repmgr port=5432 connect_timeout=2'"
NOTICE: 0 files copied to /var/lib/postgresql/11/main
NOTICE: setting node 1's upstream to node 2
WARNING: unable to ping "host=test_node_1 user=repmgr dbname=repmgr port=5432 connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "sudo systemctl start postgresql@11-main"
INFO: executing notification command for event "node_rejoin"
DETAIL: command is: /etc/repmgr/repmgr-agent.sh 1 node_rejoin 1 "2022-12-31 16:59:19.837178+00" "node 1 is now attached to node 2" >/tmp/repmgr-agent.out.$$ 2>&1
WARNING: unable to execute event notification command
DETAIL: parsed event notification command was: /etc/repmgr/repmgr-agent.sh 1 node_rejoin 1 "2022-12-31 16:59:19.837178+00" "node 1 is now attached to node 2" >/tmp/repmgr-agent.out.$$ 2>&1
NOTICE: NODE REJOIN successful
DETAIL: node 1 is now attached to node 2

After the execution is complete, you should see the old primary joined back to the replication cluster as a standby node.

postgres@test_node_1:~$ repmgr service status -f /etc/repmgr/repmgr.conf
 ID | Name        | Role    | Status    | Upstream    | repmgrd | PID  | Paused? | Upstream last seen
----+-------------+---------+-----------+-------------+---------+------+---------+--------------------
 1  | Test_node_1 | standby |   running | Test_node_2 | running | 4000 | no      | 1 second(s) ago
 2  | Test_node_2 | primary | * running |             | running | 2011 | no      | n/a

Monitoring and implementing the node rejoin during a split brain scenario can be automated as well to add fault tolerance capability to the HA cluster, making it a reliable solution for critical workloads.

At this point, we have successfully achieved high availability with fault tolerance in this cluster. This cluster can automatically detect and quickly recover from any sort of failure with minimal interruptions to the end user. The cluster is also highly reliable, it can detect dangerous scenarios like split brain on the cluster and successfully recover without any impact on the user experience. This cluster is now sufficiently equipped to provide close to 100% availability and have minimal interruptions while handling critical workloads.