Load Balancing PostgreSQL Cluster with Repmgr and Pg-Pool-II

In the previous post, I have demonstrate how to create PostgreSQL-12 replication server. I will work well for distribute the connection to both master and slave. But problem is when you create a resource to connect to both databases you need to create two data sources separately which is not realistic the practical application. You need to access to single point of the resource and your connection will be distributed automatically. You don't need to manage connection to the datasource manually. Postgresql provides the solution for this problem by Pg-Pool-II which you have multiple databases (master / slave). You can access to the datasource from one single point of access. Pg-Pool will handle distribution of the connection automatically. So, in this post I will show you how to use Pg-Pool-II to handle request to your database cluster automatically. 

Installing and SetUp Master/Slave Replication

First, to create PostgreSQL-Pooling we need to create Master/Slave Replication.

Fig 02 Environment Set Up

For my set up, I have use below environment.

  • Pg pool II server: pgpool00.itstikk.pro (PgPool-II 9.4), IP : 66.55.64.14
  • Database Master: db01.itstikk.pro (PostgreSQL 9.4), IP : 66.55.64.137
  • Database Slave: db02.itstikk.pro (PostgreSQL 9.4), IP : 66.55.64.125

Before installing any package to the servers, we need to create key pair to facilitate login to each server.

Create Key pairs for server

[root@pgpool00 ~]#ssh-keygen -t rsa
[root@db01 ~]#ssh-keygen -t rsa
[root@db02 ~]#ssh-keygen -t rsa

Send created public key to each server. First on pgpool server(pgpool00.itstikk.pro).

