PostgreSQL复制断开故障

栏目: 数据库 · PostgreSQL · 发布时间: 6年前

问题

PostgreSQL导入大量数据时,导致备机复制断开

日志中的错误消息如下:

 FATAL: terminating walreceiver process due to administrator command 
 LOG: ecord with incorrect prev-link 3F136/36 at 28/C000098 

原因

推测导入数据时导致备机的复制状态反馈超时导致主节点断开复制,之后备机读到了已被回收的WAL旧数据。

如何处理?

把pg_wal中的WAL清空,再重启备机

参考

检查PostgreSQL邮件列表,发现有类似bug。

https://www.postgresql.org/message-id/20180523.103409.61588279.horiguchi.kyotaro%40lab.ntt.co.jp

> When this last error occurs, the recovery is to go on the replica and remove
> all the WAL logs from the pg_xlog director and then restart Postgresql. 
> Everything seems to recover and come up fine.  I've done some tests
> comparing counts between the replica and the primary and everything seems
> synced just fine from all I can tell.  


It is right recovery steps, as far as looking the attached log
messages.

A segment is not cleard on recycling. walreceiver writes WAL
record by record so startup process can see arbitrary byte
sequence after the last valid record when replication connection
is lost or standby is restarted.

https://www.postgresql.org/message-id/20180426.195304.118373455.horiguchi.kyotaro@lab.ntt.co.jp

A segment is not cleard on recycling. walreceiver writes WAL
record by record so startup process can see arbitrary byte
sequence after the last valid record when replication connection
is lost or standby is restarted.


The following scenario results in the similar situation.


1. create master and standby and run.


   It makes happen this easily if wal_keep_segments is set large
   (20 or so) on master and 0 on standby.


2. Write WAL to recycle happens on standby. Explicit checkpoints
   on standby make it faster. May be required to run several
   rounds before we see recycled segment on standby.


   maybe_loop {
     master:
       create table t (a int);
       insert into t (select a from generate_series(0, 150000) a);
       delete from t;
       checkpoint;


     standby:
       checkpoint;
       
<check for="" recycled="" segments="" in="" pg_wal="">
 
   }


3. stop master


4. standby starts to complain that master is missing.


  At this time, standby complains for several kinds of failure. I
  saw 'invalid record length' and 'incorrect prev-link' this
  time. I saw 'invalid resource manager ID' when mixing different
  size records. If XLogReadRecord saw a record with impossibly
  large tot_length there, it will causes the palloc failure and
  startup process dies.


5. If you see 'zero length record', it's nothing interesting.
  Repeat 3 and 4 to see another.
</check>

以上所述就是小编给大家介绍的《PostgreSQL复制断开故障》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

网络英雄传

网络英雄传

郭羽、刘波 / 江苏凤凰文艺出版社 / 2018-6 / 59.80元

“商战鬼才郭羽、营销奇才刘波强强联手,凝集十年实战经验,倾力打造商战巨作。” 这是一个商业竞争和资本激战交织的惊心动魄的创业交锋故事。 由郭天宇、刘帅共同创立的在线旅游公司万全天盛凭借其出色的商业模式异军突起,与老牌巨头“51旅游网”两强相争,但国际巨头通远来势汹汹,国内在线旅游市场进入战火纷飞的“三国杀”时代,分踞杭、沪、京三地互相“搏杀”。中国新兴的互联网公司面对国际巨头的入侵,毫不退缩......一起来看看 《网络英雄传》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具