postgresql-9.6 主从复制（流复制）
一、搭建PostgreSQL9.6数据库异步流复制环境
主备数据库信息如下
|
主库地址/端口 |
192.168.1.151 / 5432 |
|
备库地址/端口 |
192.168.1.176 / 5432 |
|
主备流复制用户名/密码 |
wzh / 123456 |
|
数据库用户名 |
postgres |
PostgreSQL主备数据库的同步设置主要涉及如下文件：
- pg_hba.conf postgresql 主库访问规则文件
- postgresql.conf postgresql 主库配置文件
- recovery.conf postgresql 备库访问主库配置文件
- .pgpass postgresql 备库访问主库的密码文件
正常主备流复制情况下：
- 主库需要pg_hba.conf、postgresql.conf
- 备库需要recovery.conf、.pgpass
实际操作中，建议主、备库上都配置这四个文件，因为主、备库角色是随着倒换变更的。注：recovery.conf文件在备库上是recovery.conf，在主库上配置为recovery.done。
主库（master）配置
1、创建复制用户，进行主从同步使用
[root@master ~]# systemctl start postgresql-9.6
[root@master ~]# su - postgres
上一次登录：一 5月 17 17:51:39 CST 2021pts/0 上
-bash-4.2$ psql
psql (9.6.21)
输入 "help" 来获取帮助信息.
postgres=#create user wzh SUPERUSER LOGIN password '123456';
CREATE ROLE
其中：wzh为在主库上创建的用于备库连接主库进行流复制的用户，此用户需要用户需要有REPLICATION权限和LOGIN权限。开户如：
create user wzh SUPERUSER LOGIN password '123456';
2、 配置postgresql.conf
[root@master ~]# cd /data/pg/data/
[root@master data]# vim postgresql.conf
Listen_adresses = '*'
wal_level = hot_standby # minimal, replica, or logical 使得日志支持Streaming Replication
max_wal_senders = 2 # max number of walsender processes 这个设置了可以最多有几个流复制连接
dynamic_shared_memory_type = posix
wal_keep_segments = 256 #设置流复制保留的最多的xlog数目，不要设置太小导致WAL日志还没有来得及传送到standby就被覆盖。一个WAL文件默认16M
wal_sender_timeout = 60s
hot_standby = on # "on" allows queries during recovery 设置为备库时是否支持可读
archive_mode = on
archive_command = 'cp %p /data/pg/data/pg_archive/%f'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
3、 配置pg_hba.conf
[root@test ~]# cd /data/pg/data/
[root@test data]# vim pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication wzh 192.168.1.0/24 md5
host all wzh 192.168.1.0/24 trust
其中：
1） wzh为在主库上创建的用于备库连接主库进行流复制的用户，此用户需要用户需要有REPLICATION权限和LOGIN权限。
2） 192.168.1.0/24为备库地址段+掩码
4、 （可选）配置recovery.done、.pgpass
同备库recovery.conf、.pgpass配置。recovery.conf中如下IP、端口、用户名要对应备库信息：
primary_conninfo = 'host=1192.168.1.151 port=5432 user='123456' 备库连接主库地址、端口、用户名、密码
5、 配置完毕需重启数据库
[root@master ~]# systemctl restart postgresql-9.6
从库（standby）安装 并 配置postgresql
同步data目录：
从库安装完成后，不初始化，若已经初始化，删除其/data/pg/data目录下的所有内容
执行，以下命令将主数据库的/data/pg/data的目录同步过来。
（9.0版本后引入了pg_basebackup工具，在备库操作）：
pg_basebackup工具支持对主库发起一个基准备份，发起备份需要超级用户权限或REPLICATION权限，注意max_wal_senders参数配置，因为pg_basebackup工具将消耗至少一个WAL发送进程。
如下IP为主库地址
pg_basebackup -h 192.168.1.151 -U wzh -F p -x -P -R -D /data/pg/data/ #-R 表示会在备份结束后自动生成recovery.conf文件，这样就避免了手动创建。
备库配置：
1、 修改postgresql.conf
hot_standby = on # "on" allows queries during recovery 设置为备库时是否支持可读
2、 配置recovery.conf
standby_mode = on #指明从库身份
recovery_target_timeline = 'latest' #同步到最新数据
primary_conninfo = 'host=192.168.1.151 port=5432 user= wzh password='123456' 连接主库地址、端口、用户名、密码
3、 设置连接主库密码.pgpass
10.10.10.1: 5432:replication: u_standby:standby123 //备库都主库同步WAL日志使用
10.10.10.2: 5432:replication: u_standby:standby123 //倒换后，主库降备库，新备库使用
4、 配置完毕需重启数据库
[root@standby~]# systemctl restart postgresql-9.6
启动从数据库，可能会报权限不正确的错误
[root@standby~# cd /data/pg/
[root@standby pg]# ls
data
[root@standby pg]# chown -R postgres:postgres data
[root@standby pg]# chmod -R 0700 data
再次重启数据库，就可以了
[root@standby pg]# systemctl restart postgresql-9.6
结果检查：
1、 配置成功后，可以查看主、备库的walsender、walreceiver进程。
ps -ef | grep wal
主库：
备库：
2、测试主从同步
在主库上创建一个新的库
到备库上查看是不是已同步过去，如果同步过来，就说明已ok
同步状态查询
在主库执行，查询从库同步状态
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
出现async 说明是同步成功的
