大家可能都會(huì)遇到機(jī)房突然斷電，當(dāng)zabbix恢復(fù)運(yùn)行，查看日志發(fā)現(xiàn)有大量的PGRES_FATAL_ERROR錯(cuò)誤信息，這種情況應(yīng)該如何解決呢？

1、zabbix報(bào)錯(cuò)信息：

?[select clock,ns,value from history_uint where itemid=36570 and clock<=1662337221 and clock>1661732421 order by clock desc limit 2]

134751:20220906:092021.356 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: ?could not read block 619 in file “base/17376/55998”: read only 0 of 32768 bytes

?[select clock,ns,value from history_uint where itemid=36570 and clock<=1662337221 and clock>1661732421 order by clock desc limit 2]

134751:20220906:092021.359 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: ?could not read block 873 in file “base/17376/55991”: read only 0 of 32768 bytes

?[select clock,ns,value from history_uint where itemid=36567 and clock<=1662337221 and clock>1661732421 order by clock desc limit 2]

134751:20220906:092021.361 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: ?could not read block 873 in file “base/17376/55991”: read only 0 of 32768 bytes

[Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: could not read block 874

2、分析：

發(fā)現(xiàn)是因?yàn)橥蝗粩嚯?，導(dǎo)致pg數(shù)據(jù)庫(kù)的表部分索引出現(xiàn)問(wèn)題了，需要修復(fù)，但是由于zabbix數(shù)據(jù)使用timescaledb時(shí)序數(shù)據(jù)庫(kù)插件以及超表分區(qū)功能，故無(wú)法指定單表修復(fù)。

3、修復(fù)：

在網(wǎng)上搜索后，在以下網(wǎng)站找到了類(lèi)似的報(bào)錯(cuò)以及修復(fù)方法：

http://lxadm.com/Repairing_broken_PostgreSQL_databases_/_tables

4、修復(fù)損壞的?PostgreSQL?數(shù)據(jù)庫(kù)/?表

If your server happened to crash, PostgresSQL database is corrupted, but didn’t contain too precious information, you may try the following fix.

如果你的服務(wù)器突然發(fā)生崩潰，PostgresSQL?突然被中斷，但是沒(méi)有包含太多之前的信息，你可以嘗試安裝以下方法修復(fù)。

The typical symptoms of a corrupted Postgres database would be like below:

常見(jiàn)的因?yàn)閿?shù)據(jù)庫(kù)運(yùn)行突然中斷的日志結(jié)果如下：

2013-03-05 11:29:50 GMT ERROR: ?invalid page header in block 608102 of

relation base/16385/16615 2013-03-05 11:29:50 GMT STATEMENT: ?COPY

public.history (itemid, clock, value) TO stdout; 2013-03-05 11:29:50

GMT LOG: ?could not send data to client: Broken pipe

Or?或者

Query failed: [0] PGRES_FATAL_ERROR:ERROR: ?right sibling’s left-link doesn’t match:

block 149266 links to 70823 instead of expected 71357 in index “history_uint_1”

The actual fix is quite easy, and basically sets “zero_damaged_pages = on”, then performs vacuum and reindexing.

實(shí)際的修復(fù)也簡(jiǎn)單，在數(shù)據(jù)庫(kù)設(shè)置sets “zero_damaged_pages = on”，然后執(zhí)行vacuum and reindexing重新建立索引即可。

DATABASE=yourdatabase

?TABLES=$(echo?\\d?|?psql?$DATABASE?|?grep?“^ public”?|?awk?‘{print $3}’)

?for?TABLE in $TABLES;?do?

???echo?$TABLE

???echo?“SET zero_damaged_pages = on; VACUUM FULL?$TABLE; REINDEX TABLE?$TABLE”?|?psql?$DATABASEdone

在zabbix server或者是pg數(shù)據(jù)庫(kù)服務(wù)器，創(chuàng)建shell腳本，將以上的復(fù)制到腳本，修改為在使用的數(shù)據(jù)庫(kù)。

5、修復(fù)腳本：

vim pg_repair_index.sh

!# /bin/bash

DATABASE=zabbix???#報(bào)錯(cuò)的數(shù)據(jù)庫(kù)名稱(chēng)

TABLES=$(echo \\d | psql $DATABASE | grep “^ public” | awk ‘{print $3}’)

for TABLE in $TABLES; do

???echo $TABLE

???echo “SET zero_damaged_pages = on; VACUUM FULL $TABLE; REINDEX TABLE $TABLE” | psql $DATABASE

done?

給腳本執(zhí)行權(quán)限

chmod +x pg_repair_index.sh

執(zhí)行執(zhí)行腳本?./pg_repair_index.sh，會(huì)自動(dòng)重置每個(gè)表的索引。

更多zabbix技術(shù)資料，請(qǐng)持續(xù)關(guān)注尊龍時(shí)凱社區(qū)：http://forum.ydcanyin.com/

• 

  zabbix自動(dòng)發(fā)現(xiàn)：python-json模塊應(yīng)用介紹

  2023/03/03 7111

  zabbix自動(dòng)發(fā)現(xiàn)的python-json模塊應(yīng)用介紹

  View details

• 

  如何通過(guò)zabbix Docker配置HTTPS訪問(wèn)系統(tǒng)？

  2023/02/02 7664

  部署的Zabbix系統(tǒng)是使用http協(xié)議進(jìn)行訪問(wèn)的。有時(shí)候?yàn)榱吮ＷC安全。我們需要配置使用https協(xié)議進(jìn)行訪問(wèn)。 下面就講述如何使用自簽名的ssl證書(shū)配置https訪問(wèn)。

  View details

• 

  zabbix遠(yuǎn)程命令-Tomcat主備切換案例

  2022/08/04 9848

  使用zabbix遠(yuǎn)程命令控制tomcat主備切換

  View details

• 

  zabbix與尊龍時(shí)凱監(jiān)控對(duì)比分析（七）——網(wǎng)絡(luò)功能篇

  2023/01/06 8027

  本篇是Zabbix與尊龍時(shí)凱監(jiān)控對(duì)比專(zhuān)題系列文章的第七篇——網(wǎng)絡(luò)功能篇，具體包括IP地址管理、網(wǎng)絡(luò)端口映射發(fā)現(xiàn)、網(wǎng)絡(luò)配置備份、專(zhuān)線監(jiān)控等功能的對(duì)比分析。

  View details