[root@pgpool00 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db01.itstikk.pro
[root@pgpool00 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db02.itstikk.pro

Secondly, it's master database (db01.itstikk.pro)

[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgpool00.itstikk.pro
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db02.itstikk.pro

And for standby (db02.itstikk.pro)

[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@pgpool00.itstikk.pro
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db01.itstikk.pro

Install PostgreSQL94 repo for CentOS7

Next is install the repo file of PostgreSQL on each Server

[root@pgpool00 ~]# yum install -y https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm
[root@db01 ~]# yum install -y https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm
[root@db02 ~]# yum install -y https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm

Add postgres users and groups in advance to facilitate subsequent operations. First, for the pgpool server (pgpool00.itstikk.pro)

Create Postgres user and home directory

[root@pgpool00 ~]# mkdir /var/lib/pgsql
[root@pgpool00 ~]# groupadd -r -g 26 postgres
[root@pgpool00 ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
[root@pgpool00 ~]# passwd postgres
[root@pgpool00 ~]# chown postgres:postgres -R /var/lib/pgsql/

Next is master server (db01.itstikk.pro)

[root@db01 ~]# mkdir /var/lib/pgsql
[root@db01 ~]# groupadd -r -g 26 postgres
[root@db01 ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
[root@db01 ~]# passwd postgres
[root@db01 ~]# chown postgres:postgres -R /var/lib/pgsql/

And for standby server (db02.itstikk.pro)

[root@db02 ~]# mkdir /var/lib/pgsql
[root@db02 ~]# groupadd -r -g 26 postgres
[root@db02 ~]# useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres
[root@db02 ~]# passwd postgres
[root@db02 ~]# chown postgres:postgres -R /var/lib/pgsql/

Create Key pairs for Postgres user

First, access postgres account and generate key pair for pgpool (pgpool00.itstikk.pro).

[root@pgpool00 ~]# su - postgres
-bash-4.2$ ssh-keygen -t rsa
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@db01.itstikk.pro
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@db02.itstikk.pro

Next is db01 (db01.itstikk.pro)

[root@db01 ~]# su - postgres
-bash-4.2$ ssh-keygen -t rsa
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgpool00.itstikk.pro
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@db02.itstikk.pro

And create key pair for db02 (db02.itstikk.pro)

[root@db02 ~]# su - postgres
-bash-4.2$ ssh-keygen -t rsa
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@db01.itstikk.pro
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub postgres@pgpool00.itstikk.pro

Install Postgres94 and Set Up Master Database

Let start to create replication server by installing PostgreSQL to Master Database Server. So, first you need to install repository for yum package as below.

[root@db01 ~]#yum -y install postgresql94 postgresql94-server postgresql94-contrib rsync

Following is to install replication management package. The repmgr package is used to monitor the Replication action of PostgreSQL Server to other PostgreSQL Server!

[root@db01 ~]#yum -y install https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/repmgr94-4.3.0-1.rhel7.x86_64.rpm

Next is initiated database setup and configure database connection.

[root@db01 ~]#/usr/pgsql-9.4/bin/postgresql94-setup initdb
[root@db01 ~]#vim /var/lib/pgsql/9.4/data/pg_hba.conf

And modify configuration as following.

local    all         all                             trust
host     all         all         127.0.0.1/32        md5
host     all         all         ::1/128             md5
host     repmgr      repmgr      66.55.64.137/32     trust
host     replication repmgr      66.55.64.137/32     trust
host     repmgr      repmgr      66.55.64.125/32     trust
host     replication repmgr      66.55.64.125/32     trust
host     all         pgpool      66.55.64.14/32      trust
host     all         all         66.55.64.14/32      md5

configure Postgres as following setup

[root@db01 ~]#vim /var/lib/pgsql/9.4/data/postgresql.conf

Edit configuration as bellow.

listen_addresses = '*'
max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 2621kB
maintenance_work_mem = 128MB
default_statistics_target = 100
shared_preload_libraries = 'repmgr_funcs'
wal_level = hot_standby
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
wal_keep_segments = 5000
wal_sender_timeout = 1s
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_lock_waits = on
log_statement = 'all'
log_temp_files = 0
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

Next is to set up directory for repmgr.

[root@db01 ~]#su - postgres
$ mkdir -p /var/lib/pgsql/repmgr/

Then Edit the remggr configuration file:

[root@db01 ~]#vim /var/lib/pgsql/repmgr/repmgr.conf

Edit file as below

node_id=01
node_name=db01
conninfo='host=db01.itstikk.pro user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.4/data'
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.4/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'
follow_command='/usr/pgsql-9.4/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'

Set up firewall and start PostgreSQL

[root@db01 ~]#firewall-cmd --permanent --add-service=postgresql
[root@db01 ~]#firewall-cmd --reload
[root@db01 ~]#systemctl enable postgresql-9.4
[root@db01 ~]#systemctl start postgresql-9.4

Create Replication and rempgr users, and rempgr database and register db01 as master node of the cluster.

#su - postgres
$ psql
postgres=# CREATE ROLE pgpool SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION LOGIN ENCRYPTED PASSWORD 'secret';
postgres=# CREATE USER repmgr SUPERUSER LOGIN ENCRYPTED PASSWORD 'secret';
postgres=# CREATE DATABASE repmgr OWNER repmgr;
postgres=# \q
$ /usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf master register

Set Up Slave

After we create master database server. We need to create slave for replication. But first we need to install PostgreSQL94 and related packages for slave server.

[root@db02 ~]#yum -y install postgresql94 postgresql94-server postgresql94-contrib rsync

Next is to install repmgr package to slave server

[root@db02 ~]#yum -y install https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/repmgr94-4.3.0-1.rhel7.x86_64.rpm

Synchronize all configuration with master server, but first create repmgr configuration file.

[root@db02 ~]#su - postgres
-bash-4.2$ vi /var/lib/pgsql/repmgr/repmgr.conf
node_id=02
node_name=db02
conninfo='host=db02.itstikk.pro user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/9.4/data'
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.4/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'
follow_command='/usr/pgsql-9.4/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'

Then synchronize configuration with master database

-bash-4.2$ /usr/pgsql-9.4/bin/repmgr -h db01.itstikk.pro -f /var/lib/pgsql/repmgr/repmgr.conf -d repmgr -p 5432 -U repmgr -R postgres standby clone

Create a directory for repmgr.

-bash-4.2$ mkdir -p /var/lib/pgsql/repmgr/

Allow port on firewall and start server

[root@db02 ~]#firewall-cmd --permanent --add-service=postgresql
[root@db02 ~]#firewall-cmd --reload
[root@db02 ~]#systemctl enable postgresql-9.4
[root@db02 ~]#systemctl start postgresql-9.4

Register db02 as cluster node

[root@db02 ~]#su - postgres
-bash-4.2$/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby register.

Master/Slave Replication Operation Confirm

After create master and slave database, we need to test performance of the replication. To test how master/slave work, first we query rempgr on the Standby Server

[root@db02 ~]#su - postgres
-bash-4.2$ /usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show
ID  | Name | Role    | Status    | Upstream | Location | Priority | Connection string
----+------+---------+-----------+----------+----------+----------+-------------------------------------------------
1   | db01 | primary | * running |          | default  | 100      | host=db01.itstikk.pro user=repmgr dbname=repmgr
2   | db02 | standby |   running | db01     | default  | 100      | host=db02.itstikk.pro user=repmgr dbname=repmgr

Query the PostgreSQL Database on the Standby Server

[root@db02 ~]#su - postgres
-bash-4.2$ psql -U postgres -c "\list"
                         List of databases
Name       | Owner    | Encoding  | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
postgres   | postgres | SQL_ASCII | C       | C     |
pro        | pgpool   | SQL_ASCII | C       | C     |
repmgr     | repmgr   | SQL_ASCII | C       | C     |
template0  | postgres | SQL_ASCII | C       | C     | =c/postgres +
           |          |           |         |       | postgres=CTc/postgres
template1  | postgres | SQL_ASCII | C       | C     | =c/postgres +
           |          |           |         |       | postgres=CTc/postgres

Create a test database on the Master Server:

[root@db01 ~]#su - postgres
-bash-4.2$ psql -U postgres -c "CREATE DATABASE test"

Then check database in slave server

[root@db02 ~]#su - postgres
-bash-4.2$ psql -U postgres -c "\list"
                         List of databases
Name       | Owner    | Encoding  | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
postgres   | postgres | SQL_ASCII | C       | C     |
pro        | pgpool   | SQL_ASCII | C       | C     |
repmgr     | repmgr   | SQL_ASCII | C       | C     |
template0  | postgres | SQL_ASCII | C       | C     | =c/postgres +
           |          |           |         |       | postgres=CTc/postgres
template1  | postgres | SQL_ASCII | C       | C     | =c/postgres +
           |          |           |         |       | postgres=CTc/postgres
test       | postgres | SQL_ASCII | C       | C     |

new database has been replicated to slave server. Remember that you can write only on master, if your try to write on slave, slave server will throw error as below.

[root@db02 ~]#su - postgres
-bash-4.2$ psql -U postgres -c "CREATE DATABASE test2"
ERROR: cannot execute CREATE DATABASE in a read-only transaction

Installing and SetUp PgPool-II

Now,we install pgpool-II into pgpool00.itstikk.pro. First, we need to install postgreSQL94 and pgpool-II-94

[root@pgpool00 ~]#yum install postgresql94 pgpool-II-94

create pgpool configuration file.

[root@pgpool00 ~]#cp /etc/pgpool-II-94/pgpool.conf.sample-stream /etc/pgpool-II-94/pgpool.conf
[root@pgpool00 ~]#vi /etc/pgpool-II-94/pgpool.conf

configure as below.

listen_addresses = '*'
port = 5432
backend_hostname0 = 'db01.itstikk.pro'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.4/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'db02.itstikk.pro'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.4/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pid_file_name = '/var/run/pgpool-II-94/pgpool.pid'
sr_check_user = 'pgpool'
sr_check_password = 'secret'
health_check_period = 10
health_check_user = 'pgpool'
health_check_password = 'secret'
failover_command = '/etc/pgpool-II-94/failover.sh %d %H'
recovery_user = 'pgpool'
recovery_password = 'secret'
recovery_1st_stage_command = 'basebackup.sh'

Create the failover.sh scripts file

[root@pgpool00 ~]#vi /etc/pgpool-II-94/failover.sh
#!/bin/sh
failed_node=$1
new_master=$2
(
date
echo "Failed node: $failed_node"
set -x
/usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

Then change execution mode of the script file.

[root@pgpool00 ~]#chmod 755 /etc/pgpool-II-94/failover.sh

Edit the pool_hba.conf file

[root@pgpool00 ~]#vi /etc/pgpool-II-94/pool_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

Create the pool_passwd file

[root@pgpool00 ~]#touch /etc/pgpool-II-94/pool_passwd
[root@pgpool00 ~]#chown postgres:postgres /etc/pgpool-II-94/pool_passwd
[root@pgpool00 ~]#su - postgres
-bash-4.2$pg_md5 -m -u pgpool secret
-bash-4.2$exit
[root@pgpool00 ~]#echo "pgpool:$(pg_md5 secret)"| tee /etc/pgpool-II-94/pcp.conf

Extend share memory as below

[root@pgpool ~]# vi /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

kernel.shmmax = 104857600

Next is read the configuraton

[root@pgpool ~]# sysctl -p

Finally, open port and firewall and start service

[root@pgpool ~]#firewall-cmd --permanent --add-service=postgresql
[root@pgpool ~]#firewall-cmd --reload
[root@pgpool ~]#systemctl enable pgpool-II-94
[root@pgpool ~]#systemctl start pgpool-II-94

Create Connection from client to Cluster

Connection pgpool-II-94 with DBeaver as below.

Fig 03 Connection to pgpool-II-94

Fig 05 Testing Configuration


Reference

Please find my reference for the resource of this post below.

  1. Comparisoin of Pg-Pool and PgBouncer
  2. Setup a redundant PostgreSQL database with repmgr and pgpool
  3. Creating a pgpool-II based PostgreSQL Cluster

No comments:

Post a Comment

Subscribe to: Posts (Atom)

Feature Recently

Running Wildfly Application Server in Domain Mode

  Wildfly application server provides two modes of how to run application one wildfly application server. It is very simple if you run your ...

Most Views