Installing Postgresql 10 with repmgr and pgpool-II

repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers. It enhances PostgreSQL’s built-in hot-standby capabilities with tools to set up standby servers, monitor replication, and perform administrative tasks such as failover or manual switchover operations.

Berikut daftar server yang harus disiapkan :

  • Master DB : 10.200.200.10
  • Standby DB : 10.200.200.20
  • pgpool2 : 10.200.200.30

pada master dan standby server, install postgresql 10 dan repmgr

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-10

sudo sh -c 'echo "deb https://apt.2ndquadrant.com/ $(lsb_release -cs)-2ndquadrant main" > /etc/apt/sources.list.d/2ndquadrant.list'
curl https://apt.2ndquadrant.com/site/keys/9904CD4BD6BAF0C3.asc | sudo apt-key add -
sudo apt-get update
apt-get install postgresql-10-repmgr

pada master dan standby server, buat user untuk repmgr

sudo -i -u postgres
createuser --replication --createdb --createrole --superuser repmgr
psql -c 'ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;'
createdb repmgr --owner=repmgr

pada master dan standby server dan user postgres,  generate ssh-keygen dan masukan kedalam database lawannya :

ssh-keygen
# Paste ssh key id_rsa.pub on other servers
nano .ssh/authorized_keys

testing ssh

# On Master
ssh 10.200.200.20
# On Standby
ssh 10.200.200.10

edit postgres configuration pada master dan standby server

exit
cd /mnt
mkdir server
mkdir server/archivedir
cd ~
sudo nano /etc/postgresql/10/main/postgresql.conf

listen_addresses = *
shared_preload_libraries = 'repmgr'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
max_wal_senders = 5
wal_keep_segments = 64
max_replication_slots = 5
hot_standby = on

edit konektivitas postgresql pada master dan standby server

sudo nano /etc/postgresql/10/main/pg_hba.conf

host    repmgr             repmgr          10.200.200.10/32     trust
host    repmgr             repmgr          10.200.200.20/32     trust

host    replication        repmgr          10.200.200.10/32     trust
host    replication        repmgr          10.200.200.20/32     trust

restart postgresql pada master dan standby server

systemctl restart postgresql@10-main

pada master node :

sudo su postgres
psql 'host=10.200.200.20 dbname=repmgr user=repmgr'

pada standby node :

sudo su postgres
psql 'host=10.200.200.10 dbname=repmgr user=repmgr'

buat cluster configuration pada master node :

sudo nano /etc/repmgr.conf

node_id = 1
node_name = 'node1'
conninfo = 'host=10.200.200.10 user=repmgr dbname=repmgr'
data_directory='/var/lib/postgresql/10/main'
use_replication_slots = 1
reconnect_attempts=5
reconnect_interval=1
failover=automatic
pg_bindir='/usr/lib/postgresql/10/bin'
promote_command='repmgr standby promote -f /etc/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr.conf'
log_level=INFO
log_file='/var/log/postgresql/repmgr.log

buat cluster configuration pada standby node :

sudo nano /etc/repmgr.conf

node_id = 2
node_name = 'node2'
conninfo = 'host=10.200.200.20 user=repmgr dbname=repmgr'
data_directory='/var/lib/postgresql/10/main'
use_replication_slots = 1
reconnect_attempts=5
reconnect_interval=1
failover=automatic
pg_bindir='/usr/lib/postgresql/10/bin'
promote_command='repmgr standby promote -f /etc/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr.conf'
log_level=INFO
log_file='/var/log/postgresql/repmgr.log

allow automatic failover pada master dan standby node :

nano /etc/default/repmgrd

REPMGRD_ENABLED=yes
REPMGRD_CONF="/etc/repmgr.conf"

sudo service repmgrd restart

registrasi cluster pada master node :

su - postgres
repmgr primary register
repmgr cluster show

registrasi cluster pada standby node :

sudo service postgresql@10-main stop
sudo service repmgrd stop

sudo su postgres
rm -rf /var/lib/postgresql/10/main
repmgr -h 10.200.200.10 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone

exit
sudo service postgresql@10-main start

sudo su postgres
repmgr -f /etc/repmgr.conf standby register
repmgr cluster show

pada pgpool-ii node, instlal pgpool-ii :

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt-get update
# Check your os version before running the below command using lsb_relase -a
sudo apt-get install pgpool2=3.7.5-2.pgdg18.04+1

edit pgpool-ii configuration file pada pgpool-ii node :

sudo nano /etc/pgpool2/pgpool.conf

listen_addresses = '*'
port = 5432

backend_hostname0 = '10.200.200.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/10/main/'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.200.200.20'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/10/main/'
backend_flag1 = 'ALLOW_TO_FAILOVER'

load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_user = 'pgpool'
sr_check_database = 'pgpool'

health_check_period = 30
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = ''
health_check_database = 'pgpool'
health_check_max_retries = 5
health_check_retry_delay = 20
connect_timeout = 10000

buat pgpool user dan password pada master node

sudo su postgres
psql 
create user pgpool;
create database pgpool;
GRANT ALL PRIVILEGES ON DATABASE "pgpool" to pgpool;

edit konektivitas pada master dan standby node :

sudo nano /etc/postgresql/10/main/pg_hba.conf
host all all 0.0.0.0/0  password
host    pgpool             pgpool          10.200.200.30/32    trust

sudo service postgresql@10-main restart

lihat apakah cluster masih aktif pada master dan standby node :

su - postgres
repmgr cluster show

restart pgpool-ii node :

sudo service pgpool2 restart
sudo service pgpool2 status

Comments are closed.