When you design a scale, high availability, high performance system, I sure that you have to apply Replication, Sharding and Load Balance technologies for your database layer. In this post, I will show you the way how to setup a load balance with PgPool2 for your PostgreSQL Master-Slave Replication.
My scenario as image below:
Step 1: Install PostgreSQL Replication for your servers
At first, Please refer to 2 posts:
After that, install Pgpool Lib:
|
1 |
sudo apt-get install postgresql-9.4-pgpool2 |
Step 2, Install PgPool2 for server 192.168.1.3
|
1 |
sudo apt-get install pgpool2 |
Edit /etc/pgpool2/pgpool.conf as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# - Backend Connection Settings - backend_hostname0 = '192.168.1.4' # Host name or IP address to connect to for backend 0 backend_port0 = 5432 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/var/lib/postgresql/9.4/main/' # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER backend_hostname1 = '192.168.1.5' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/9.4/main/' backend_flag1 = 'ALLOW_TO_FAILOVER' # - Authentication - enable_pool_hba = on |
Make sure you set enable_pool_hba = on
And change /etc/pgpool2/pool_hba.conf to enable md5 for authentication:
|
1 |
host all all 192.168.1.0/24 md5 |
Add account to /etc/pgpool2/pool_passwd.conf (just a example)
|
1 |
postgres:md5xxxxxxxxxxxxxxxxxxxxxxxxxxx |
Now, run pgpool with log:
|
1 |
pgpool -n -d > /tmp/pgpool.log 2>&1 & |
And stop Pgpool if you need:
|
1 |
pgpool stop |
Finally, test your installation. Good Luck!
