Open Source PolarDB Uses pgpool-II to Implement Transparent Read/Write Splitting

By digoal

Background

PolarDB's cloud-native computing-storage separation architecture offers cost-effective data storage, scalable operations, high-speed multi-machine parallel computing, and efficient data search and processing. By combining PolarDB with computing algorithms, it enables businesses to derive value from their data and turn it into productivity.

This article describes how open source PolarDB uses pgpool-II to implement transparent read/write splitting.

• pgpool-II is a middleware for PostgreSQL read/write splitting. Since PolarDB has a compute-storage separation architecture, you only need to configure the pgpool's load balancer and do not need to configure its High Availability (HA) function.

• For HA functionality, it is recommended to use PolarDB's open-source ecosystem products. When configuring pgpool, use the VIP (Virtual IP) corresponding to the rw (read-write) and ro (read-only) nodes, which is managed by Multiplier Technology's cluster management software.

The test environment used for this demonstration is macOS + Docker. For detailed instructions on PolarDB deployment, please refer to the following article: Simple Deployment of PolarDB

Deploy pgpool-II

cd ~  
wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.4.1.tar.gz -O pgpool-II-4.4.1.tar.gz  
  
tar -zxvf pgpool-II-4.4.1.tar.gz  
  
cd pgpool-II-4.4.1  
  
./configure --prefix=/usr/local/pgpool4.4.1 --with-openssl  
  
make -j 8  
sudo make install

Configure dynamic libraries and default paths.

sudo vi /etc/ld.so.conf  
# addd  
/usr/local/pgpool4.4.1/lib  
  
sudo ldconfig  
  
  
  
  
vi ~/.bash_profile   
# add  
export PATH=/usr/local/pgpool4.4.1/bin:$PATH  
  
. ~/.bash_profile

Configure pgpool-II

The PolarDB 3 nodes are configured as follows:

[postgres@1373488a35ab ~]$ netstat -anp|grep LISTEN  
tcp        0      0 0.0.0.0:5434            0.0.0.0:*               LISTEN      72/postgres           
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      9/postgres            
tcp        0      0 0.0.0.0:5433            0.0.0.0:*               LISTEN      33/postgres           
tcp6       0      0 :::5434                 :::*                    LISTEN      72/postgres           
tcp6       0      0 :::5432                 :::*                    LISTEN      9/postgres            
tcp6       0      0 :::5433                 :::*                    LISTEN      33/postgres           
unix  2      [ ACC ]     STREAM     LISTENING     22905    9/postgres           ./.s.PGSQL.5432  
unix  2      [ ACC ]     STREAM     LISTENING     18212    33/postgres          ./.s.PGSQL.5433  
unix  2      [ ACC ]     STREAM     LISTENING     24071    72/postgres          ./.s.PGSQL.5434  
  
  
[postgres@1373488a35ab ~]$ psql -p 5432 -c "select pg_is_in_recovery();"  
 pg_is_in_recovery   
-------------------  
 f  
(1 row)  
  
[postgres@1373488a35ab ~]$ psql -p 5433 -c "select pg_is_in_recovery();"  
 pg_is_in_recovery   
-------------------  
 t  
(1 row)  
  
[postgres@1373488a35ab ~]$ psql -p 5434 -c "select pg_is_in_recovery();"  
 pg_is_in_recovery   
-------------------  
 t  
(1 row)

PolarDB, similar to Aurora, shares storage cluster mode and does not require pgpool to manage HA.

https://www.pgpool.net/docs/latest/en/html/example-aurora.html

Configure pgpool.conf.

cd /usr/local/pgpool4.4.1/etc  
  
sudo vi pgpool.conf  
  
listen_addresses = '0.0.0.0'  
port = 9999  
unix_socket_directories = '/tmp'  
pcp_listen_addresses = 'localhost'  
pcp_port = 9898  
pcp_socket_dir = '/tmp'  
log_destination = 'stderr'  
logging_collector = on  
log_directory = '/tmp/pgpool_logs'  
pid_file_name = '/var/run/pgpool/pgpool.pid'  
logdir = '/tmp'  
  
backend_clustering_mode = 'streaming_replication'  
load_balance_mode = on  
sr_check_period = 0  
health_check_period = 0  
failover_on_backend_shutdown=off  
failover_on_backend_error=off  
enable_pool_hba = on  
  
backend_hostname0 = '127.0.0.1'  
backend_port0 = '5432'  
backend_weight0 = 1  
backend_application_name0 = 'polardb_primray'  
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'  
  
backend_hostname1 = '127.0.0.1'  
backend_port1 = '5433'  
backend_weight1 = 2  
backend_application_name1 = 'polardb_reader1'  
backend_flag1 = 'DISALLOW_TO_FAILOVER'  
  
backend_hostname2 = '127.0.0.1'  
backend_port2 = '5434'  
backend_weight2 = 2  
backend_application_name2 = 'polardb_reader2'  
backend_flag2 = 'DISALLOW_TO_FAILOVER'

Configure pool_hba.conf.

sudo vi pool_hba.conf  
# add  
host all all 0.0.0.0/0 md5

Configure the user password file pool_passwd of pgpool database.

[postgres@1373488a35ab etc]$ sudo pg_md5 --md5auth --username=digoal pwd123  
  
