PG14归档失败解决办法archiver failed on wal_lsn

已于 2023-10-13 14:14:23 修改
阅读量1.1k 收藏 1
点赞数 1
分类专栏： Postgres 文章标签： pg14归档失败
于 2023-10-13 14:09:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq961573863/article/details/133809496
版权

问题描述

昨晚Repmgr+PG14主备主库因wal日志撑爆磁盘，删除主库过期wal文件重做备库后上午进行主备状态巡查，主库向备库发送wal文件正常，但是查主库状态时发现显示有1条归档失败的记录。
postgres: archiver failed on 000000010000006F00000086

  • 主库：

walsender repmgr 172.28.32.23(36122) streaming 72/1BAC3A10" walsender正常
archiver failed on 000000010000006F00000086" 归档失败

  • 备库：

walreceiver streaming 77/9EB6A198" “” “” " walreceiver正常

--查主库数据库状态
[root@pgmaster ~]# systemctl status postgres
● postgres.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgres.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2023-10-12 22:04:08 CST; 13h ago
Process: 3710968 ExecStart=/server/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA (code=exited, status=0/SUCCESS)
Main PID: 3710970 (postgres)
Tasks: 53 (limit: 201967)
Memory: 19.0G
CGroup: /system.slice/postgres.service
├─ 3710970 /server/data/pgdb/pgsql/bin/postgres -D /server/data/pgdb/data
├─ 3710971 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710992 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710993 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710994 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710995 "postgres: archiver failed on 000000010000006F00000086" "" "" "" "" "" "" "" "" ""
├─ 3710996 "postgres: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3711001 "postgres: top_portal top_portal 172.28.32.18(41438) idle" "" "" "" "" "" ""
├─ 3711003 "postgres: tj_sjjh dataexchange 172.28.32.28(35406) idle" "" "" "" "" "" "" ""
├─ 3711009 "postgres: repmgr repmgr 172.28.32.22(64096) idle" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3711468 "postgres: top_portal top_portal 172.28.32.18(41720) idle" "" "" "" "" "" ""
├─ 3713807 "postgres: top_portal top_portal 172.28.32.20(44492) idle" "" "" "" "" "" ""
├─ 3723017 "postgres: walsender repmgr 172.28.32.23(36122) streaming 72/1BAC3A10"  #wal 发送正常

--查备库状态
[root@pgslave ~]# systemctl status postgres
● postgres.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgres.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2023-10-13 00:12:19 CST; 12h ago
Process: 1931221 ExecStart=/server/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA (code=exited, status=0/SUCCESS)
Main PID: 1931223 (postgres)
Tasks: 7 (limit: 201967)
Memory: 23.2G
CGroup: /system.slice/postgres.service
├─ 1931223 /server/data/pgdb/pgsql/bin/postgres -D /server/data/pgdb/data
├─ 1931224 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 1931225 "postgres: startup recovering 00000001000000770000009E" "" "" "" "" "" "" "" "" ""
├─ 1931226 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 1931227 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 1931230 "postgres: walreceiver streaming 77/9EB6A198" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""   #wal接收
└─ 1931430 "postgres: repmgr repmgr 172.28.32.23(22956) idle" "" "" "" "" "" "" "" "" "" "" "" "" "" ""

Oct 13 00:12:17 pgslave systemd[1]: Starting PostgreSQL database server...
Oct 13 00:12:17 pgslave pg_ctl[1931221]: waiting for server to start....
Oct 13 00:12:17 pgslave pg_ctl[1931223]: 2023-10-13 00:12:17.497 CST [1931223] LOG:  redirecting log output to logging collector process
Oct 13 00:12:17 pgslave pg_ctl[1931223]: 2023-10-13 00:12:17.497 CST [1931223] HINT:  Future log output will appear in directory "log".
Oct 13 00:12:19 pgslave pg_ctl[1931221]: . done
Oct 13 00:12:19 pgslave pg_ctl[1931221]: server started
Oct 13 00:12:19 pgslave systemd[1]: Started PostgreSQL database server.

问题分析

1.查看数据库日志

在这里插入图片描述

2.查看归档配置参数

参数配置正确，归档目录权限也正确

postgres=# show archive_command;
                      archive_command                      
-----------------------------------------------------------
 /usr/bin/lz4 -q -z %p /server/data/pgdb/pg_archive/%f.lz4
(1 row)

postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

--查看归档目录的权限
[postgres@pgmaster ~]$ ls -ld /server/data/pgdb/pg_archive
drwxr-x--- 2 postgres postgres 4214784 Oct 13 13:14 /server/data/pgdb/pg_archive

3.手动切日志

