Skip to main content

Cấu hình Replication Manager trên PostgreSQL

17,March 2018
Cấu hình replication postgresql sử dụng repmgr

repmgr (replication manager) là công cụ open-source sử dụng để quản lý việc replication và failover trong cluster PostgreSQL server. repmgr giúp đồng bộ data giữa master và slave, monitoring và thực hiện failover hay switchover giữa master slave  

I. Cài đặt

1. Cài đặt posgresql 9.6

- Thự hiện trên cả master & slave server db

+ Ubuntu 16

$ echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' >> /etc/apt/sources.list.d/pgdg.list
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install postgresql-10 postgresql-contrib-10 postgresql-client-10 python-psycopg2 postgresql-10-repmgr
$ systemctl enable postgresql
$ netstat -plntu

+ Change hostname trên repmgr_node1 (master)

# vim /etc/hosts
192.168.10.101  repmgr_node1.itlabvn.net repmgr_node1
192.168.10.102  repmgr_node2.itlabvn.net repmgr_node2

# vim /etc/hostname 
repmgr_node1.itlabvn.net

+ Change hostname trên repmgr_node2 (slave)

# vim /etc/hosts
192.168.10.101  repmgr_node1.itlabvn.net repmgr_node1
192.168.10.102  repmgr_node2.itlabvn.net repmgr_node2

# vim /etc/hostname 
repmgr_node2.itlabvn.net

 

2. Cấu hình truy cập SSH giữa master salve cho account "postgres"

$ sudo su postgres
$ ssh-keygen 

#Copy the publich key id_rsa.pub contents and add it to the authorized_keys file in the other host's postgres user ssh directory
postgres@repmgr_node1:~$ cat ~/.ssh/id_rsa.pub #paste postgres@repmgr_node2:~$ vim ~/.ssh/authorized_keys
postgres@repmgr_node2:~$ cat ~/.ssh/id_rsa.pub #paste postgres@repmgr_node1:~$ vim ~/.ssh/authorized_keys

#Testing ssh access 
postgres@repmgr_node1:~$ ssh repmgr_node2
postgres@repmgr_node2:~$ ssh repmgr_node1

 

II. Cấu hình

1. Cấu hình Databases và Users trên PostgreSQL master server - 192.168.10.101

- Chúng ta sẽ tạo 2 databases. 1 DB được sử dụng bởi repmgr và 1 db khác sẽ được sử dụng bởi application

+ For repmgr DB

$ createuser -s repmgr
$ createdb repmgr -O repmgr

+ For application DB

$ createuser test_user
$ createdb test_db -O test_user
$ sudo su postgres
postgres=# ALTER USER test_user WITH PASSWORD '12345678'; 

 

2. Cấu hình PostgreSQL replication - Thực hiện trên cả 2 node master slave

+ Cấu hình postgresql.conf

$ vim /etc/postgresql/9.6/main/postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
#For the 'Replication' settings, in this tutorial we use 2 servers only, master and slave, uncomment the 'wal_sender' line and change value to 2, and for the 'wal_keep_segments' value is 10.
max_wal_senders = 2
wal_keep_segments = 10
max_replication_slots = 1  
hot_standby = on 

+ Cấu hình authentication ( pg_hba.conf ) cho phép "repmgr" truy cập replication DB và application DB (test_db)

$ vim /etc/postgresql/9.6/main/pg_hba.conf  #paste to bottom

host    repmgr          repmgr     192.168.0.0/0     trust
host    replication     repmgr     192.168.0.0/0     trust
host    test_db         test_user  192.168.0.0/0     md5

+ Restart lại PostgreSQL

$ systemctl restart postgresql

$ tail -n100 /var/log/postgresql/postgresql-10-main.log
$ netstat -nutlp

 

3. Cấu hình Replication Manager

+ Trên master

root@repmgr_node1:~# mkdir /etc/repmgr
root@repmgr_node1:~# vim /etc/repmgr/repmgr.conf 

node_id=1  
node_name=node1  
use_replication_slots=1  
conninfo='host=repmgr_node1 user=repmgr dbname=repmgr'  
pg_bindir=/usr/lib/postgresql/10/bin
data_directory=/var/lib/postgresql/10/main
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='/etc/repmgr/auto_failover.sh'

