PostgreSQL9.6主从复制配置_postgres 9.6 主从复制-优快云博客

本文链接：https://blog.youkuaiyun.com/cen50958/article/details/103593315

HA方式简介

1.基于日志文件的复制
Master库向Standy库异步传输数据库的WAL日志，Standby解析日志并把日志中的操作重新执行，以实现Replication功能。缺点在于Master库必须等待每个WAL日志填充完整后才能发给Standby，如果在填充WAL日志的过程中Master库宕机，未发送的日志内的事务操作会全部丢失
2.异步流复制模式
Master库以流模式向Standby库异步传输数据库的WAL日志，Standby解析收到的内容并把其中的操作重新执行，以实现Replication功能，这个方式与"基于日志文件的复制"相比不需要等待整个WAL日志填充完毕，大大降低了丢失数据的风险，但在Master库事务提交后，Standby库等待流数据库的时刻发生Master宕机，会导致丢失最后一个事务的数据。同时备库可以配置成HOT Standby，可以向外提供查询服务，供分担负载
3.流同步复制模式（Synchronous Replication）
流复制模式的同步版本，向Master库发出commit命令后，该命令会被阻塞，等待对应的WAL日志流所有被配置为同步节点的数据库上提交后，才会真正提交。因为只有Master库和Standby库同时宕机才会丢数据，多层事务嵌套时，子事务不受此保护，只有最上层事务受此保护。纯读操作和回滚不受此影响。同时备库可以配置成HOT Standby，可以向外提供查询服务，供分担负载。采用这种模式的性能损耗依据网络情况和系统繁忙程度而定，网络越差越繁忙的系统性能损耗严重

环境

配置
角色 IP地址系统版本数据库版本
Master 192.168.47.120 CentOS Linux release 7.6.1810 (Core) PostgreSQL9.6.16
Standby 192.168.47.121 CentOS Linux release 7.6.1810 (Core) PostgreSQL9.6.16
前置条件
Master和Standby都需安装postgres数据库完成，安装教程可参考：CentOS7安装PostgreSQL9.6数据库操作步骤

角色	IP地址	系统版本	数据库版本
Master	192.168.47.120	CentOS Linux release 7.6.1810 (Core)	PostgreSQL9.6.16
Standby	192.168.47.121	CentOS Linux release 7.6.1810 (Core)	PostgreSQL9.6.16

主库（Master）配置

创建repl用户
登录Master库，创建用于复制数据具有replication权限的用户

 CREATE ROLE repl  login replication password '123456';

[root@localhost 9.6]# su - postgres
Last login: Wed Dec 18 21:14:02 CST 2019 on pts/0
-bash-4.2$ psql -U postgres
psql (9.6.16)
Type "help" for help.

postgres=#  CREATE ROLE repl  login replication password '123456';
CREATE ROLE

配置网络策略
修改Master库的pg_hba.conf(/var/lib/pgsql/9.6/data/pg_hba.conf)，把Master库和Standby库的地址添加进Master库网络策略白名单，使Standby库远程连上Master库，用于复制
vim /var/lib/pgsql/9.6/data/pg_hba.conf


# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host    all             all             192.168.47.0/24         md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     repl      192.168.47.120/32            md5
host    replication     repl      192.168.47.121/32            md5
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

数据库配置
切换用户为postgres用户，创建文件夹（/var/lib/pgsql/9.6/archive）用于存放归档文件

[root@localhost data]# su - postgres
Last login: Wed Dec 18 21:27:07 CST 2019 on pts/0
-bash-4.2$ mkdir -p /var/lib/pgsql/9.6/archive
-bash-4.2$ ll /var/lib/pgsql/9.6
total 8
drwxr-xr-x  2 postgres postgres    6 Dec 18 21:27 archive
drwx------  2 postgres postgres    6 Nov 30 23:00 backups
drwx------ 21 postgres postgres 4096 Dec 18 21:28 data
-rw-------  1 postgres postgres  923 Dec 18 21:13 initdb.log