手工归档成功，但是未解决，查看状态依然时卡住归档失败的那条wal记录那里

--手工归档
top_portal=# select pg_switch_wal();
 pg_switch_wal 
---------------
 72/51C4CFD8
(1 row)

--查主库数据库状态
[root@pgmaster ~]# systemctl status postgres
● postgres.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgres.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2023-10-12 22:04:08 CST; 13h ago
Process: 3710968 ExecStart=/server/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA (code=exited, status=0/SUCCESS)
Main PID: 3710970 (postgres)
Tasks: 53 (limit: 201967)
Memory: 19.0G
CGroup: /system.slice/postgres.service
├─ 3710970 /server/data/pgdb/pgsql/bin/postgres -D /server/data/pgdb/data
├─ 3710971 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710992 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710993 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710994 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3710995 "postgres: archiver failed on 000000010000006F00000086" "" "" "" "" "" "" "" "" ""
├─ 3710996 "postgres: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3711001 "postgres: top_portal top_portal 172.28.32.18(41438) idle" "" "" "" "" "" ""
├─ 3711003 "postgres: tj_sjjh dataexchange 172.28.32.28(35406) idle" "" "" "" "" "" "" ""
├─ 3711009 "postgres: repmgr repmgr 172.28.32.22(64096) idle" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 3711468 "postgres: top_portal top_portal 172.28.32.18(41720) idle" "" "" "" "" "" ""
├─ 3713807 "postgres: top_portal top_portal 172.28.32.20(44492) idle" "" "" "" "" "" ""
├─ 3723017 "postgres: walsender repmgr 172.28.32.23(36122) streaming 72/1BAC3A10"  #wal 发送正常


--查当前wal_lsn
top_portal=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 72/52638F10
(1 row)

--查当前wal_lsn对应的wal文件
top_portal=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000007200000052
(1 row)

--查当前最新检查点,最新检查点之前的wal文件均可以删除
[postgres@pgmaster ~]$ pg_controldata $PGDATA
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7268852449124462799
Database cluster state:               in production
pg_control last modified:             Fri 13 Oct 2023 10:07:35 AM CST  
Latest checkpoint location:           71/CDD2FF28
Latest checkpoint's REDO location:    71/CDD28F18
Latest checkpoint's REDO WAL file:    0000000100000071000000CD

--查报错中的wal文件
[postgres@pgmaster pg_wal]$ ls -l 000000010000006F00000086
-rw------- 1 postgres postgres 16777216 Oct 12 21:12 000000010000006F00000086
[postgres@pgmaster pg_wal]$ find /server/data/pgdb/pg_archive -name 000000010000006F00000086*
ls: cannot access '000000010000006F00000086': No such file or directory
[postgres@pgmaster pg_wal]$ find /server -name 000000010000006F00000086*
-rw------- 1 postgres postgres 16777216 Oct 12 21:12 000000010000006F00000086

4.检查$PGDATA/pg_wal/archive_status/目录下文件

[postgres@pgmaster ~]$ cd /server/data/pgdb/data/pg_wal/archive_status/
[postgres@pgmaster archive_status]$ ls -l *.ready
ls: cannot access '*.ready': No such file or directory

说明不存在需要归档但没归档的文件

该目录下，ready说明是需要归档但是没归档的，done是归档完成了的

解决办法

1.将归档失败的wal文件备份到/home/postgres目录下(生产环境如果磁盘空间允许切记不要rm删除，mv备份到目标位置)
2.手工归档select pg_switch_wal();
3.再次查看主备库状态

--1.将归档失败的wal文件备份到/home/postgres目录下
[postgres@pgmaster pg_wal]$ mv 000000010000006F00000086 /home/postgres/000000010000006F00000086
[postgres@pgmaster pg_wal]$ ls -l /home/postgres/000000010000006F00000086
-rw------- 1 postgres postgres 16777216 Oct 12 21:12 /home/postgres/000000010000006F00000086

--2.手工归档
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 73/7EF502E0
(1 row)

