Pgpool-II is a middle ware that sits between PostgreSQL servers and a PostgreSQL database client. It provides the following features: Connection Pooling ,Replication ,Load Balance,Limiting Exceeding Connections , etc . Pgpool-II provides a set of child processes that maintain cached connections to one or more database servers.
Topology
The following is the topology we use:
1) A MacOS which using PSQL as the client to connect to the remote Pgpool service
2) A Ubuntu VM which installed Pgpool service
3) A BigAnimal Cluster
Steps
1) Install Pgpool on Ubuntu : apt-get install -y pgpool
( Can also Follow steps > EDB Pgpool Installation )
2) Update Pgpool configuration file -/etc/pgpool2/pgpool.conf
* Update details of your BigAnimal Cluster
backend_hostname0 = 'p-x9xtf4g2vy.xceslrpouz7uuo17.biganimal.io' (host details)
backend_port0 = 5432 (port no)
backend_weight0 = 1 (load balance ratio of the backend server)
backend_data_directory0 = '/var/lib/postgresql/data/pgdata' (Data Directory)
backend_flag0 = 'ALWAYS_PRIMARY' (depends on scenario , have options like ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER or ALWAYS_PRIMARY
allow_clear_text_frontend_auth = on (It allows the pgpool to get the password in plain text from the frontend and use it for backend authentication)
3) Restart the service
- systemctl restart pgpool2
-
systemctl status pgpool2
4) Check the connection
- Connect DB from PGPool Server to PG Cluster Via PGPool.
- Connecting DB from personal m\c to PG Cluster via PGPool (Add inbound port rule for 9999 in Azure networking settings to connect via personal Mac )