[postgres@1373488a35ab etc]$ cat /usr/local/pgpool4.4.1/etc/pool_passwd   
digoal:md531a770cec82aa37e217bb6e46c3f9d55  
  
  
  
-- It is actually the md5 value of pwd + username.
postgres=# select md5('pwd123digoal');  
               md5                  
----------------------------------  
 31a770cec82aa37e217bb6e46c3f9d55  
(1 row)

Create the corresponding user in the database.

postgres=# create user digoal superuser encrypted password 'pwd123' login;  
CREATE ROLE

Configure PCP to manage user password file pcp.conf.

postgres=# select md5('pwd123');  
               md5                  
----------------------------------  
 45cb41b32dcfb917ccd8614f1536d6da  
(1 row)  
  
  
  
cd /usr/local/pgpool4.4.1/etc  
sudo vi pcp.conf  
pcpadm:45cb41b32dcfb917ccd8614f1536d6da

Prepare the PID file directory and log directory of pgpool runtime.

sudo mkdir /var/run/pgpool  
sudo mkdir /tmp/pgpool_logs

Enable pgpool

sudo pgpool

View the pgpool listener.

[postgres@1373488a35ab pgpool_logs]$ netstat -anp|grep LISTE  
(Not all processes could be identified, non-owned process info  
 will not be shown, you would have to be root to see it all.)  
tcp        0      0 0.0.0.0:9999            0.0.0.0:*               LISTEN      -                     
tcp        0      0 0.0.0.0:5434            0.0.0.0:*               LISTEN      72/postgres           
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      9/postgres            
tcp        0      0 0.0.0.0:5433            0.0.0.0:*               LISTEN      33/postgres           
tcp        0      0 127.0.0.1:9898          0.0.0.0:*               LISTEN      -                     
tcp6       0      0 :::5434                 :::*                    LISTEN      72/postgres           
tcp6       0      0 :::5432                 :::*                    LISTEN      9/postgres            
tcp6       0      0 :::5433                 :::*                    LISTEN      33/postgres           
unix  2      [ ACC ]     STREAM     LISTENING     22905    9/postgres           ./.s.PGSQL.5432  
unix  2      [ ACC ]     STREAM     LISTENING     18212    33/postgres          ./.s.PGSQL.5433  
unix  2      [ ACC ]     STREAM     LISTENING     24071    72/postgres          ./.s.PGSQL.5434  
unix  2      [ ACC ]     STREAM     LISTENING     30964    -                    /tmp/.s.PGSQL.9999  
unix  2      [ ACC ]     STREAM     LISTENING     30967    -                    /tmp/.s.PGSQL.9898

Use PCP to manage commands to view the pgpool middleware status.

pcp_node_info -U pcpadm -p 9898  
Password:   
127.0.0.1 5432 2 0.200000 up unknown primary unknown 0 none none 2023-01-02 03:44:20  
127.0.0.1 5433 2 0.400000 up unknown standby unknown 0 none none 2023-01-02 03:44:20  
127.0.0.1 5434 2 0.400000 up unknown standby unknown 0 none none 2023-01-02 03:44:20


[postgres@1373488a35ab etc]$ pcp_node_count -U pcpadm -p 9898  
Password:   
3


pcp_pool_status  -U pcpadm -h localhost -p 9898  
Password:   
...  
name : backend_application_name1  
value: polardb_reader1  
desc : application_name for backend #1  
  
name : backend_hostname2  
value: 127.0.0.1  
desc : backend #2 hostname  
  
name : backend_port2  
value: 5434  
desc : backend #2 port number  
  
name : backend_weight2  
value: 0.400000  
desc : weight of backend #2  
  
name : backend_flag2  
value: DISALLOW_TO_FAILOVER  
desc : backend #2 flag  
...

Use the pgpool proxy to link to PolarDB.

export PGPASSWORD=pwd123  
export PGDATABASE=postgres  
psql -p 9999 -U digoal -c "select * from pg_stat_activity where pid=pg_backend_pid();"

Test the pgpool Read/Write Splitting

pgbench -i -s 1 -h 127.0.0.1 -p 9999 -U digoal postgres  
pgbench -n -r -P 1 -c 8 -j 8 -T 10 -S -h 127.0.0.1 -p 9999 -U digoal postgres  
  
  
  
  
  
[postgres@1373488a35ab ~]$ psql -p 5432 -c "select count(*) from pg_stat_activity where application_name='pgbench';"  
 count   
-------  
     8  
(1 row)  
  
[postgres@1373488a35ab ~]$ psql -p 5433 -c "select count(*) from pg_stat_activity where application_name='pgbench';"  
 count   
-------  
     8  
(1 row)  
  
[postgres@1373488a35ab ~]$ psql -p 5434 -c "select count(*) from pg_stat_activity where application_name='pgbench';"  
 count   
-------  
     8  
(1 row)

References

https://www.pgpool.net/docs/latest/en/html/index.html

Community

Open Source PolarDB Uses pgpool-II to Implement Transparent Read/Write Splitting

Background

Deploy pgpool-II

Configure pgpool-II

Test the pgpool Read/Write Splitting

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

PolarDB for Xscale

PolarDB for MySQL

Managed Service for Prometheus