Configuring and Managing VIP for Pgpool-II on AWS

-

It is possible to install and configure Pgpool-II in EC2 instances. However, the common ways to assign VIP can't be used in the Cloud. We need to consider alternatives for assigning VIP in the Cloud.

This post describes how to configure and manage VIP in AWS.

There several ways to configure VIP in AWS:

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

 Multi-AZ Support Client Application Pgpool-II Instance Subnet
Assigning Elastic IP ○ connect from the internet
 public
Assigning secondary private IP × within VPC
 private
Updating route table ○ within VPC private
Updating Route53 record ○ within VPC
other VPCs
on-premise		 private

 

Assigning Elastic IP 

Elastic IP is a public IPv4 address, which is reachable from the internet, and it can be associated with an EC2 instance. 

The Elastic IP should be associated with leader Pgpool-II instance and clients connect to Pgpool-II via Elastic IP. If the leader Pgpool-II goes down, it disassociates the Elastic IP, and then the new leader Pgpool-II associate the Elastic IP with own instance.

This method supports multiple availability zones.

Assigning Secondary Private IP

Multiple private IP addresses can be assigned for EC2 instances.

You can assign a secondary private IP address to leader Pgpool-II instance. If the leader Pgpool-II goes down, then reassign it to the new Pgpool-II instance. 

Because the private IP address is not reachable over the internet, you need to locate your application instance in the same VPC. 

Note: A secondary private IP address cannot be assigned across multiple availability zones.

Updating Route Table 

This method can be useful if you want to locate Pgpool-II instances across multiple availability zones, and Pgpool-II can only be connected within VPC.

You need to create a route entry for the VIP to redirect the traffic to leader Pgpool-II instance. If the leader Pgpool-II goes down, then redirect the traffic to the new leader Pgpool-II instance by updating the VIP route entry.

This method supports multiple availability zones.

Updating Route53 Record 

This method can be useful if you want to locate Pgpool-II instances in private subnets and connect to it from on-promise or other VPCs. This method supports multiple availability zones.

Route53 is a DNS web service provided by AWS.

Configure Route53 and add a DNS A record which should route the traffic to leader Pgpool-II instance. If the leader Pgpool-II goes down, then route the traffic to the new leader Pgpool-II instance by updating the A record.

Note: the DNS record updates may take time.

Conslusion 

In this blog, I have described several ways to configure and manage VIP in the Cloud. 

If you want to locate Pgpool-II instances in private subnets across multiple availability zones and speed up failover, "Updating Route Table" is recommended.

In the next post, I will describe detailed information about configuring VIP by updating route table.

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