How to make Pgpool-II Leader Switchover Seamless on AWS - Updating Route Table

In my previous post I wrote about  how to configure and manage virtual IP for Pgpool-II on AWS. 

As I mentioned in my previous post, there are four methods to assign the virtual IP on AWS:

  • Assigning Elastic IP
  • Assigning secondary private IP
  • Updating route table
  • Updating Route53 record

This post describes how to use "updating route table" method to configure and manage virtual IP on AWS.

Architecture

In this post, we create three Pgpool-II instances across multiple Availability Zones within one region.

We assume that application is running in the same VPC. The figure below shows the application architecture, with the application instance in a public subnet and the Pgpool-II instances in private subnets.

 

Prerequisites

Before you start, ensure that:

  • You have created a PVC with one public subnet and three private subnets and each private subnet is in a different Availability Zone.
  • You have created four EC2 instances.
  • You have installed Pgpool-II in each Pgpool-II instance.
  • You have created a Unix user postgres in each Pgpool-II instance. In this tutorial we run Ppgool-II using Unix user postgres.
  • You have created a PostgreSQL cluster.
  • Determine an IPv4 address used for virtual IP which is used by the application to connect to Pgpool-II. This IP address should not belong to the IP addresses range for VPC.

In this post, we use the following configurations:


Application Instance Pgpool-II #1 Instance Pgpool-II #2 Instance Pgpool-II #3 Instance
VPC 10.0.0.0/16
Virtual IP 20.0.0.50
Subnet 10.0.1.0/24 10.0.11.0/24 10.0.12.0/24 10.0.13.0/24
Availability Zone us-west-2a us-west-2a us-west-2b us-west-2c
Private IP 10.0.1.10 10.0.11.10 10.0.12.10 10.0.13.10
Security Group SSH TCP 22 All traffic All All 10.0.0.0/16

Installing and Configuring AWS CLI

In this tutorial we use AWS CLI to access AWS services and modify route tables. When running AWS CLI commands, the AWS CLI needs credentials to access AWS services.

This section describes how to configure the credential settings.

Before configuring the AWS CLI credentials, you need to have an AWS account and access keys. If you don't have any exist access keys, generate the access keys from the AWS management console.

Run the following commands on all Pgpool-II instances.

Install the AWS CLI.

[ec2-user@all pgpool instances]$ curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
[ec2-user@all pgpool instances]$ unzip awscliv2.zip
[ec2-user@all pgpool instances]$ sudo ./aws/install
Run aws configure command to create the config and credentials files in Pgpool-II startup user's home directory. In this tutorial we run Pgpool-II using the Unix user postgres.
[ec2-user@all pgpool instances]$ sudo su - postgres
[postgres@all pgpool instances]$ aws configure
AWS Access Key ID [None]: <Your AWS Access Key ID>
AWS Secret Access Key [None]: <Your AWS Secret Access Key>
Default region name [None]: us-west-2
Default output format [None]: json

Confirm if the AWS CLI is configured correctly. If the following commands failed, check your AWS CLI configuration.

[postgres@all pgpool instances]$ TOKEN=$(curl -sX PUT "http://169.254.169.254/latest/api/token" -H "X-aws-ec2-metadata-token-ttl-seconds: 21600")
[postgres@all pgpool instances]$ curl -H "X-aws-ec2-metadata-token: $TOKEN" -s http://169.254.169.254/latest/meta-data/instance-id
i-0a632a8349b701357

Configuring Pgpool-II

This section describes how to install and configure Pgpool-II parameters to manage the virtual IP on AWS. This tutorial only describes the virtual IP related configuration parameters. For other configurations, please refer to docs.

To assign and manage the virtual IP you need to configure the following configuration parameters.

1. Set the virtual IP address to delegate_IP.  In this post, we use 20.0.0.50.

delegate_IP = '20.0.0.50'
2. Because arping_cmd isn't required on AWS, set arping_cmd = 'true' to always return a successful exit status.
arping_cmd = 'true'
3. Configure if_up_cmd and if_down_cmd used to rewrite the route entry for virtual IP.
if_up_cmd = '<path to script> up $_IP_$ <interface> <route table ID> <path to awscli>'
if_down_cmd = '<path to script> down $_IP_$ <interface> <route table ID> <path to awscli>'

  • <path to script>: Specify the path to the script used to rewrite the route entry for virtual IP.
  • <interface>: Specify the network interface.
  • <route table ID>: Specify the route table ID associated with the public subnet (Application instance).
  • <path to awscli>: Specify the path to AWS CLI command.
The following example shows sample settings. Replace them with your own settings.
if_up_cmd = '/etc/pgpool-II/if_cmd.sh up $_IP_$ eth0 rtb-08ba4c1b34 /usr/local/bin/aws'
if_down_cmd = '/etc/pgpool-II/if_cmd.sh down $_IP_$ eth0 rtb-08ba4c1b34 /usr/local/bin/aws'

Create if_cmd.sh script on all Pgpool-II instances.

[ec2-user@all pgpool instances]$ sudo su - postgres

