postgresql主从配置

主机环境:
[root@test01 ~]# uname -a
Linux test01 3.10.0-862.el7.x86_64 #1 SMP Fri Apr 20 16:44:24 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@test01 ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
主:192.168.139.139 从:192.168.139.143
查看PostgreSQL安装版本:
[root@test01 ~]# psql --version
psql (PostgreSQL) 9.2.23
Zabbix监控PostgreSQL:https://www.tracymc.cn/archives/852
客户端设置连接PostgreSQL不需要密码:https://www.tracymc.cn/archives/838
Linux yum安装PostgreSQL-10.3以及常见操作:https://www.tracymc.cn/archives/510

postgresql安装完成后需执行如下步骤:
安装完成后,操作系统会自动创建一个postgres用户用来管理数据库,为其初始化密码:
passwd postgres(这步一定要做,后面的操作基本上都是在postgres用户下)
修改PostgresSQL数据库配置实现远程访问:
vi /var/lib/pgsql/data/postgresql.conf
将listen_addresses前的#去掉,并将listen_addresses = 'localhost' 改成 listen_addresses = '*'
修改客户端认证配置文件pg_hba.conf,将需要远程访问数据库的IP地址或地址段加入该文件:
vi /var/lib/pgsql/data/pg_hba.conf
将IPv4区下的127.0.0.1/32修改为0.0.0.0/0;将ident修改为md5.
重启服务以使设置生效:
systemctl restart postgresql

postgres的主从主称之为primary,从称为stand_by.主从配置需要注意的一个是主从的postgres的版本、环境等最好都需要一致,否则可能产生奇奇怪怪的问题.
主配置(192.168.139.139):
配置一个账号进行主从同步.修改/var/lib/pgsql/data/pg_hba.conf,增加replica用户,进行同步:
host replication replica 192.168.139.143/32 md5
这样就设置了replica这个用户可以从192.168.139.143对应的网段进行流复制请求.
给postgres设置密码,登录和备份权限:
psql -c"CREATE ROLE replica login replication encrypted password 'replica';"
修改/var/lib/pgsql/data/postgresql.conf,注意设置下下面几个地方:
wal_level = hot_standby # 这个是设置主为wal的主机
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的
重启主:
systemctl restart postgresql

postgres的从配置(192.168.139.143):
复制主库到备库,前提/var/lib/pgsql/data01需要为空:
su - postgres
mkdir -p /var/lib/pgsql/data01
chmod -R 700 /var/lib/pgsql/data01  //这步一定要做,不然启动postgresql会报错
pg_basebackup -D /var/lib/pgsql/data01 -Fp -Xs -v -P -h 192.168.139.139 -U replica -p5432 --password
配置参数文件/var/lib/pgsql/data01/postgresql.conf:
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
配置recover.conf文件:
cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data01/recovery.conf //这里的/var/lib/pgsql/data01路径与pg_basebackup这一步的路径相同
修改/var/lib/pgsql/data01/recovery.conf配置:
standby_mode = on # 这个说明这台机器为从库
primary_conninfo = 'host=192.168.139.139 port=5432 user=replica password=replica' # 这个说明这台机器对应主库的信息
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
重启从:
pg_ctl start -D /var/lib/pgsql/data01 -l /var/lib/pgsql/data01/log.log //启动报错日志可在/var/lib/pgsql/data01/log.log中查看,一定要这么启动,用systemctl启动的话默认配置会在/var/lib/pgsql/data这个目录下,会导致主从搭建不成功

查看复制状态:
192.168.139.139(主)上面执行如下语句:
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 71422 //sender的进程号
usesysid | 16384 //复制的用户id
usename | replica //复制的用户用户名
application_name | walreceiver
client_addr | 192.168.139.143 //复制的客户端地址
client_hostname |
client_port | 38108 //复制的客户端端口
backend_start | 2018-08-03 04:54:45.575049-04 //这个主备搭建的时间
state | streaming //同步状态 startup: 连接中、catchup: 同步中、streaming: 同步
sent_location | 0/70000B8 //Master传送WAL的位置
write_location | 0/70000B8 //Slave接收WAL的位置
flush_location | 0/70000B8 //Slave同步到磁盘的WAL位置
replay_location | 0/70000B8 //Slave同步到数据库的WAL位置
sync_priority | 0 //同步Replication的优先度 0: 异步、1～?: 同步(数字越小优先度越高)
sync_state | async //有三个值，async: 异步、sync: 同步、potential: 虽然现在是异步模式，但是有可能升级到同步模式

查看主从进程
-->查看wal sender进程 -->walsender进程是用来发送WAL日志记录的
[root@test01 log]# ps -ef |grep sender|grep -v grep //主上操作
postgres 71422 70611 0 04:54 ? 00:00:00 postgres: wal sender process replica 192.168.139.143(38108) streaming 0/7000150

-->查看receiver/start进程 --> walreceiver进程是用来接收WAL日志记录的,startup进程是用来apply日志的
[root@test02 data]# ps -ef |grep receiver|grep -v grep //备上操作
postgres 66422 66416 0 04:54 ? 00:00:00 postgres: wal receiver process streaming 0/7000150

验证是否开始同步:
主:
postgres=# create table t_2 (name varchar(10));
CREATE TABLE
postgres=# insert into t_2 values('1');
INSERT 0 1
备:
postgres=# select * from t_2;
name
------
1
(1 row)
postgres=# //说明数据已经同步过来,主从搭建成功,并且从的会话是只读的