<\/p>\n
Master-Slave mode: <\/strong><\/h3>\n <\/p>\n
Pgpool-II load balancing of select queries works with Master-Slave mode to distribute the read load on the standby backend nodes, when load balancing is enabled with Master-Slave mode Pgpool-II sends the writing queries to the primary node in Master Slave mode and read queries to other nodes.<\/p>\n
<\/p>\n
You can read more about it here.<\/a><\/p>\n <\/p>\n
Now, Let\u2019s start with the installation:<\/strong><\/p>\n <\/p>\n
Prepare the RDS instances:<\/b><\/h2>\n
<\/p>\n
Writable instance: <\/strong>Start a new RDS instance.<\/p>\nFor this tutorial we’ll be using t2.micro instance with postgresql version 11.5r1 (latest supported postgresql version).<\/p>\n
<\/p>\n
Note: During the creation of DB instance Backup retention for DB instance must be enabled.<\/strong><\/p>\n <\/p>\n
All the DDL (Data definition language) queries will be directed to writable RDS instance by pgpool.<\/p>\n
<\/p>\n
Readable<\/strong> Replica:<\/strong> After the instance is in available state, select the instance and click on the actions menu to select the create read replica<\/strong> option as\u00a0 a result a read replica instance will be created.<\/p>\n <\/p>\n
Prepare the EC2 instance:<\/strong><\/h2>\n <\/p>\n
For the sake of this tutorial, we’ll be using a t2.micro instance with Ubuntu 18.04 LTS operating system.<\/p>\n
<\/p>\n
Installing Pgpool-II:<\/strong><\/h3>\n <\/p>\n
We’ll be installing pgpool-II version 4.1.0 (karasukiboshi)<\/p>\n
<\/p>\n
Step 1<\/strong>: Update the existing packages of distribution
\n<\/strong><\/h4>\n <\/p>\n
sudo apt update<\/pre>\n <\/p>\n
Step 2: <\/strong>Add postgresql repository<\/h4>\n <\/p>\n
echo \"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt\/ `lsb_release -cs`-pgdg main\" \\\r\n|sudo tee \/etc\/apt\/sources.list.d\/pgdg.list<\/pre>\n <\/p>\n
We need to import GPG key and add PostgreSQL repository into our Ubuntu instance.<\/p>\n
<\/p>\n
wget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -<\/pre>\n <\/p>\n
Repository has been added successfully,\u00a0 hence update the package list and install pgpool2.<\/p>\n
<\/p>\n
sudo apt update\r\nsudo apt install -y pgpool2<\/pre>\n <\/p>\n
Therefore, to start pgpool2 use the pgpool2 systemd unit file.<\/p>\n
<\/p>\n
sudo systemctl start pgpool2<\/pre>\n <\/p>\n
Similarly, to stop<\/p>\n
<\/p>\n
sudo systemctl stop pgpool2<\/pre>\n <\/p>\n
Step 3: <\/strong>Configuring pgpool2<\/h4>\n <\/p>\n
sudo vim \/etc\/pgpool2\/pgpool.conf<\/pre>\n <\/p>\n
Most of the configuration of pgpool is already done in file \/etc\/pgpool2\/pgpool.conf as a result only some parameters are required to change.<\/p>\n
The table below shows the important ones.<\/p>\n
<\/p>\n
\n\n \n\nlisten_addresses<\/strong><\/td>\n\u2018*\u2019<\/td>\n<\/tr>\n \nbackend_hostname0<\/strong><\/td>\nAmazon RDS postgresql writeable endpoint.<\/td>\n<\/tr>\n \nbackend_port0<\/strong><\/td>\n5432<\/td>\n<\/tr>\n \nbackend_flag0<\/strong><\/td>\nALWAYS_MASTER<\/td>\n<\/tr>\n \nbackend_weight0<\/strong><\/td>\n1<\/td>\n<\/tr>\n \nbackend_hostname1<\/strong><\/td>\nAmazon RDS postgresql read replica endpoint.<\/td>\n<\/tr>\n \nbackend_port1<\/strong><\/td>\n5432<\/td>\n<\/tr>\n \nbackend_weight1<\/strong><\/td>\n2<\/td>\n<\/tr>\n \nenable_pool_hba<\/strong><\/td>\non<\/td>\n<\/tr>\n \npool_passwd<\/strong><\/td>\n\u2018pool_passwd\u2019<\/td>\n<\/tr>\n \nreplication_mode<\/strong><\/td>\noff<\/td>\n<\/tr>\n \nload_balance_mode<\/strong><\/td>\non<\/td>\n<\/tr>\n \nmaster_slave_mode<\/strong><\/td>\non<\/td>\n<\/tr>\n \nmaster_slave_sub_mode<\/strong><\/td>\n\u2018stream\u2019<\/td>\n<\/tr>\n \nsr_check_period<\/strong><\/td>\n0<\/td>\n<\/tr>\n \nhealth_check_period<\/strong><\/td>\n5<\/td>\n<\/tr>\n \nhealth_check_user<\/strong><\/td>\npostgres<\/td>\n<\/tr>\n \nhealth_check_password<\/strong><\/td>\nPassword for postgres user<\/td>\n<\/tr>\n \nhealth_check_max_retries<\/strong><\/td>\n20<\/td>\n<\/tr>\n \nhealth_check_retry_delay<\/strong><\/td>\n1<\/td>\n<\/tr>\n \nfailover_on_backend_error<\/strong><\/td>\noff<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
Step 4<\/strong>: Creating the pool_passwd file:<\/h4>\n <\/p>\n
Above all, We’ll be using pg_md5 command line utility to generate the pool_passwd file here’s the syntax of the command:<\/p>\n
<\/p>\n
pg_md5 --md5auth --username=username password<\/pre>\n <\/p>\n
Here, replace username with your postgresql username and also replace password with your own postgresql password.<\/p>\n
<\/p>\n
Step 5: <\/strong>In addition to this, we’ll check if both write instance and read replica are recognized by pgpool:<\/h4>\n <\/p>\n
cat \/var\/log\/postgresql\/pgpool_status<\/pre>\n <\/p>\n
Note: The file would show ‘up’ for both the instances, as a result traffic on both these instances will be distributed.
\n<\/strong><\/p>\n <\/p>\n
Testing the installation:<\/h2>\n
<\/p>\n
Furthermore, To test the installation enable the log parameters in<\/p>\n
\/etc\/pgpool2\/pgpool.conf<\/em> file:<\/p>\n <\/p>\n
\n\n \n\nlog_destination<\/strong><\/td>\n\u2018syslog\u2019<\/td>\n<\/tr>\n \nlog_statement<\/strong><\/td>\non<\/td>\n<\/tr>\n \nlog_per_node_statement<\/strong><\/td>\non<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
In addition, Restart pgpool:<\/p>\n
sudo systemctl restart pgpool2<\/pre>\nInstall postgresql client:<\/p>\n
sudo apt install postgresql-client<\/pre>\nFurthermore, Connecting to postgresql server via pgpool<\/p>\n
psql -h localhost -p 5432 -U postgres -W<\/pre>\nIn addition, Enter password in password prompt<\/p>\n
Also, in the second terminal monitor the syslog<\/p>\n
tail -f \/var\/log\/syslog<\/pre>\nIn another terminal where psql command is executed.<\/p>\n
Create a new database, new table and insert some data into it<\/p>\n
CREATE DATABASE TEST;<\/pre>\nAbove all, Connect to the newly created database:<\/p>\n
\\c TEST<\/pre>\nCreate new table:<\/p>\n
CREATE TABLE TEST_TABLE(VAL VARCHAR(10));<\/pre>\nLikewise, insert value in the table<\/p>\n
INSERT INTO TEST_TABLE VALUES('DATA');<\/pre>\nFinally, read data from table<\/p>\n
SELECT * FROM TEST_TABLE;<\/pre>\n <\/p>\n
<\/p>\n
Now, Let\u2019s start with the installation:<\/strong><\/p>\n <\/p>\n <\/p>\n Writable instance: <\/strong>Start a new RDS instance.<\/p>\n For this tutorial we’ll be using t2.micro instance with postgresql version 11.5r1 (latest supported postgresql version).<\/p>\n <\/p>\n Note: During the creation of DB instance Backup retention for DB instance must be enabled.<\/strong><\/p>\n <\/p>\n All the DDL (Data definition language) queries will be directed to writable RDS instance by pgpool.<\/p>\n <\/p>\n Readable<\/strong> Replica:<\/strong> After the instance is in available state, select the instance and click on the actions menu to select the create read replica<\/strong> option as\u00a0 a result a read replica instance will be created.<\/p>\n <\/p>\n <\/p>\n For the sake of this tutorial, we’ll be using a t2.micro instance with Ubuntu 18.04 LTS operating system.<\/p>\n <\/p>\n <\/p>\n We’ll be installing pgpool-II version 4.1.0 (karasukiboshi)<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n We need to import GPG key and add PostgreSQL repository into our Ubuntu instance.<\/p>\n <\/p>\n <\/p>\n Repository has been added successfully,\u00a0 hence update the package list and install pgpool2.<\/p>\n <\/p>\n <\/p>\n Therefore, to start pgpool2 use the pgpool2 systemd unit file.<\/p>\n <\/p>\n <\/p>\n Similarly, to stop<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n Most of the configuration of pgpool is already done in file \/etc\/pgpool2\/pgpool.conf as a result only some parameters are required to change.<\/p>\n The table below shows the important ones.<\/p>\n <\/p>\n <\/p>\n <\/p>\n Above all, We’ll be using pg_md5 command line utility to generate the pool_passwd file here’s the syntax of the command:<\/p>\n <\/p>\n <\/p>\n Here, replace username with your postgresql username and also replace password with your own postgresql password.<\/p>\n <\/p>\n <\/p>\n <\/p>\n Note: The file would show ‘up’ for both the instances, as a result traffic on both these instances will be distributed. <\/p>\n <\/p>\n Furthermore, To test the installation enable the log parameters in<\/p>\n \/etc\/pgpool2\/pgpool.conf<\/em> file:<\/p>\n <\/p>\n <\/p>\n In addition, Restart pgpool:<\/p>\n Install postgresql client:<\/p>\n Furthermore, Connecting to postgresql server via pgpool<\/p>\n In addition, Enter password in password prompt<\/p>\n Also, in the second terminal monitor the syslog<\/p>\n In another terminal where psql command is executed.<\/p>\n Create a new database, new table and insert some data into it<\/p>\n Above all, Connect to the newly created database:<\/p>\n Create new table:<\/p>\n Likewise, insert value in the table<\/p>\n Finally, read data from table<\/p>\n <\/p>\nPrepare the RDS instances:<\/b><\/h2>\n
Prepare the EC2 instance:<\/strong><\/h2>\n
Installing Pgpool-II:<\/strong><\/h3>\n
Step 1<\/strong>: Update the existing packages of distribution
\n<\/strong><\/h4>\nsudo apt update<\/pre>\n
Step 2: <\/strong>Add postgresql repository<\/h4>\n
echo \"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt\/ `lsb_release -cs`-pgdg main\" \\\r\n|sudo tee \/etc\/apt\/sources.list.d\/pgdg.list<\/pre>\n
wget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -<\/pre>\n
sudo apt update\r\nsudo apt install -y pgpool2<\/pre>\n
sudo systemctl start pgpool2<\/pre>\n
sudo systemctl stop pgpool2<\/pre>\n
Step 3: <\/strong>Configuring pgpool2<\/h4>\n
sudo vim \/etc\/pgpool2\/pgpool.conf<\/pre>\n
\n
\n listen_addresses<\/strong><\/td>\n \u2018*\u2019<\/td>\n<\/tr>\n \n backend_hostname0<\/strong><\/td>\n Amazon RDS postgresql writeable endpoint.<\/td>\n<\/tr>\n \n backend_port0<\/strong><\/td>\n 5432<\/td>\n<\/tr>\n \n backend_flag0<\/strong><\/td>\n ALWAYS_MASTER<\/td>\n<\/tr>\n \n backend_weight0<\/strong><\/td>\n 1<\/td>\n<\/tr>\n \n backend_hostname1<\/strong><\/td>\n Amazon RDS postgresql read replica endpoint.<\/td>\n<\/tr>\n \n backend_port1<\/strong><\/td>\n 5432<\/td>\n<\/tr>\n \n backend_weight1<\/strong><\/td>\n 2<\/td>\n<\/tr>\n \n enable_pool_hba<\/strong><\/td>\n on<\/td>\n<\/tr>\n \n pool_passwd<\/strong><\/td>\n \u2018pool_passwd\u2019<\/td>\n<\/tr>\n \n replication_mode<\/strong><\/td>\n off<\/td>\n<\/tr>\n \n load_balance_mode<\/strong><\/td>\n on<\/td>\n<\/tr>\n \n master_slave_mode<\/strong><\/td>\n on<\/td>\n<\/tr>\n \n master_slave_sub_mode<\/strong><\/td>\n \u2018stream\u2019<\/td>\n<\/tr>\n \n sr_check_period<\/strong><\/td>\n 0<\/td>\n<\/tr>\n \n health_check_period<\/strong><\/td>\n 5<\/td>\n<\/tr>\n \n health_check_user<\/strong><\/td>\n postgres<\/td>\n<\/tr>\n \n health_check_password<\/strong><\/td>\n Password for postgres user<\/td>\n<\/tr>\n \n health_check_max_retries<\/strong><\/td>\n 20<\/td>\n<\/tr>\n \n health_check_retry_delay<\/strong><\/td>\n 1<\/td>\n<\/tr>\n \n failover_on_backend_error<\/strong><\/td>\n off<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Step 4<\/strong>: Creating the pool_passwd file:<\/h4>\n
pg_md5 --md5auth --username=username password<\/pre>\n
Step 5: <\/strong>In addition to this, we’ll check if both write instance and read replica are recognized by pgpool:<\/h4>\n
cat \/var\/log\/postgresql\/pgpool_status<\/pre>\n
\n<\/strong><\/p>\nTesting the installation:<\/h2>\n
\n
\n log_destination<\/strong><\/td>\n \u2018syslog\u2019<\/td>\n<\/tr>\n \n log_statement<\/strong><\/td>\n on<\/td>\n<\/tr>\n \n log_per_node_statement<\/strong><\/td>\n on<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n sudo systemctl restart pgpool2<\/pre>\n
sudo apt install postgresql-client<\/pre>\n
psql -h localhost -p 5432 -U postgres -W<\/pre>\n
tail -f \/var\/log\/syslog<\/pre>\n
CREATE DATABASE TEST;<\/pre>\n
\\c TEST<\/pre>\n
CREATE TABLE TEST_TABLE(VAL VARCHAR(10));<\/pre>\n
INSERT INTO TEST_TABLE VALUES('DATA');<\/pre>\nSELECT * FROM TEST_TABLE;<\/pre>\n
![\"\"](\"https:\/\/cloudkul.com\/blog\/wp-content\/uploads\/2020\/01\/SC2-300x97.png\")
<\/a><\/p>\n