+ Trên slave

root@repmgr_node2:~# mkdir /etc/repmgr
root@repmgr_node2:~# vim /etc/repmgr/repmgr.conf 

node_id=2  
node_name=node2  
use_replication_slots=1  
conninfo='host=repmgr_node2 user=repmgr dbname=repmgr'  
pg_bindir=/usr/lib/postgresql/10/bin
data_directory=/var/lib/postgresql/10/main
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='/etc/repmgr/auto_failover.sh'

+ Nội dung file bash shell "auto_failover.sh"

$ cat /etc/repmgr/auto_failover.sh

#!/bin/bash  
echo "Promoting Standby at `date '+%Y-%m-%d %H:%M:%S'`" >>/tmp/repsetup.log  
/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main promote >>/tmp/repsetup.log

+ Change permission

chown postgres. /etc/repmgr/auto_failover.sh

+ Join master vào replication topology

root@repmgr_node1:~$ sudo su postgres
postgres@repmgr_node1:~$ repmgr -f /etc/repmgr/repmgr.conf master register

# Cluster show command
postgres@repmgr_node1:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show

ID | Name  | Role    | Status    | Upstream | Location | Connection string                          
----+-------+---------+-----------+----------+----------+---------------------------------------------
 1  | node1 | primary | * running |          | default  | host=repmgr_node1 user=repmgr dbname=repmgr

+ Clone Database Slave - thực hiện trên slave node

$ sudo systemctl stop postgresql
$ sudo su postgresql
postgres@repmgr_node2:~$ mv /var/lib/postgresql/10/main /var/lib/postgresql/10/main-backup
postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf --force --rsync-only -h repmgr_node1 -d repmgr -U repmgr --verbose standby clone

$ sudo systemctl start postgresql
$ tail -n100 /var/log/postgresql/postgresql-10-main.log

+ Register the slave as a standby

$ sudo su postgresql
postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf --force standby register
NOTICE: standby node "node2" (id: 2) successfully registered

#See the state of our replication cluster using the command 
postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show

 ID | Name  | Role    | Status    | Upstream | Location | Connection string                          
----+-------+---------+-----------+----------+----------+---------------------------------------------
 1  | node1 | primary | * running |          | default  | host=repmgr_node1 user=repmgr dbname=repmgr
 2  | node2 | standby |   running | node1    | default  | host=repmgr_node2 user=repmgr dbname=repmgr

 

+ Switchover: Khi cần thiết chuyển đổi role giữa 2 node sử dụng câu lệnh sau

postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf -C /etc/repmgr/repmgr.conf standby switchover -v


+ Failover : Khi master node failure. Phải đảm bảo master node down, ( stop service postgresql ), sau đó promote slave node thành new master sử dụng câu lệnh

root@repmgr_node1:~# systemctl stop postgresql

$ sudo su postgresql
postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf standby promote
NOTICE: promoting standby to primary
DETAIL: promoting server "node2" (ID: 2) using "/usr/lib/postgresql/10/bin/pg_ctl  -w -D '/var/lib/postgresql/10/main' promote"
waiting for server to promote.... done
server promoted
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node2" (ID: 2) was successfully promoted to primary

postgres@repmgr_node2:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Connection string                          
----+-------+---------+-----------+----------+----------+---------------------------------------------
 1  | node1 | primary | - failed  |          | default  | host=repmgr_node1 user=repmgr dbname=repmgr
 2  | node2 | primary | * running |          | default  | host=repmgr_node2 user=repmgr dbname=repmgr

postgres@repmgr_node2:~$ psql
postgres=# \l
postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# create table student(id int primary key not null, name varchar(50));
CREATE TABLE
test_db=# insert into student values(1001,'Ngo Huy');
INSERT 0 1
test_db=# insert into student values(1002,'Vu Ha');
INSERT 0 1
test_db=# select * from student;
  id  |  name   
------+---------
 1001 | Ngo Huy
 1002 | Vu Ha
(2 rows)