What is Pgpool-II

Pgpool-II is a unique middleware solution, specially designed to optimize and scale the capabilities of the PostgreSQL database management system. It offers numerous benefits such as connection optimization, load balancing, and data replication, making Pgpool-II an indispensable tool in managing PostgreSQL deployments. In this detailed guide, we will go through the steps to install and configure Pgpool-II on the Ubuntu Linux operating system, helping you maximize the performance and high availability of your database.

Installation Architecture

Before we start, we need to prepare 4 servers

Install PostgreSQL Replication

Install PostgreSQL 16 Replication on 3 servers postgresql-master and postgresql-slave-01, postgresql-slave-02.

Install PGpool-II 4.5.0

Step 1: Install PGpool-II 4.5.0

Install make and gcc

*** GNU make version 3.80 or newer is required; other make programs or older GNU make versions will not work. (GNU make is sometimes installed under the name gmake.) To check GNU, enter:

sudo apt update
sudo apt install -y make gcc libpq-dev

After installation, check the version of make

make --version

The current version of make is 4.3

gcc --version

The current version of gcc is 11.4.0

Download PGpool-II installation package

wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.0.tar.gz -O pgpool-II-4.5.0.tar.gz

Extract and install

# extract
tar -xvf pgpool-II-4.5.0.tar.gz
cd pgpool-II-4.5.0

Next, we install memcached and libmemcached-dev and libpq-dev

sudo apt install -y libmemcached-dev libpq-dev

Then, we will configure and install PGpool-II by performing the following steps:

./configure --prefix=/home/pgpool2 --with-memcached=/usr/sbin/memcached 
make && sudo make install

You can customize the build and installation process by providing one or more of the following command line options to configure:

After configuring, we proceed to create ln -la to create a link to the /usr/sbin directory

sudo ln -s /home/pgpool2/bin/* /usr/sbin

Continue to check the version of pgpool after successful installation with the following command:

pgpool --version

Step 2: Configure PGpool-II

Create a configuration directory for PGpool-II:

sudo mkdir /etc/pgpool2

Copy from sample config :

sudo cp /home/pgpool2/etc/pgpool.conf.sample /etc/pgpool2/pgpool.conf 
sudo cp /home/pgpool2/etc/pool_hba.conf.sample /etc/pgpool2/pool_hba.conf 
sudo cp /home/pgpool2/etc/pcp.conf.sample /etc/pgpool2/pcp.conf

Step 3: Configure Connection Management

Next, we will configure connection management by editing the configuration file /etc/pgpool2/pool_hba.conf:

sudo nano /etc/pgpool2/pool_hba.conf

Add the following configuration to the file:

host    all         all         0.0.0.0/0          trust

Step 4: Start PGpool-II

Create a directory to store the pid file:

sudo mkdir /var/run/pgpool

Finally, restart the PGpool-II service to apply the changes:

sudo /usr/sbin/pgpool -n -f /etc/pgpool2/pgpool.conf -F /etc/pgpool2/pcp.conf

Step 5: Configure pcp.conf

Pgpool-II provides an interface for administrators to perform management operations, such as viewing the status of Pgpool-II or shutting down Pgpool-II processes remotely. pcp.conf is the file that contains the user/password used for authentication for this interface. All operating modes require the pcp.conf file to be set up.

Create the pcp.conf file:

password_hash=$(pg_md5 Ehi@123)
echo "pgpool:$password_hash" >> /etc/pgpool2/pcp.conf

Where Ehi@123 is the password for the pgpool account, you can change the password as desired.

Then, add access permissions to the pcp.conf file:

sudo chown pgpool:pgpool /etc/pgpool2/pcp.conf
sudo chmod 0600 /etc/pgpool2/pcp.conf

Step 6: Create PGpool-II Service

Create a systemd service file pgpool2.service on the pgpool2 server:

sudo nano /etc/systemd/system/pgpool2.service

Add the following content to the pgpool2.service file:

Description=pgpool-II
Documentation=man:pgpool(8)
Wants=postgresql.service
After=network.target



[Service]
User=root
ExecStart=/usr/sbin/pgpool -n -f /etc/pgpool2/pgpool.conf -F /etc/pgpool2/pcp.conf
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
StandardOutput=syslog
SyslogFacility=local0

[Install]
WantedBy=multi-user.target

/usr/sbin/pgpool -n -f /etc/pgpool2/pgpool.conf -F /etc/pgpool2/pcp.conf -m smart trong đó :

• -n : Không chạy dưới dạng daemon
• -f : Đường dẫn đến tệp cấu hình pgpool.conf
• -F : Đường dẫn đến tệp cấu hình quản lý pcp.conf
• -m : Chế độ hoạt động của Pgpool-II. Có 3 chế độ hoạt động:
  • fast : Chế độ hoạt động nhanh
  • smart : Chế độ hoạt động thông minh, mặc định

Automatically enable at system startup:

sudo systemctl enable pgpool2

Restart the PGpool-II service:

sudo systemctl start pgpool2

Thus, we have installed and configured PGpool-II on the Ubuntu Linux operating system. By performing these steps, you can optimize and expand the capabilities of your PostgreSQL database, helping you maximize the performance and high availability of your database.