Postgresql 主从复制+主从切换（流复制）及各种问题处理

pgsql有多种主从复制方式，推荐的是流复制

一、前置条件

1.至少两个pgsql数据库（可以是一台设备上的两个）

可以参考下面的教程
pgsql编译安装：pgsql 编译安装（linux）
pgsql单机多开：pgsql 单机多开

2.两个数据库已经用工具连接上

9版本后才支持流复制，完成的支持建议用10以后的版本（现在应该都用是14，15了）

3.流复制优点说明

即使副库和主库连接断开一段时间，也不会影响（时间太长了不行）

二、配置主从

演示主机如下
主库机 192.168.0.233：5432
从库机 192.168.0.165：5432

（一）主库操作

1.创建复制账号

psql

这里的账号名为replica，密码为replica

CREATE USER replica replication encrypted password 'replica';

2.修改pg_hba.conf

vi /var/pgsqldata/pg_hba.conf

添加下面内容，下面的192.168.0.165改为从服务器的ip

host   replication    replica    192.168.0.165/32     trust

如果有host all all这样的字段，记得写在这个之上

3.修改postgresql.conf

vi /var/pgsqldata/postgresql.conf

找到REPLICATION模块配置，默认状态是全部为注释状态，这里有几个参数可以调整，也可以默认不改动：

级别
wal_level = replica

4.重启数据库

systemctl restart pgsql

（二）从库操作

1.先关闭数据库进程

systemctl stop pgsql

2.基础备份

先将当前的已有的数据做基础备份
如果当前目录不为空，是不能操作的，选择一个空的目录，然后在运行上面的命令，如果原来没有数据库，也可以删除原来的默认文件

mkdir /var/pgsqldata && cd /var/pgsqldata

手动进行初始的备份

pg_basebackup -F p -P -R -D /var/pgsqldata -h 192.168.0.233 -p 5432 -U replica -W

密码为上面设定的replica

同步完成后，需要把这里的权限重新明确一下，以防无法启动

chmod -R 750 /var/pgsqldata

chown -R postgres:postgres /var/pgsqldata

3.修改postgresql.conf

vi /var/pgsqldata/postgresql.conf

修改下面几个部分
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询

max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间

wal_receiver_status_interval = 10s # 多久向主报告一次从的状态

hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

4.启动从服务器

因为已经设置为从服务器了，所以重启pgsql自动进行主从同步

systemctl restart pgsql

（三）检查连接是否同步

在主库的psql，输入下面的命令，可以查看所有的连接，有说明已经正常了

select client_addr,sync_state from pg_stat_replication;

三、其它注意事项和可能遇到问题

（一）时区/地区问题

如果主从的时区不同，会报错。
可以用下面的进行检查处理

1.主库设备

locale

找到第一个显示的地区

2.从库设备

dpkg-reconfigure locales

然后在列表中找到主库的地区，输入数字即可

（二）权限问题

1.pgsql的目录权限是750，其它无法启动

（三）插件问题

从库必须有主库的插件，否则会启动失败
从库的插件需要和主库保持一致，可以一下操作（并不是每个插件都一定要装才能启动，但是考虑到从库可能变主库，所以还是先都装上好）

sql命令在主库查询所有的插件

select * from pg_extension;

比如看到如下结果，说明安装了下面的插件

之前主库怎么安装插件的，这里也怎么安装，我这里也有几个插件的安装方法

pg_trgm，rum插件 ：链接

pg_stat_statements插件：添加链接描述

postgis插件： 链接