--3.再次查看主库状态显示正常
[root@pgmaster data]# systemctl status postgres
● postgres.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgres.service; enabled; vendor preset: disabled)
     Active: active (running) since Thu 2023-10-12 22:04:08 CST; 13h ago
    Process: 3710968 ExecStart=/server/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA (code=exited, status=0/SUCCESS)
   Main PID: 3710970 (postgres)
      Tasks: 50 (limit: 201967)
     Memory: 26.6G
     CGroup: /system.slice/postgres.service
             ├─ 3710970 /server/data/pgdb/pgsql/bin/postgres -D /server/data/pgdb/data
             ├─ 3710971 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3710992 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3710993 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3710994 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3710995 "postgres: archiver archiving 000000010000007100000035" "" "" "" "" "" "" "" "" ""
             ├─ 3710996 "postgres: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3711001 "postgres: top_portal top_portal 172.28.32.18(41438) idle" "" "" "" "" "" ""
             ├─ 3711003 "postgres: tj_sjjh dataexchange 172.28.32.28(35406) idle" "" "" "" "" "" "" ""
             ├─ 3711009 "postgres: repmgr repmgr 172.28.32.22(64096) idle" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─ 3711468 "postgres: top_portal top_portal 172.28.32.18(41720) idle" "" "" "" "" "" ""
             ├─ 3713807 "postgres: top_portal top_portal 172.28.32.20(44492) idle" "" "" "" "" "" ""
             ├─ 3723017 "postgres: walsender repmgr 172.28.32.23(36122) streaming 73/7F000BD0"

补充：若$PGDATA/pg_wal/archive_status/目录下存在大量的*.ready文件

可能的原因分析：如果数据库是突然断电，那么可能arvchive命令没有完全完成，归档目录会存在不完整的文件名称，重启数据库后，会出现归档失败的情况，这个时候，需要去归档目录删除相关归档失败文件，那么归档就会重新归档。
还未遇到该场景的问题，暂未实验。
参考链接

