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 (https://agix.com.au/install-and-configure-postgresql-stream-replication/) 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:
CREATE USER monitor WITH ENCRYPTED PASSWORD 'mypass'; CREATE DATABASE monitor;
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 10.0.0.0/24 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:
/etc/pgpool2/pgpool.conf
# https://mydbops.wordpress.com/2021/01/20/pgpool-ii-installation-configuration-part-i/ ## Connection Details listen_addresses='*' port=5432 socket_dir = '/var/run/postgresql' ## Backend Server Details # Read/Write server. # Primary (set to your primary database server IP/DNS name) backend_hostname0 = '10.0.0.148' backend_port0 = 5432 backend_weight0 = 1 # Read-only server. # Secondary (set to your secondary database server IP/DNS name) backend_hostname1 = '10.0.0.147' 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.
Resources:
- https://www.pgpool.net/docs/latest/en/html/index.html
- https://mydbops.wordpress.com/2021/01/20/pgpool-ii-installation-configuration-part-i/
