ubuntu–postgresql流复制+主从切换
Postgresql的流复制分为两种：同步流复制、异步流复制。
- 同步流复制：实时性较高，当备库宕机，主库会被拴住，无法向主库写入数据。
- 异步流复制：实时性略低，当备库宕机，主库会不受影响，仍可以向主库写入数据，再次启动宕机备库，数据依旧可以同步到备库，当备库宕机时间过长，主库备库不在一个时间线，数据将无法同步。
- 在查找pg流复制资料发现，全网都会用到归档，pg进行数据归档后，如果数据库产生异常，我们可以从归档文件中恢复数据，但是pg的pg_wal文件也是对数据进行归档，还有必要对pg单独建立归档吗，有待研究。postgresql的内置流复制会同步ddl、dml等所有命令(具体哪些命令无法同步还不清楚)
集群规划及路径介绍
全路径 | |
log | /var/log/postgresql/ |
PGDATA | /var/lib/postgresql/12/main/ |
PGHOME | /usr/lib/posgresql/12/ |
conf | /etc/postgresql/12/main/ |
节点 | p/s | hostname | ip |
1 | primary | edgeaicdh94 | 192.168.8.194 |
2 | standby | edgeaicdh95 | 192.168.8.195 |
配置前准备【all server】
创建新用户postgres
userdel -r postgres #删除由于之前安装postgresql导致残留的postgres用户
groupdel postgres #删除用户组
groupadd postgres #新建用户组
adduser postgres #新建用户
passwd postgres #对新建用户添加密码
sudo usermod -aG sudo postgres # 赋予postgres用户管理员权限
cd /etc/sudoers.d/
sudo touch postgres
echo %postgres "ALL=(ALL)" NOPASSWD:ALL >>postgres #实现免密操作配置免密登录
- postgresql流复制用不到，但是pgpool配置pg高可用需要用到，所以在这里提前配置 su postgres
- 如果配置pg的高可用不采用pgpool的自动恢复脚本，此项可忽略
su postgres
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@edgeaicdh194
ssh-copy-id -i id_rsa_pgpool.pub postgres@edgeaicdh195卸载postgresql
sudo service postgresql stop && sudo apt-get --purge remove postgresql\* -y && sudo apt autoremove postgresql\* -y && sudo rm -r /etc/postgresql/ /etc/postgresql-common/ /var/lib/postgresql/安装postgresql-12
#添加postgresql-12源，防止安装报错
echo deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main >> /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
#安装pg-12
sudo apt-get install postgresql-12 -ypostgresql的环境变量
vim /etc/profile
#PGHOME
export PGHOME=/usr/lib/postgresql/12
export PGDATA=/var/lib/postgresql/12/main
export PATH=$PATH:$PGHOME/bin
source /etc/profile更改pg数据库管理员用户postgres的密码
sudo service postgresql start && sudo -u postgres psql -c "alter user postgres password 'abc123'"pg_hba.conf
- 主备之间设置无密通信：trust ，避免pg_basebackup命令需要输入密码
vim /etc/postgresql/12/main/pg_hba.conf
# 添加如下内容
host replication postgres 192.168.8.194/32 trust
host replication postgres 192.168.8.195/32 trust
host all all 0.0.0.0/0 trust
host all all 0.0.0.0/0 md5开始配置主从同步
主库配置【主库】
postgresql.conf
vim /etc/postgresql/12/main/postgresql.conf
cluster_name='pg194'
listen_addresses = '*' #监听地址
wal_level = hot_standby #日志等级
max_wal_senders=20 #流复制允许连接进程
wal_keep_segments =64
full_page_writes = on #同步复制
wal_log_hints = on #记录提示位，用于pg_rewind参数重新启动pg【主库】
sudo service postgresql restart备库配置【备库】
拷贝主库到备库 (pg_basebackup)
#删除备库下的数据文件
rm -r /var/lib/postgresql/12/main/*
#利用pg_basebackup方式拷贝主库数据到从库下
pg_basebackup -F p -R --progress -D /var/lib/postgresql/12/main/ -h 192.168.8.194 -p 5432 -U postgrespostgresql.conf
#更改postgresql.conf文件内容
vim /etc/postgresql/12/main/postgresql.conf
# 添加如下内容
cluster_name='pg195'
listen_addresses = '*' #监听地址
wal_level = hot_standby #日志等级
max_wal_senders=20 #流复制允许连接进程
wal_keep_segments =64
full_page_writes = on #同步复制
wal_log_hints = on #记录提示位，用于pg_rewind参数
hot_standby = on #备份时允许查询
max_standby_streaming_delay = 30s #可选，流复制最大延迟
wal_receiver_status_interval = 10s #可选，从向主报告状态的最大间隔时间
hot_standby_feedback = on #可选，查询冲突时向主反馈
max_connections = 100 # 默认参数，非主从配置相关参数，表示到数据库的连接数，默认100standby.signal
vim /var/lib/postgresql/12/main/standby.signal
# 添加如下内容
standby_mode = on #指明从库身份
primary_conrinfo = 'host=edgeaicdh194 port=5432 user=postgres password=abc123 options="-cwal_sender_timeout=5000"' #主库信息
recovery_target_timeline = 'latest' #同步到最新数据
#pg_basebackup命令执行完成后，会自动在$PDDATA路径下生成standby.signal文件
-cwal_sender_timeout=5000：中断那些停止活动超过这个时间量的复制连接，单位毫秒，默认60毫秒重新启动pg【备库】
sudo service postgresql restart测试数据库主从复制
# 创建数据库demo
create database demo;
\c demo
# 插入可产生约2Gwal日志的数据，0.4亿行
create table t1(a int);
insert into t1 values (generate_series(1,10000000));
insert into t1 values (generate_series(1,10000000));
insert into t1 values (generate_series(1,10000000));
insert into t1 values (generate_series(1,10000000));
# 查看数据
select count(*) from t1;
#查看是否是备库， 结果为t为备库，f为主库
select pg_is_in_recovery();
sudo service postgresql start && sudo -u postgres psql -c "select pg_is_in_recovery()"
#在主库查看备库信息，需要开启展开显示：\x
\x
select * from pg_stat_replication ;主从切换
1，停掉主库
sudo service postgresql stop
******************************************************************
以下命令为测试用：
******************************************************************
# 先停掉备库
sudo service postgresql stop
# 插入数据到主库,使主库数据多于备库：验证pg_rewind
insert into t1 values (generate_series(1,10000000));
# 模拟主库出现问题，停掉主库
sudo service postgresql stop
******************************************************************2，注释备库配置文件：postgresql.auto.conf–>promary_conninfo行
vim /var/lib/postgresql/12/main/postgresql.auto.conf
#primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=prefer host=192.168.8.194 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
#pg_basebackup命令执行完成后，会自动在备库的PDDATA/postgresql.auto.conf中生成primary_conninfo行3，将从库升级为主库：
sudo service postgresql start
/usr/lib/postgresql/12/bin/pg_ctl promote -D /var/lib/postgresql/12/main/
#模拟主库出现问题，从库可以正常使用，所以此处从库是开启状态，否者无法升级为主库
#执行promote命令后，standby.signal文件会消失，所以无需手动移除standby.signal文件4，新备库执行：pg_rewind
- 这一步至关重要，一定要慎重执行，执行pg_rewind 命令前，一定要关闭新备库pg，否者会报错：pg_rewind: fatal:target server must be shut down cleanly
# 关闭新备库（必须）：
sudo service postgresql stop
# 回退新备库：
/usr/lib/postgresql/12/bin/pg_rewind --target-pgdata /var/lib/postgresql/12/main --source-server='host=192.168.8.195 port=5432 user=postgres dbname=demo'
备注：
由于主库出新问题，主库和从库的数据可能会不一致（主库的数据比备库多），主备切换后，需要回退主库的数据，保证主备库数据一致
如果执行pg_rewind失败，并且无法恢复数据，那就使用pg_basebackup命令，重新配置主从同步吧
如果显示pg_rewind: source and target cluster are on the same timeline，pg_rewind: no rewind required，那就继续等待，保证新备库回退完成5，备库的standby.signal保存到指定位置
- 必须在pg_rewind后配置此项，否者pg_rewind命令会把standby.signal文件删除
#复制194节点standby.signal文件到195节点，并更改对应参数
vim /var/lib/postgresql/12/main/standby.signal
# 添加如下内容
standby_mode = on #指明从库身份
primary_conrinfo = 'host=edgeaicdh195 port=5432 user=postgres password=abc123 options="-cwal_sender_timeout=5000"' #主库信息
recovery_target_timeline = 'latest' #同步到最新数据6，原先主库变成备库
- 必须在pg_rewind后配置此项，否者pg_rewind命令会把primary_conninfo行的host变为本机host，并注释primary_conninfo行
# 在postgresql.auto.conf中，添加promary_conninfo行
vim /var/lib/postgresql/12/main/postgresql.auto.conf
primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=prefer host=192.168.8.195 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'7，开启新备库：
- 此步骤可以开始测试主备切换是否配置成功
sudo service postgresql start8，更改主备库的配置文件：postgresql.conf
- 在此步骤前，pg的主从切换已经完成，防止更改参数对数据库造成影响，就把此步骤放在配置主从切换之后了
vim /etc/postgresql/12/main/postgresql.conf
# 主库添加如下内容：
hot_standby = on #备份时允许查询
max_standby_streaming_delay = 30s #可选，流复制最大延迟
wal_receiver_status_interval = 10s #可选，从向主报告状态的最大间隔时间
hot_standby_feedback = on #可选，查询冲突时向主反馈
max_connections = 100 # 默认参数，非主从配置相关参数，表示到数据库的连接数，默认100
# 备库注释如下内容：
#hot_standby = on #备份时允许查询
#max_standby_streaming_delay = 30s #可选，流复制最大延迟
#wal_receiver_status_interval = 10s #可选，从向主报告状态的最大间隔时间
#hot_standby_feedback = on #可选，查询冲突时向主反馈
#max_connections = 100 # 默认参数，非主从配置相关参数，表示到数据库的连接数，默认1009，重新启动两台pg
sudo service postgresql restart经验
- max_wal_size
pg的日志提示
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
解决方式：max_wal_size参数作用于pg_wal路径下的文件，对于大多数情况，理想的值是增加max_wal_size的值，以使其至少可以保存一小时的日志。 但是，这里有个警告，不要将此值设置得很高，因为它会增加崩溃恢复所需的时间
参考：https://www.wangt.cc/2020/10/%E8%B0%83%E6%95%B4postgresql%E7%9A%84%E9%85%8D%E7%BD%AE%E4%BB%A5%E4%BE%BF%E5%BA%94%E5%AF%B9%E9%AB%98%E8%B4%9F%E8%BD%BD%E7%9A%84%E5%86%99%E6%93%8D%E4%BD%9C/- 同步流复制配置
/etc/postgresql/12/main/postgresql.conf【主库】
#在增加如下一行，一主一副：synchronous_standby_names = 'standby_pg1'
#增加如下一行，一主两副：synchronous_standby_names = 'FIRST 1(standby_pg1,standby_pg3)' #first 1 表示前一台实时同步，后一台异步，any 1：随意一台实时，另一台异步- highest timeline 3 of the primary is behind recovery timeline 4
如果备库宕机很久，备库时间线将会小于主库时间线，无法同步主库数据，这时主从同步将会失效，尝试使用pg_rewind命令可以把备库宕机时未同步的数据同步过来，但是时间线依旧无法恢复，
查阅资料发现只有一个解决方法，使用pg_basebackup重新配置主从同步。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
