PgPool – II Installation & Configuration – (Part-I)
PGPool-II is an open-source proxy software that can be used for the PostgreSQL servers(ProxySQL for MySQL) environment. It lies between the application server/PostgreSQL client and the PostgreSQL database servers and can provide much-needed features like Load Balancing, Query Cache, Connection Pooling, and Watchdog. Each feature requires a detailed explanation with supporting examples. So, in this part let’s discuss the basic setup of PGPool-II with PostgreSQL streaming replication and in the upcoming parts, we will explore more about its features.
History of PGPool-II:
PGPool-II started its life as PGPool in 2003 just as a connection pooling software. Later in 2006, it was released as PGPool-II with many features and with the elimination of many limitations in PGPool. Still, PGPool-II is one of the most popular proxy available for the PostgreSQL servers.
The necessity of PGPool-II:
Apart from the advanced features, PGPool needs for few cases like automatic failover of connections to the backend healthy DB server and maximum connection errors(Too many connections) in the applications. Also, connection pooling reduces the overhead of creating new connections on the database.
Now, let’s start with the setup of PGPool-II for the two PostgreSQL servers(Primary and Secondary) configured with streaming replication. Considering as having the primary and the secondary already which setup has not been explained here.
| Server A | 192.168.33.11 | PGPool-II |
| Server B | 192.168.33.12 | Primary PG |
| Server C | 192.168.33.13 | Secondary PG |
Below is the architecture of this setup which is planned to achieve.
Installation of PGPool-II :
The following steps have been performed on server A.
yum install -y http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
2. Installing the PGPool-II
yum install -y pgpool-II-pg12-*
The PostgreSQL version used in this setup is 12, so the pg12* of PGPool-II is used for libraries and extension directories of PostgreSQL 12. Replace step 2 pg12* with the appropriate version of PostgreSQL.
In case of any difficulties, while installing using the package manager or unable to install the version required, it is good to go with source installation.
wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.2.1.tar.gz
2. Extract the downloaded file
It will create the directory pgpool-II-4.2.1
3. Install the below packages that need to compile the code
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install gcc make libpq -y
4. Post successful installation, configure, compile and install it using the below commands
./configure make make install
Configuration of PGPool-II:
The configuration file of PGPool-II is /etc/pgpool-II/pgpool.conf
First, creating two users on the primary server
- Monitor user – To perform a health check for the backend databases (only login is required)
- App user – For the application use case (Provide the required privileges based on the application purpose, read or write)
CREATE USER monitor WITH ENCRYPTED PASSWORD 'Monitor@321'; CREATE USER appuser WITH ENCRYPTED PASSWORD 'Appuser@321';
And creating a database for the monitor user
Make sure these users are replicated to the secondary server and also add an entry on the primary and secondary servers pg_hba.conf file to access the database from pgpool server and reload it.
host all all 192.168.33.11/32 md5
And reload it to take effect.
Preparing the configuration file for the pgpool-II on server A
## Connection Details listen_addresses='*' port=9999 socket_dir = '/var/run/postgresql' ## Backend Server Details # Primary backend_hostname0 = '192.168.33.12' backend_port0 = 5432 backend_weight0 = 1 # Secondary backend_hostname1 = '192.168.33.13' backend_port1 = 5432 backend_weight1 = 1 ## Load Balancing load_balance_mode = 'ON' ## Replication Responsibilty master_slave_mode = 'ON' master_slave_sub_mode = 'stream' ## Streaming checks sr_check_period = 10 sr_check_user = 'monitor' sr_check_password = 'Monitor@321' sr_check_database = 'monitor' delay_threshold = 10240 ## Client Authentication allow_clear_text_frontend_auth = ‘ON’
Here explaining the purpose of each variable on the config, it can be modified based on the requirements.
Listen_addresses – From which IP addresses pgpool accepts incoming requests. * means it accepts all requests.
Port – On which port, pgpool wants to listen for client requests. The port here using needs to be used on the application or the client to connect to the database.
Socket_dir – Directory for socket accepting connections
backend_hostname*, backend_port*, backend_weight* – hostname specifies the PostgreSQL backend server, port specifies the port number of the backend server, weight defines the load balance ratio of the backend server
Here we are having two servers, so the backend variable has id 0 and 1.
In case, if we are adding one more server as the secondary in the future, it needs to be added as backend_hostname2, backend_port2, backend_weight2.
load_balance_mode
To enable load balancing on incoming select queries to both the backend servers.
master_slave_mode, master_slave_sub_mode
To determine the replication system used for data replication. The value used is stream as it is a streaming replication.
Streaming checks variables are used to monitor the status of streaming replication using the created monitor user. The variable delay_threshold defines the maximum lag (in bytes) in the secondary server. If the lag exceeds, pgpool won’t serve any select queries to the secondary to avoid reading stale data on the application.
allow_clear_text_frontend_auth – It allows the pgpool to get the password in plain text from the frontend and use it for backend authentication.
Once all the above settings are done, restarting the pgpool takes all the settings into effect.
[root@server1 ~]# service pgpool restart Redirecting to /bin/systemctl restart pgpool.service [root@server1 ~]#
Checking the connection using the pgpool from pgpool server
[root@server1 data]# psql -h 192.168.33.11 -Uappuser -p 9999 postgres -c "select datname from pg_database" Password for user appuser: datname ----------- postgres test1 template1 template0 monitor aakash mydbops (7 rows)
Now doing some tests and checking the status of the node and the queries distribution on the pgpool.
[root@server1 ~]# watch -n1 "psql -h 192.168.33.11 -Uappuser -p 9999 postgres -c 'select datname from pg_database'"
[root@aakash2 ~]# psql -h 192.168.33.11 -Uappuser -p 9999 postgres -c "show pool_nodes" node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+------------------- -- 0 | 192.168.33.12 | 5432 | up | 0.500000 | primary | 21 | false | 0 | | | 2021-01-14 16:00:4 9 1 | 192.168.33.13 | 5432 | up | 0.500000 | standby | 7 | true | 0 | | | 2021-01-14 16:00:4 9 (2 rows)
From the above output, the node status is online and queries are distributed.
That’s it on the basic pgpool setup with streaming replication. It is easy to add more replicas under it by adding backend_* variables.
Current architecture will be a single point of failure(SPOF) in case server1 (pgpool) goes down. So, in the next blog, we will look on how to achieve high availability on pgpool servers (PGPool Cluster) using watchdog and Virtual IP.
Share this:
Новые версии pgpool
Собрал pgpool 3.7.22, но не работает команда pcp_watchdog_info.
Версия pgpool из дистрибутива Астры (pgpool2/stable,now 3.6.1-1 amd64 [installed]) работает, но хотелось бы более новую сборку.
Возможно кто-то собирал новый pgpool и сталкивался с данной проблемой.
Есть идеи почему так происходит?
DISTRIB_ID="AstraLinuxCE" DISTRIB_DESCRIPTION="Astra Linux CE 2.12.45 (Orel)" DISTRIB_RELEASE=2.12.45 DISTRIB_CODENAME=orelApr 12 09:43:48 astra-srv01 pgpool[1320]: 186 2023-04-12 09:43:48: pid 1320: DEBUG: pool_flush_it: flush size: 5 Apr 12 09:43:49 astra-srv01 pgpool[1126]: 50 2023-04-12 09:43:49: pid 1126: DEBUG: watchdog heartbeat: send heartbeat signal to astra-srv02:9694 Apr 12 09:43:51 astra-srv01 pgpool[1126]: 51 2023-04-12 09:43:51: pid 1126: DEBUG: watchdog heartbeat: send heartbeat signal to astra-srv02:9694 Apr 12 09:43:52 astra-srv01 pgpool[1354]: 50 2023-04-12 09:43:52: pid 1354: WARNING: get watchdog nodes command failed Apr 12 09:43:52 astra-srv01 pgpool[1354]: 50 2023-04-12 09:43:52: pid 1354: DETAIL: ipc command timeout Apr 12 09:43:52 astra-srv01 pgpool[1354]: 51 2023-04-12 09:43:52: pid 1354: ERROR: PCP: informing watchdog info failed Apr 12 09:43:52 astra-srv01 pgpool[1354]: 51 2023-04-12 09:43:52: pid 1354: DETAIL: invalid watchdog index Apr 12 09:43:52 astra-srv01 pgpool[1354]: 52 2023-04-12 09:43:52: pid 1354: DEBUG: received PCP packet Apr 12 09:43:52 astra-srv01 pgpool[1354]: 52 2023-04-12 09:43:52: pid 1354: DETAIL: PCP packet type of service 'X' Apr 12 09:43:52 astra-srv01 pgpool[1354]: 53 2023-04-12 09:43:52: pid 1354: DEBUG: PCP processing request, client disconnecting Apr 12 09:43:52 astra-srv01 pgpool[1354]: 53 2023-04-12 09:43:52: pid 1354: DETAIL: closing PCP connection, and exiting child Apr 12 09:43:52 astra-srv01 pgpool[1318]: 44 2023-04-12 09:43:52: pid 1318: DEBUG: PCP child reaper handler Apr 12 09:43:52 astra-srv01 pgpool[1318]: 45 2023-04-12 09:43:52: pid 1318: LOG: PCP process with pid: 1354 exit with SUCCESS. Apr 12 09:43:52 astra-srv01 pgpool[1318]: 46 2023-04-12 09:43:52: pid 1318: LOG: PCP process with pid: 1354 exits with status 0DEBUG: recv: tos="m", len=8 DEBUG: recv: tos="r", len=21 DEBUG: send: tos="W", len=7 DEBUG: recv: tos="N", len=97 BACKEND WARNING: get watchdog nodes command failed DETAIL: ipc command timeout DEBUG: recv: tos="E", len=99 ERROR: PCP: informing watchdog info failed DETAIL: invalid watchdog index DEBUG: send: tos="X", len=4