福娃筱欢
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
IMFFilterManager.zip_IMF Archiver Manager_IMF Filter Manager_IMF
09-24
管理垃圾邮件,与exchage 配合使用.
思庄oracle技术分享-无法传日志到逻辑备库,日志报:ORA-16191 ORA-01017 and ORA-16000
m0_61458430的博客
09-03 594
问题现象： 物理备库转换为逻辑备库后，主库报警文件文件一直报以下错误： Sun Jun 21 11:13:23 2020 Error 1017 received logging on to the standby ------------------------------------------------------------ Check that the primary and standby are using a password file and...
iOS开发Archive报错Build Failed不显示打包界面
MinggeQingchun的博客
04-11 1984
项目开发完成之后，想自己打一个ipa包下载测试一下，无奈选择Archive之后一直显示Build Failed，没有提示任何错误，这是查看了一些文档，才找到错误所在，选择Xcode右上角Show the Report navigator按钮，如下：   这时我们就可以找到Archived Failed原因了，出现了implicit declaration function Dlog is 
PostgreSQL：WAL日志归档策略
高云平的博客
03-06 4141
文章目录概述参数配置归档策略脚本 概述 今天分享一个PostgreSQL数据库配置wal日志归档的策略。所谓wal日志归档，就是把在线的wal日志备份出来。说起来简单，但是在正式的系统上需要一个完备的归档策略，以备系统所需稳定 参数配置 在开启wal日志归档，首先需要设置几个参数 wal_level = replica 该参数的可选的值有minimal，replica和logical，wal的...
pg运维之checkpoint
乡下人
11-14 78
如果增加了一行，WAL可能包含一条记录，表明数据文件中的一行必须被改变，它可能包含几条指令来改变索引条目，也许需要写一个额外的块，等等。max_wal_size是一个比较棘手的问题：首先，这是一个软限制–不是一个硬限制。然而，在许多情况下，更多的工作是由检查点进程本身完成的。因此，不要把min_wal_size设置得太低（与max_wal_size相比），以减少在负载再次增加时创建新的WAL文件的需要，可能是一个好主意。在生产中，0.7 - 0.9的值似乎是大多数工作负载的最佳选择，但可以试验一下。
PostgreSQL 为什么Archive 缓慢或不归档,问题的原因在哪里
sql server的专栏
11-25 1215
POSTGRESQL 中是可以对日志进行ARCHIVE 的,但经常会有一个问题就是ARCHIVE 的速度好像经常没有产生WAL 日志的速度快,有的时候很长一段时间WALLOG 都没有被...
failed to archive wal segment
喝醉酒的小白
04-03 456
通常情况下，PostgreSQL 将 WAL 段定期归档到指定的归档位置，以确保在出现故障时系统可以恢复。4、归档命令失败：如果你已经配置了 PostgreSQL 使用自定义的归档命令，那么命令中的错误可能会导致 “failed to archive wal segment” 错误消息。2、磁盘空间不足：归档位置可能已经用完磁盘空间，从而阻止了 PostgreSQL 的 WAL 段写入。1、权限不足：运行 PostgreSQL 服务器的用户帐户可能没有足够的权限写入指定的归档位置。
postgresql学习笔记之归档堆积处理（亲测有用)
weixin_44847119的博客
11-26 1390
一，跟归档相关的参数 1 查看是否开启归档模式 show archive_mode; ----仅在已完成的wal段进行归档，如果强制归档，归档段文件大小也是16M 2 查看归档相关参数 show archive_timeout;----超过这个时长就必须归档一次，设置为0表示默认不启用 show archive_command;----归档方式的shell命令（定义怎么去归档) 二 archive_command设置 1 常规设置 test ! -f /data/arch/%f && cp
Archiver_3.0.7.zip
12-18
Archiver 是一款功能齐全的压缩解压工具，简单实用切功能齐全，你只需简单的拖放文件就可以进行压缩,你还可以为你的文件设定密码，如果文件很大你可以直接切割文件。
Archiver归档的使用
03-26
iOS研发中数据缓存archiver归档的使用
Oracle归档日志写满(ora-00257)了怎么办
09-10
今天在使用oracle数据库做项目时，突然报错：ORA-00257: archiver error....经过本人一番折腾此问题还要归档于日志满了，下面小编把Oracle归档日志写满(ora-00257)的解决办法在此分享给大家供大家参考
Nodejs使用archiver-zip-encrypted库加密压缩文件时报错(解决方案)
01-20
压缩文件时使用了 archiver 库，加密码使用了 archiver-zip-encrypted 库。在windows系统上测试时，发现会概率的出现以下异常： events.js:174 throw er; // Unhandled 'error' event ^ Error: file data stream ...
基于NFV的虚拟化BRAS组网方案.docx
04-27
5G通信行业、网络优化、通信工程建设资料。
299-煤炭大数据智能分析解决方案.pptx
04-27
299-煤炭大数据智能分析解决方案.pptx
工资汇总打印税务计算系统-（Excel函数版）
04-27
使用说明： 1、各月工资表，已用公式设置完毕,请在AI1单元格填入月份本表自动显示数据,您再按实际情况稍加修正,工资就完成了! 2、使用时,请把一月份工资表中公式的数据,按你的实际情况修改,之后把一月份工资表复制到2至12月就行了。以后再用时参阅第一条说明。 3、养老保险、失业保险、医疗保险、住房公积金 自动生成，但各单位的比例不同，请自行修改公式中的参数。 4、AK 列至 BD 列是报税资料，自动生成。 5、“四联工资单”只须输入员工编号与选择月份，便可自动取数；请根据需要任选。 6、“工资条”全部自动生成；有单行与双行两种,请任选使用。使用工资条时,请在《个税报告》表的V9单元格选择月份。 7、《扣缴个人所得税报告表》自动生成,请在V9单元格选择月份。请不要随意改动。 8、加班工资、考勤应扣，按每月30天计算；养、失、医、房 项目提取基数与比例亦应按单位规定进行修改。 9、各表均设了保护，但未设密码，您尽可撤消，做您想作的事。 10、打印工资表时，可将不需用的列
考试资料+7、互联网与物联网.docx
04-27
5G通信行业、网络优化、通信工程建设资料
景区4G网络覆盖提升解决案例.docx
04-27
5G通信、网络优化与通信建设
基于Springboot+Vue的机动车号牌管理系统-毕业源码案例设计.zip
04-27
网络技术和计算机技术发展至今，已经拥有了深厚的理论基础，并在现实中进行了充分运用，尤其是基于计算机运行的软件更是受到各界的关注。加上现在人们已经步入信息时代，所以对于信息的宣传和管理就很关键。系统化是必要的，设计网上系统不仅会节约人力和管理成本，还会安全保存庞大的数据量，对于信息的维护和检索也不需要花费很多时间，非常的便利。 网上系统是在MySQL中建立数据表保存信息，运用SpringBoot框架和Java语言编写。并按照软件设计开发流程进行设计实现。系统具备友好性且功能完善。 网上系统在让售信息规范化的同时，也能及时通过数据输入的有效性规则检测出错误数据，让数据的录入达到准确性的目的，进而提升数据的可靠性，让系统数据的错误率降至最低。 关键词：vue；MySQL；SpringBoot框架 【引流】 Java、Python、Node.js、Spring Boot、Django、Express、MySQL、PostgreSQL、MongoDB、React、Angular、Vue、Bootstrap、Material-UI、Redis、Docker、Kubernetes
199-袁骏毅：新形势下医院数据安全治理应对实践.pdf
最新发布
04-27
199-袁骏毅：新形势下医院数据安全治理应对实践.pdf
恢复pt-archiver 归档的数据
06-07
如果您使用 `pt-archiver` 工具将数据归档到了其他表或者其他数据库中，那么恢复这些数据也需要一些步骤和方法。下面是一些常用的方法： 1. 将归档的数据从目标表中导出为 SQL 文件，然后通过 MySQL 客户端或其他...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

福娃筱欢

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值