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.
  • Choose an IPv4 address as the 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"
    ${AWSCLI} 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"
        ${AWSCLI} 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"
    ${AWSCLI} 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} has been successfully brought ${CMD}"
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

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: # ...
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authen...
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize...
Read more