Skip to main content

Cài đặt và cấu hình Replication Master Slave Postgresql 9.6

14,March 2018
Cài đặt và cấu hình postgresql replication master slave

Master Slave Replication Postgresql là việc chạy song song hai database cùng một lúc. Master sẽ đóng vai trò là database chính để người thao tác trên đó. Trong khi đó, Slave sẽ đóng vai trò là 1 database phụ, nó có trách nhiệm chạy khi Master ngưng hoạt động. Tuy nhiên database Slave chỉ cho phép đọc dữ liệu, không cho phép ghi hay xóa dữ liệu. Hai database master và slave hoạt động song song và luôn trao đổi dữ liệu qua lại.

I. Cài đặt

1. Cài đặt posgresql 9.6

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

+ Ubuntu 16

$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
$ sudo apt install postgresql-9.6 postgresql-contrib-9.6 postgresql-client-9.6 
$ systemctl enable postgresql
$ netstat -plntu

 

II. Cấu hình

1. Cấu hình PostgreSQL master server - 192.168.10.101

- Master server sẽ có quyền read & write database và thực hiện replication data sang slave server

+ Change hostname

# vim /etc/hosts
192.168.10.101  master.itlabvn.net master
192.168.10.102  slave.itlabvn.net slave

# vim /etc/hostname 
master.itlabvn.net

+ Tạo user "replicator" sử dụng cho việc replication.

# su -l postgres
postgres@node1:~$ psql
postgres=# CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD '12345678';

postgres=# \du
                                    List of roles
 Role name  |                         Attributes                         | Member of 
------------+------------------------------------------------------------+-----------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replicator | Replication                                                | {}

+ Cấu hình postgresql.conf

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

listen_addresses = '192.168.10.101'
wal_level = hot_standby
#For the synchronization level, uncomment the synchronous_commit line and change the value as shown below.
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
#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
#For the application name, uncomment 'synchronous_standby_names' line and change the value to the name 'pgslave001'.
synchronous_standby_names = 'pgslave001'

+ Tạo folder "archive" và change permission cho account "postgres"

$ mkdir -p /var/lib/postgresql/9.6/main/archive/
$ chmod 700 /var/lib/postgresql/9.6/main/archive/
$ chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

NOTE: Nếu muốn chuyển thư mục chứa dữ liệu PostgreSQL sang 1 thư mục khác default 

$ mkdir /pg_data
$ chown -R postgres. /pg_data 
$ rsync -av /var/lib/postgresql /pg_data/
$ mv /var/lib/postgresql/9.6/main/ /var/lib/postgresql/9.6/main-backup

$ mkdir -p /pg_data/postgresql/9.6/main/archive/
$ chmod 700 /pg_data/postgresql/9.6/main/archive/
$ chown -R postgres:postgres /pg_data/postgresql/9.6/main/archive/

$ vim /etc/postgresql/9.6/main/postgresql.conf
data_directory = '/pg_data/postgresql/9.6/main'         # use data in another directory

$ systemctl restart postgresql

 

+ Edit pg_hba.conf file for authentication configuration.

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

#Paste configuration below to the end of the line.
# Localhost
local   replication     postgres                                         peer
host    replication     replicator          127.0.0.1/32                 md5

# PostgreSQL Master IP address
host    replication     replicator          192.168.10.101/32            md5

# PostgreSQL SLave IP address
host    replication     replicator          192.168.10.102/32            md5

+ Khởi động lại service "postgresql"

$ systemctl restart postgresql
$ netstat -plntu

 

2. Cấu hình PostgreSQL Slave server - 192.168.10.102

- Slave PostgreSQL server có quyền Read database

+ Change hostname

# vim /etc/hosts
192.168.10.102  slave.itlabvn.net slave
192.168.10.101  master.itlabvn.net master

# vim /etc/hostname 
slave.itlabvn.net

+ Cấu hình postgresql.conf

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

listen_addresses = '192.168.10.102'
wal_level = hot_standby
#For the synchronization level, uncomment the synchronous_commit line and change the value as shown below.
synchronous_commit = local
#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
#For the application name, uncomment 'synchronous_standby_names' line and change the value to the name 'pgslave001'.
synchronous_standby_names = 'pgslave001'
#Enable hot_standby for the slave server by uncommenting the following line and change value to 'on'.
hot_standby = on

+ Tạo folder "archive" và change permission cho account "postgres"

$ mkdir -p /var/lib/postgresql/9.6/main/archive/
$ chmod 700 /var/lib/postgresql/9.6/main/archive/
$ chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

+ Edit pg_hba.conf file for authentication configuration.

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

#Paste configuration below to the end of the line.
# Localhost
local   replication     postgres                                                     peer
host    replication     replicator          127.0.0.1/32                      md5

# PostgreSQL Master IP address
host    replication     replicator          192.168.10.101/32            md5

# PostgreSQL SLave IP address
host    replication     replicator          192.168.10.102/32            md5

+ Khởi động lại service "postgresql"

$ systemctl restart postgresql
$ netstat -plntu

 

3. Copy PostgreSQL Data from the MASTER to the SLAVE

- Tiếp theo chúng ta sẽ copy dữ liệu tron thư mục "/var/lib/postgresql/9.6/main" từ master sang slave server

+ Login Slave Posgresql server - 192.168.10.102

su - postgres
mv /var/lib/postgresql/9.6/main  /var/lib/postgresql/9.6/main-backup
mkdir /var/lib/postgresql/9.6/main 
chmod 700 /var/lib/postgresql/9.6/main 

+ Copy thư mục "/var/lib/postgresql/9.6/main"  từ Master server sang Slave server với câu lệnh "pg_basebackup"

su - postgres
#-D /var/lib/postgresql/9.6/main - là thư mục muốn đồng bộ ở slave server từ master 
pg_basebackup -h 192.168.10.101 -U replicator -D /var/lib/postgresql/9.6/main -P --xlog
Password: "12345678"

+ Tạo file "/var/lib/postgresql/9.6/main/recovery.conf" với nội dung sau

$ vim /var/lib/postgresql/9.6/main/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.10.101 port=5432 user=replicator password=12345678 application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

+ Change permission file 'recovery.conf'

chmod 600 /var/lib/postgresql/9.6/main/recovery.conf
systemctl start postgresql
netstat -plntu

 

III. Testing

- Kiểm tra khả năng replication status từ master sang slave server

+ Login master server

su -l postgres
postgres@master:~$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state 
------------------+-----------+---------------+------------
 pgslave001       | streaming |             1 | sync
(1 row)

postgres@master:~$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 1302
usesysid         | 16384
usename          | replicator
application_name | pgslave001
client_addr      | 192.168.10.102
client_hostname  | 
client_port      | 42508
backend_start    | 2018-03-14 14:56:29.457112+07
backend_xmin     | 
state            | streaming
sent_location    | 0/701A878
write_location   | 0/701A878
flush_location   | 0/701A878
replay_location  | 0/701A878
sync_priority    | 1
sync_state       | sync

postgres@master:~$ psql
postgres=# CREATE DATABASE test;
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE replica_test (id int primary key not null, name varchar(100));
CREATE TABLE
test=# insert into replica_test values(1001,'Ngo Minh Huy'),(1002,'Nguyen Minh Cuong');

+ Login Slave server

su -l postgres
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from replica_test;
  id  |       name        
------+-------------------
 1001 | Ngo Minh Huy
 1002 | Nguyen Minh Cuong
(2 rows)