Practice - Postgresql replicatio

测试环境

Azure VM
OS: redhat 7.3 (RHEL)
Postgresql: 9.2.24 (default)
参考文档 https://www.howtoforge.com/tutorial/how-to-install-and-configure-master-slave-replication-with-postgresql-96-on-centos-7/

过程概括

• 安装/配置 Postgresql
• 配置防火墙
• 配置 Master
• 配置 Slave
• 测试

安装/配置 Postgresql （on Master and Slave)

数据库安装

sudo yum install -y postgresql-server postgresql-contrib

初始化数据库

sudo postgresql-setup initdb

配置自启动

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.

检查服务, 发现5432端口

$ sudo netstat -plnt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd           
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      4657/sshd           
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      36607/postgres      
tcp6       0      0 :::111                  :::*                    LISTEN      1/systemd           
tcp6       0      0 :::22                   :::*                    LISTEN      4657/sshd           
tcp6       0      0 ::1:5432                :::*                    LISTEN      36607/postgres      

配置防火墙

$ sudo firewall-cmd --add-service=postgresql --permanent
success
$ sudo firewall-cmd --reload
success
$ sudo firewall-cmd --list-all
public (active)
 target: default
 icmp-block-inversion: no
 interfaces: eth0
 sources:
 services: dhcpv6-client postgresql ssh
 ports:
 protocols:
 masquerade: no
 forward-ports:
 sourceports:
 icmp-blocks:
 rich rules:

配置 Master

前期准备，创建同步账号和archive目录

$ sudo su - postgres
-bash-4.2$ createuser --replication -P replica
Enter password for new role:
Enter it again:
-bash-4.2$ cd /var/lib/pgsql/
-bash-4.2$ mkdir archive

修改 postgresql.conf

-bash-4.2$ cd /var/lib/pgsql/data
-bash-4.2$ vi postgresql.conf

修改以下部分
Uncomment the 'listen_addresses' line and change value of the Master server IP address

listen_addresses = '10.0.0.4'

Uncomment 'wal_level' line and change the value to 'hot_standby'.

wal_level = hot_standby

For the synchronization level, we will use local sync. Uncomment and change value line as below.

synchronous_commit = local

Enable archiving mode and give the archive_command variable a command as value. 这是手工replication需要的数据，预防自动同步失败

archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'

For the 'Replication' settings, uncomment the 'wal_sender' line and change value to 2 (in this tutorial, we use only 2 servers master and slave), 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 value to 'pgslave01'.

synchronous_standby_names = 'pgslave01'

保存退出

修改 pg_hba.conf

Paste configuration below to the end of the line.

# Localhost
host    replication     replica          127.0.0.1/32            md5

# PostgreSQL Master IP address
host    replication     replica          10.0.0.4/32            md5

# PostgreSQL SLave IP address
host    replication     replica          10.0.0.5/32            md5

重启服务

systemctl restart postgresql

配置Slave

停服务，把data目录备份

$ sudo systemctl stop postgresql
$ sudo su - postgres
$ cd /var/lib/pgsql
$ mv data{,-backup}

把data 目录从Master 上同步过来。

-bash-4.2$ pg_basebackup -h 10.0.0.4 -U replica -D /var/lib/pgsql/data -P --xlog
Password:
36536/36536 kB (100%), 1/1 tablespace

修改 postgresql.conf
listen_addresses 改成自己的IP

listen_addresses = '10.0.0.5'

Enable hot_standby on slave

hot_standby = on

新建文件 recovery.conf
内容如下：

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.4 port=5432 user=replica password=123456 application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

修改文件权限

chmod 600 recovery.conf

启动服务

systemctl start postgresql

测试

状态检查 on master

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
psql -x -c "select * from pg_stat_replication;"

You should see the state value is streaming, and the sync_state is sync.

$ sudo su - postgres
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 pgslave01        | streaming |             1 | sync
(1 row)

-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+-----------------------------
pid              | 37658
usesysid         | 16384
usename          | replica
application_name | pgslave01
client_addr      | 10.0.0.5
client_hostname  |
client_port      | 49766
backend_start    | 2018-09-10 07:48:45.91729+00
state            | streaming
sent_location    | 0/301BC80
write_location   | 0/301BC80
flush_location   | 0/301BC80
replay_location  | 0/301BC80
sync_priority    | 1
sync_state       | sync

新建数据库和表来测试 on Master

$ createdb roytest
$ psql roytest
roytest=# create table fruits (id SERIAL, name char(20), primary key(id));
NOTICE:  CREATE TABLE will create implicit sequence "fruits_id_seq" for serial column "fruits.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fruits_pkey" for table "fruits"
CREATE TABLE
roytest=#
roytest=# insert into fruits (name) values('apple');
INSERT 0 1
roytest=# insert into fruits (name) values('pear');
INSERT 0 1
roytest=# select * from fruits;
id |         name         
----+----------------------
  1 | apple               
  2 | pear                
(2 rows)

On Slave, 检查发现新库和表都同步过来了。

-bash-4.2$ psql roytest -c "select * from fruits"
 id |         name
----+----------------------
  1 | apple
  2 | pear
(2 rows)