PostgreSQL 12 的同步流复制搭建及主库hang问题处理与分析

前言

主备流复制,是PostgreSQL最常用、最简单的一种高可用、读写分离的形式,类似于Oracle的ADG,主库用于读写,备库可以只读。

PostgreSQL流复制,有两种方式,分别是异步流复制和同步流复制。

  • 异步流复制模式中,当备库出现异常,主库可以正常的进行读写;

  • 同步流复制模式中,当备库出现异常,主库则可能会hang住(DML、DDL)。

这两天根据需求,我需要搭建一套同步流复制库,但是在过程中遇到了一些小问题,所以记录下来,作为备忘以及指导,帮助大家避坑。

一、基本环境

本次搭建,我的PostgreSQL基本环境如下:

  • PostgreSQL版本为12.5;

  • 操作系统为CentOS 7.6;

  • Server: 192.168.18.181:18801

  • Slave: 192.168.18.182:18802

二、同步流复制搭建

我们知道,搭建PostgreSQL的流复制核心步骤非常简单,只需要2-3步:

1. 对源端数据库进行基础备份;

2. 将基础备份拷贝到目标端;

3. 配置备库参数,并启动备库

那么在基础备份中,我们直接在目标端,通过pg_basebackup工具对源端数据库进行基础备份并将数据目录直接放在目标端的/pgdata中:

pg_basebackup -h 192.168.18.181 -p 18801 -U repl -l pg_basebackup_`date +%Y%m%d%H%M%S` -Fp -X fetch -P -v -R -D /pgdata

在PostgreSQL 12中,已经没有recovery.conf文件了,而是用standby.signal文件所代替,且原来需要在recovery.conf文件中配置的primary_conninfo参数,已经融合在postgresql.conf中。

所以,此时我们只需要配置主库和备库的postgresql.conf,以及备库的standby.signal即可,具体如下:

备库的standby.signal中:

standby_mode = 'on'

主库的postgresql.conf中:

#同步流复制
synchronous_standby_names = 'standbydb1' #同步流复制才配置该值
synchronous_commit = 'remote_write'

备库的postgresql.conf中:

hot_standby = 'on'
primary_conninfo = 'application_name=standbydb1 user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

配置完成后,直接启动备库即可。

pg_ctl -D /pgdata start

三、发现问题

通过ps -ef|grep postgres,已经可以看到主库和备库的walsender和walreciver进程都已经启动,流复制已经搭建好。

但是,通过pg_stat_replication进行查看,发现当前数据库状态仍然还是异步流复制状态:

postgres=# select * from pg_stat_replication;

-[ RECORD 2 ]----+------------------------------
pid              | 11767
usesysid         | 24746
usename          | repl
application_name | walreceiver
client_addr      | 192.168.18.182
client_hostname  |
client_port      | 29946
backend_start    | 2021-01-17 22:48:36.529698+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/91000148
write_lsn        | 0/91000148
flush_lsn        | 0/91000148
replay_lsn       | 0/91000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-01-17 22:48:46.545856+08

且此时,在进行DML或者DDL操作的时候,主库会hang住。

postgres=# insert into wangxin1 values (1,'aaa');
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

但是,通过pg_stat_replication及数据比对,发现主库的wal已经全部都写入到备库中。

所以,起初我一直以为是因为备库中的wal或者disk的相关写配置参数有一些问题,并且进行了多次修改。

但是始终无法改变备库的状态为同步流复制(也尝试多次设置synchronous_commit参数,但是只有当该参数为local的时候,主库才不会hang住)。

四、原因分析

经过近一天的资料的查询和官方文档的搭建标准参数设置方法查询,发现在网上一些帖子中,需要修改postgresql.auto.conf参数文件。我们知道,postgresql.auto.conf参数文件是一个动态参数文件,一般我们不会手动去修改它,而是通过数据库中alter system set parameter_name=values的方式来修改该文件中的参数。

但是,另外一个需要知道的点就是,postgresql.auto.conf的优先级要高于postgresql.conf,当我们启动数据库的时候,postgresql会先去postgresql.auto.conf中加载参数,当该文件中没有相应的参数时,则会加载postgresql.conf中的参数。

所以,此时我进入数据库对primary_conninfo参数进行查看:

show primary_conninfo

primary_conninfo | 'user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

从数据库中的参数可以看到,此时primary_conninfo加载的参数,是异步流复制的参数,而不是我们在postgresql.conf中配置的同步流复制的参数。

此时,我们到postgresql.auto.conf中查看:

cat postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

可以看到,在postgresql.auto.conf中有参数primary_conninfo的设置,且为异步流复制的配置参数。

此时,我们就能理解最开始出现的异常的原因了。

其实,在PostgreSQL中通过pg_basebackup进行基础备份的时候(由于加了-R参数),默认就会在postgresql.auto.conf文件中加入primary_conninfo参数,且该参数为异步流复制的参数(其实我们可以不用在postgresql.conf中配置primary_conninfo参数了)。