[postgres@all pgpool instances]$ cat /etc/pgpool-II/if_cmd.sh #!/bin/bash CMD=$1 VIP=$2 LOCAL_INTERFACE=$3 ROUTE_TABLE_ID=$4 AWSCLI=$5 TOKEN=$(curl -sX PUT "http://169.254.169.254/latest/api/token" -H "X-aws-ec2-metadata-token-ttl-seconds: 21600") INSTANCE_ID=$(curl -H "X-aws-ec2-metadata-token: $TOKEN" -s http://169.254.169.254/latest/meta-data/instance-id) if [ "_${CMD}" == "_up" ]; then echo "running if_up_cmd" echo "create route entry for ${VIP}/32 if it doesn't exist" aws ec2 create-route --route-table-id ${ROUTE_TABLE_ID} \ --destination-cidr-block ${VIP}/32 --instance-id ${INSTANCE_ID} if [ $? -ne 0 ]; then echo "replace route entry for ${VIP}/32" aws ec2 replace-route --route-table-id ${ROUTE_TABLE_ID} \ --destination-cidr-block ${VIP}/32 --instance-id ${INSTANCE_ID} --output text fi if [ $? -ne 0 ]; then echo "ERROR: failed to replace route entry for ${VIP}/32" exit 1 fi echo "assign virtual IP ${VIP}/32 to local network interface ${LOCAL_INTERFACE}" /usr/bin/sudo /sbin/ip addr add ${VIP}/32 dev ${LOCAL_INTERFACE} label ${LOCAL_INTERFACE}:1 if [ $? -ne 0 ]; then echo "ERROR: failed to assign virtual IP ${VIP}/32" exit 1 fi elif [ "_${CMD}" == "_down" ]; then echo "running if_down_cmd" echo "remove virtual IP ${VIP}/32 from local network interface ${LOCAL_INTERFACE}" /usr/bin/sudo /sbin/ip addr del ${VIP}/32 dev ${LOCAL_INTERFACE} if [ $? -ne 0 ]; then echo "ERROR: failed to remove virtual IP ${VIP}/32" fi echo "remove route entry for ${VIP}/32" aws ec2 delete-route --route-table-id ${ROUTE_TABLE_ID} --destination-cidr-block ${VIP}/32 if [ $? -ne 0 ]; then echo "ERROR: failed to remove virtual IP ${VIP}/32" fi echo "remove remaining route entry if any" ip route show to exact ${VIP}/32 dev ${LOCAL_INTERFACE} | xargs -r ip route delete ip route show table local to exact ${VIP}/32 dev ${LOCAL_INTERFACE} | xargs -r ip route delete fi echo "virtual IP ${VIP} ${CMD} successfully" exit 0

Make /etc/pgpool-II/if_cmd.sh executable.

[postgres@all pgpool instances]$ chmod +x /etc/pgpool-II/if_cmd.sh

Disabling source/destination check

EC2 instance checks the source or the destination of any received traffics by default.

You must disable source/destination checks on each Pgpool-II instance to route the traffic for virtual IP to the target instance.

To disable source/destination checks, run the following command on all Pgpool-II instances.

This tutorial assumes the local network interface is eth0.

[ec2-user@all pgpool instances]$ sudo su - postgres
[postgres@all pgpool instances]$ LOCAL_INTERFACE=eth0
[postgres@all pgpool instances]$ MAC_ADDR=$(ip -br link show dev ${LOCAL_INTERFACE} | tr -s ' ' | cut -d ' ' -f3)
[postgres@all pgpool instances]$ EC2_NETWORK_INTERFACE_ID=$(curl -H "X-aws-ec2-metadata-token: $TOKEN" -s http://169.254.169.254/latest/meta-data/network/interfaces/macs/${MAC_ADDR}/interface-id)
[postgres@all pgpool instances]$ aws ec2 modify-network-interface-attribute --network-interface-id ${EC2_NETWORK_INTERFACE_ID} --no-source-dest-check

Starting Pgpool-II

After configuring Pgpool-II, start Pgpool-II using the following command.

[ec2-user@all pgpool instances]$ sudo systemctl start pgpool

Verify the virtual IP

Now, let's verify the connectivity between application instance and the virtual IP.

First, move to the application instance using SSH.

Then, ping the virtual IP to test whether application instance can communicate with the virtual IP 20.0.0.50.

You should see output similar to the following:

[ec2-user@ip-10-0-1-10 ~]$ ping 20.0.0.50 -c 3
PING 20.0.0.50 (20.0.0.50) 56(84) bytes of data.
64 bytes from 20.0.0.50: icmp_seq=1 ttl=64 time=0.486 ms
64 bytes from 20.0.0.50: icmp_seq=2 ttl=64 time=0.532 ms
64 bytes from 20.0.0.50: icmp_seq=3 ttl=64 time=0.580 ms

--- 20.0.0.50 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2040ms
rtt min/avg/max/mdev = 0.486/0.532/0.580/0.046 ms

Next, stop the leader Pgpool-II to manually trigger a switchover.  In this example, Pgpool-II #1 was the leader node.

[ec2-user@ip-10-0-11-10 ec2-user]# sudo systemctl stop pgpool

Then, ping the virtual IP again.

[ec2-user@ip-10-0-1-10 ~]$ ping 20.0.0.50 -c 3
PING 20.0.0.50 (20.0.0.50) 56(84) bytes of data.
64 bytes from 20.0.0.50: icmp_seq=1 ttl=64 time=0.431 ms
64 bytes from 20.0.0.50: icmp_seq=2 ttl=64 time=0.535 ms
64 bytes from 20.0.0.50: icmp_seq=3 ttl=64 time=0.511 ms

--- 20.0.0.50 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2080ms
rtt min/avg/max/mdev = 0.431/0.492/0.535/0.048 ms

The output above shows that the application instance can communicate with the virtual IP 20.0.0.50 successfully even if the virtual IP is re-assigned to another instance.

Comments

Popular posts from this blog

Query Load Balancing in Pgpool-II

Installing Pgpool-II on Debian/Ubuntu

Connection Pooling in Pgpool-II