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)