修改Master库的postgresql.conf(/var/lib/pgsql/9.6/data/postgresql.conf)
vim /var/lib/pgsql/9.6/data/postgresql.conf

#监听所有ip
listen_addresses = '*'
#热备模式
wal_level = logical
#开启归档模式
archive_mode = on
#最多有10个流复制连接
max_wal_senders = 10
#wal日志文件保存的最大个数，每个大小16MB
wal_keep_segments = 64	
#归档命令：将归档的文件放入/var/lib/pgsql/9.6/archive/目录下
archive_command = 'test ! -f /var/lib/pgsql/9.6/archive/%f && cp %p /var/lib/pgsql/9.6/archive/%f'

修改完后重启数据库

systemctl restart postgresql-9.6

从库配置

测试从库是否能连接主服务器

[root@localhost data]# psql -h 192.168.47.120 -U postgres
Password for user postgres: 	
psql (9.6.16)
Type "help" for help.
postgres=#

清空从库的data目录
可在安装的时候不初始化数据（/usr/pgsql-9.6/bin/postgresql96-setup initdb）
停止从库
```
systemctl stop postgresql-9.6
```
删除data目录
```
rm -rf /var/lib/pgsql/9.6/data/*
```

基础数据备份
将Master的数据copy到Standby，需要先将登录账号切换为postgres

su postgres
pg_basebackup -F p --progress -D /var/lib/pgsql/9.6/data/ -h 192.168.47.120 -p 5432 -U repl --password

[root@localhost 9.6]# su postgres
#创建归档目录
 mkdir -p /var/lib/pgsql/9.6/archive
 #基础数据复制
bash-4.2$ pg_basebackup -F p --progress -D /var/lib/pgsql/9.6/data/ -h 192.168.47.120 -p 5432 -U repl --password
Password: 
72034/72034 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
bash-4.2$ exit

修改recovery配置文件用于从库从主库获取数据
复制recovery.conf文件到从库中，也切换用户为：postgres

su - postgres
cp /usr/pgsql-9.6/share/recovery.conf.sample  /var/lib/pgsql/9.6/data/recovery.conf

修改配置

 #表示该节点是从库
standby_mode = on  
#从机信息和连接用户
primary_conninfo = 'host=192.168.47.120 port=5432 user=repl password=123456'
#说明恢复到最新状态
recovery_target_timeline = 'latest'

修改postgres.conf文件

#说明这台机器不仅用于数据归档，还可以用于数据查询
hot_standby = on
#出现错误复制，向主机反馈
hot_standby_feedback = on
#流备份的最大延迟时间(默认值)
max_standby_streaming_delay = 30s 
#向主机汇报本机状态的间隔时间(默认值)
wal_receiver_status_interval = 10s

启动从库
```
systemctl restart postgresql-9.6
```

测试是否主从复制配置成功
1.连接Master数据库，对Master库数据操作，看Standby是否数据一致
2.登录Master查询当前复制清空

[root@localhost ~]#  su postgres
bash-4.2$ psql 
could not change directory to "/root": Permission denied
psql (9.6.16)
Type "help" for help.

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.47.121 | async
(1 row)

断开Standby查询

postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state 
-------------+------------
(0 rows)

主从复制配置出现的错误

启动从库出现错误：Permission denied
错误描述：

Dec 18 22:48:30 localhost.localdomain postgresql96-check-db-dir[17050]: cat: /var/lib/pgsql/9.6/data//PG_VERSION: Permission denied

问题解决：
产生原因：在进行基础数据备份时，采用的是root账号备份，造成data下数据目录所属为：root账号，则postgres在启动的时候无权读取文件
解决：删除从库data下的所有文件，切换到postgres账号下，然后重新执行基础数据备份
```
[root@localhost 9.6]# rm -rf  /var/lib/pgsql/9.6/data/*
[root@localhost 9.6]# su postgres
 #基础数据复制
bash-4.2$ pg_basebackup -F p --progress -D /var/lib/pgsql/9.6/data/ -h 192.168.47.120 -p 5432 -U repl --password
Password: 
72034/72034 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
bash-4.2$ exit	
```