Read/Write Splitting with Pgpool

This step uses an ECS using CentOS 7 and PostgreSQL 12 as an example. For other OS and database versions, adjust the parameter values in the following commands as needed.

Log in to the ECS and run the following commands to install PostgreSQL 12 and pgpool:

# Configure a yum repository in the environment.
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Search for the postgresql package.
sudo yum search all postgresql
# Search for the pgpool package.
sudo yum search all pgpool
# Install PostgreSQL 12.
sudo yum install -y postgresql12-server
# Install pgpool 12.
sudo yum install -y pgpool-II-12-extensions

Use pgpool to implement load balancing for access. Authentication occurs between the client and pgpool. The client still needs to pass the authentication of PostgreSQL.

1. Query the pgpool installation path.

   rpm -qa | grep pgpool

   Figure 1 Execution results
   
2. Run the SQL statements below on the RDS for PostgreSQL DB instance to create a user who is authorized to manage the DB instance.
   Create a user who is authorized to manage the DB instance.

   create role digoal login encrypted password 'xxxxxxx';
   create database digoal owner digoal;

   Create a user who is authorized to log in to postgres or a specified database and check the health heartbeats between pgpool and the read replica. With the parameters of pgpool properly configured, this user can check the WAL replay latency on the read replica.

   create role nobody login encrypted password 'xxxxxxx';

   xxxxxxx: password of the new database user

3. Modify the pgpool.conf file.

   cd /etc/pgpool-II-12/  
   
   cp pgpool.conf.sample-stream pgpool.conf  
   
   vi pgpool.conf

   • /etc/pgpool-II-12/: directory where pgpool is installed
   • pgpool.conf.sample-stream: default configuration file template provided by pgpool for streaming replication
   • pgpool.conf: pgpool configuration file
4. Press i to enter the edit mode. Modify the following items based on logs. You can choose to modify other configuration details based on the comments in the pgpool.conf file.

   listen_addresses = '*'   
   
   backend_hostname0 = 'IP address of the primary instance'
   backend_port0 = 5432
   backend_flag0 = 'ALWAYS_MASTER'
   
   backend_hostname1 = 'IP address of the read replica'
   backend_port1 = 5432
   backend_flag1 = 'ALLOW_TO_FAILOVER'
   
   enable_pool_hba = on
   
   pid_file_name = '/var/run/pgpool-II-12/pgpool.pid'

   • listen_addresses: the IP addresses that pgpool listens on. In this example, specify all IP addresses.
   • backend_hostname: the IP address of the backend PostgreSQL database node to be connected
   • backend_port: the port of the backend PostgreSQL database node that is connected to
   • backend_flag: used by pgpool to control node behavior
5. Use MD5 authentication to configure pool_passwd.

   pg_md5 --md5auth --username=digoal "xxxxxxx"  
   pg_md5 --md5auth --username=nobody "xxxxxxx"

   The passwords of digoal and nobody are generated and automatically written to the pool_passwd file.

6. Check the automatically generated pool_passwd file.

   cd /etc/pgpool-II-12
   
   cat pool_passwd

   pool_passwd: configuration file for pgpool to store encrypted passwords of PostgreSQL users

   Figure 2 Execution results
   
7. Configure the pgpool_hba file.

   cd /etc/pgpool-II-12  
   cp pool_hba.conf.sample pool_hba.conf  
   vi pool_hba.conf  
   
   # Press i to enter the edit mode.
   # Add the following content to the pool_hba.conf file:
   host all all 0.0.0.0/0 md5

   • pool_hba.conf.sample: a configuration file template for client connection authentication rules
   • pool_hba.conf: authentication rules for client connections to pgpool
   Figure 3 Execution results
   
8. Configure the PCP password file.

   This file is used to manage the users and passwords of pgpool, instead of database users and passwords.

   cd /etc/pgpool-II-12 
   
   pg_md5 abc # The password abc is used as an example.
   900150983cd24fb0d6963f7d28e17f72
   
   cp pcp.conf.sample pcp.conf  
   
   vi pcp.conf  
   
   # Press i to enter the edit mode.
   # Add the following content to the pcp.conf file:
   USERID:MD5PASSWD  
   manage:900150983cd24fb0d6963f7d28e17f72 # The manage user is used to manage the PCP password file.

   • pcp.conf.sample: a template used to generate pcp.conf
   • pcp.conf: a file used for authentication by the PCP interface. It defines the username and encrypted password for an administrator to remotely manage pgpool.

1. Start pgpool.

   cd /etc/pgpool-II-12
   
   pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf

2. Use pgpool to connect to the DB instance.

   psql -h 127.0.0.1 -p 9999 -U digoal -d postgres

   • 127.0.0.1: the IP address specified by listen_addresses in the pgpool.conf file
   • 9999: the default listening port configured in pgpool.conf
   Figure 4 Execution results
   
3. Check the pgpool cluster status.

   show pool_nodes;

   Figure 5 Execution results
   
4. Run the command below on the database, disconnect from and reconnect to the database, and query pg_is_in_recovery() again. If t and f are returned alternately, requests are sent to the primary instance and read replica alternately, indicating that read/write splitting is successful.

   SELECT pg_is_in_recovery();

   pg_is_in_recovery() is used to check whether the DB instance is in recovery mode. If t is returned, requests were sent to the read replica. If f is returned, requests were sent to the primary instance.

   Figure 6 Execution results
   

You can run pgpool --help to obtain more information about the commands used in pgpool. For example:

cd /etc/pgpool-II-12

pgpool -f ./pgpool.conf -m fast stop

Add new configuration information according to the format of the pgpool.conf file. Example:

backend_hostname1 = 'xx.xx.xxx.xx'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'

backend_hostname2 = 'xx.xx.xx.xx'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'

If t is always returned, pgpool is connected only to the read replica. If f is always returned, pgpool is connected only to the primary instance. Abnormal nodes are always displayed in the show pool_nodes; command output.

Figure 7 Execution results

You can clear the pgpool_status configuration file and restart pgpool. Example:

cd /etc/pgpool-II-12

# Stop the service.
pgpool -f ./pgpool.conf -m fast stop

# Delete the configuration file.
rm /tmp/pgpool_status

# Restart the service.
pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf

If the fault persists, you can use pcp_attach_node to register the primary instance or read replica again. pcp_attach_node is a cluster management tool provided by pgpool. It can re-register nodes in a cluster with the cluster.

• Check the PCP connection user.

  cd /etc/pgpool-II-12
  
  cat pcp.conf

  Figure 8 Execution results
  
• Use pcp_attach_node to register nodes again.

  pcp_attach_node -h local-IP-address -U pcp_user node_id

  Example:

  pcp_attach_node –h 127.0.0.1 –U manage 0