但是,该文件中只有primary_conninfo参数,当我们想要将流复制搭建为同步的方式的时候,必须配置两个参数:synchronous_standby_names和synchronous_commit。当我们在postgresql.conf中设置这两个参数后,由于postgresql.auto.conf中没有,所以只能到这里取。

此时数据库在进行DML和DDL操作的时候,则会等待备库的响应,但备库又为异步流复制,所以不会给主库回复wal已经接收到或者写入磁盘。因此,主库会一直hang住。

五、解决方案

解决该问题,则有以下几个方式:

1. 在备库上通过命令:

alter system set primary_conninfo = 'application_name=standbydb1 user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any';

设置postgresql.auto.conf中primary_conninfo参数,并重启备库即可。

2. 手动修改postgresql.auto.conf文件,将primary_conninfo参数手动改为同步流复制参数,并重启备库。

3. 备库上通过命令:

alter system set primary_conninfo = default;

清空postgresql.auto.conf中primary_conninfo参数的配置,并重启备库,让数据库识别postgresql.conf文件中的参数。

4. 手动删除postgresql.auto.conf文件,将primary_conninfo参数手动删除或者注释,并重启备库,让数据库识别postgresql.conf文件中的参数。

搞定!

墨天轮原文链接:https://www.modb.pro/db/44044(复制到浏览器或者点击“阅读原文”立即查看)
END
(0)

相关推荐

  • navicat 连接postgresql报错pg

    PostgreSQ数据库为了安全,它不会监听除本地以外的所有连接请求,当用户通过JDBC访问是,会报一些如下的异常: org.postgresql.util.PSQLException: FATAL: ...

  • juila(0)

    这个东西如果你要是去搜素juila,用百度搜素的话,大概率是一个这种东西: emmmm,有点迷 首先,这是一门所谓有主流语言的优点的一门"贪心"的语言: Julia语[插图]是一种 ...

  • DG中switchover切换操作

    问题描述:我们配置DG的目的就是为了在主库出现故障时,备库能够提供服务,保证业务的正常运行,switchover是用户有计划的进行停机切换,能够保证不丢失数据,我记录一下我进行switchover中的 ...

  • 基于PostgreSQL流复制的容灾库架构设想及实现

    一.前言 这几天在对PostgreSQL流复制的架构进行深入研究,其中一个关键的参数:recovery_min_apply_delay引起了我的注意,设置该参数的大概意思是:在进行流复制的时候,备库会 ...

  • Feed流|账户搭建操作手册

    一 账户绑定 广告账号支持线上绑定多类型抖音账户包含认证企业号.普通企业号和个人号,直播引流投放只支持选择已绑定的抖音账户.输入手机号并输入短信验证码,点击获取抖音号类型并校验资质一致性: 1.账户类 ...

  • Redis 的主从同步(复制)

    Redis 的主从同步(复制) Redis 的主从同步(复制) 什么是主从同步(复制) 假设有两个 redis 实例 ⇒ A 和 B B 实例的内容与 A 实例的内容保持同步 那么称 A 实例是主数据 ...

  • 餐厅生意不好?学会这12个引流技巧,顾客进店率10倍提升

    作者 l 白墨 来源 l 白墨的餐饮朋友圈 如何提升顾客进店率.转化率? 引言:为什么你的餐厅进店率很低?为什么顾客从你门前经过又离开了?你真的会利用餐厅本身来引流获客吗?餐厅本身就是最大的流量载体, ...

  • 12月8日永冠转债、超声转债申购分析&价格预估

    文|公众号[小白打个新] 小白打新债必看文章

  • 12月15日威唐转债、明电转债申购分析与价格预估

    还可申请加入打新债交流群.咨询香港卡办理 文|公众号[小白打个新] 小白打新债必看文章

  • RED-ACT: 12月23日天津蓟州3.3级地震破坏力分析

    致谢和声明: 感谢中国地震台网中心为本研究提供数据支持.本分析仅供科研使用,具体灾情和灾损分析应根据现场调查情况确定. 一.地震情况简介 据中国地震台网正式测定,2019年12月23日2时44分在天津 ...

  • RED-ACT: 12月9日绵阳市安州区4.6级地震破坏力分析

    致谢和声明: 感谢中国地震台网中心和四川省地震局为本研究提供数据支持.本分析仅供科研使用,具体灾情和灾损分析应根据现场调查情况确定. 一.地震情况简介 据中国地震台网正式测定,2019年12月9日15 ...

  • 体系搭建、知识梳理、典例分析、实战演练

    体系搭建.知识梳理.典例分析.实战演练.直击中考.归纳总结,形成了一个完美的学习闭环[赞][赞][赞]初中数学三年系统培训讲义word版--勾股定理篇(升学体系)