psql: FATAL: index "pg_attribute_relid_attnum_index" contains unexpected zero page at block 0

记一次pg 10.10的问题

psql: FATAL:  index "pg_attribute_relid_attnum_index" contains unexpected zero page at block 0

有一个脚本，定时去查询主从的状态，发现报错了

[root@pg01 scripts]# ./pg_ms_status.sh
psql: FATAL:  index "pg_attribute_relid_attnum_index" contains unexpected zero page at block 0
HINT:  Please REINDEX it.
./pg_ms_status.sh: line 17: [: ==: unary operator expected
./pg_ms_status.sh: line 20: [: ==: unary operator expected
postgres=# reindex index pg_attribute_relid_attnum_index;
REINDEX

查看日志，有报错/home/pgdata/log

2020-05-26 00:01:16.566 CST,"postgres","test1",27242,"[local]",5ecbebcc.6a6a,1,"startup",2020-05-26 00:01:16 CST,4/141203,0,FATAL,XX002,
"index ""pg_attribute_relid_attnum_index"" contains unexpected zero page at block 0",,"Please REINDEX it.",,,,,,,""
2020-05-26 15:27:48.898 CST,,,9917,,5eccc4f4.26bd,1,,2020-05-26 15:27:48 CST,5/437,0,ERROR,XX002,"index ""pg_attribute_relid_attnum_index""
 contains unexpected zero page at block 0",,"Please REINDEX it.",,,,,,,""

解决
修复后还是不成功，直接drop test库，在重建

postgres=# REINDEX INDEX pg_attribute_relid_attnum_index;
REINDEX
postgres=# drop database test1;
WARNING:  could not stat file or directory "base/40962/6102_vm": Structure needs cleaning
WARNING:  could not stat file or directory "base/40962/6106_vm": Structure needs cleaning
WARNING:  some useless files may be left behind in old database directory "base/40962"
DROP DATABASE
postgres=# create database test1;
CREATE DATABASE
postgres=# grant all privileges on database test1 to yhq;
GRANT
postgres=# \c test1
You are now connected to database "test1" as user "postgres".
test1=# CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
CREATE TABLE
test1=# INSERT INTO user_tbl(name, signup_date) VALUES('yhqtest','2020-05-26');
INSERT 0 1
test1=# select *from user_tbl;
  name   | signup_date 
---------+-------------
 yhqtest | 2020-05-26
(1 row)

test1=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
 ***** | async
(1 row)

由于这是在test库，可以这么干，如果是在生产库，需要谨慎。尽量用备份或者主从切换来恢复。

做任何操作之前，能备份就要先备份。。

--奇怪 ，刚又在一个新环境出现类似的问题，然后reindex可以搞定

postgres=# \c ***
You are now connected to database "***" as user "postgres".
****=# select * from spatial_ref_sys limit 1;
ERROR:  index "spatial_ref_sys_pkey" contains unexpected zero page at block 0
HINT:  Please REINDEX it.
***=# reindex spatial_ref_sys_pkey;
ERROR:  syntax error at or near "spatial_ref_sys_pkey"
LINE 1: reindex spatial_ref_sys_pkey;
                ^
***=# reindex index spatial_ref_sys_pkey;
REINDEX
***=# select * from spatial_ref_sys limit 1;
 srid | auth_name | auth_srid |