All HowTo's PostgreSQL

Splitting PostgreSQL Read and Write Queries to Different Database Servers with PGPool2

There are times when we need to send read/write queries to one server, and reads to another server (or multiple servers). If we control the application (the client), we can easily do this by coding such that different queries go to different database servers. However, what if we don’t control the application.

Enter “pgpool2”. In this tutorial, we’re going to send all client queries to the pgpool2 server which then splits and forward the queries to the appropriate database server.

This article starts where this ( ends. Follow that tutorial to get a working “primary / secondary” replication configuration working. Once that’s working, we can continue. Or if you have a replica set already, continue on. Remember this is a lab setup and we’re not working with real data. Make sure you have backups, test your work, and make sure you’re not risking real data.

We’re starting with two servers “primary” and “secondary”. They’re both working in a replica configuration. The primary is read/write, and the secondary is read-only.

On the primary database server (SQL statement). Obviously select a better password for this user:


IMPORTANT: The above is to allow pgpool2 to monitor the databases. The actual credentials that you (the client) connects to pgpool2 on is forwarded to the back-end database servers.

On the primary and secondary database servers, make sure the “pgpool2” server can connect to them. I’m allowing a /24 but you might want to allow only the specific IP address of the “pgpool2” server. We’re editing the “/etc/postgresql/14/main/pg_hba.conf” configuration file.

host    all     all        md5

And reload Postgresql on both servers. I’m unsure if a “restart” or “reload” is required:

systemctl restart postgresql

We have a third server called “pgpool2” which is what we’re working on from this point forward. It’s a Ubuntu 22.04 server.

apt update
apt install pgpool2
systemctl enable pgpool2
systemctl restart pgpool2

Edit the file:

## Connection Details
socket_dir = '/var/run/postgresql'

## Backend Server Details

# Read/Write server.
# Primary (set to your primary database server IP/DNS name)
backend_hostname0 = ''
backend_port0 = 5432
backend_weight0 = 1

# Read-only server.
# Secondary (set to your secondary database server IP/DNS name)
backend_hostname1 = ''
backend_port1 = 5432
backend_weight1 = 1

## Load Balancing
load_balance_mode = on

## Replication Responsibility
master_slave_mode = on
master_slave_sub_mode = 'stream'

## Streaming checks
sr_check_period = 10
sr_check_user = 'monitor'
sr_check_password = 'mypass'
sr_check_database = 'monitor'
delay_threshold = 10240

## Client Authentication
allow_clear_text_frontend_auth = on

Restart “pgpool2” and test that it’s listening:

systemctl restart pgpool2
netstat -nlp | grep :5432

Now you should have a working pgpool2 configuration. Anything connecting to that (pgpool2) is forwarded onto the back-end database servers.



Leave a Reply

Your email address will not be published. Required fields are marked *