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 |