1.index xxx contanins unexcepted zero page at block 1075
please reindex it
pg在主备切换过程中,造成了数据丢失,引起索引失效
命令行登入pg后,重新建立索引即可
reindex database xxx;
reindex talbe xxx;
reindex index xxx;
执行reindex 错误一:
could not create unique index "index1"
DETAIL: Key (column1, column2)=(value1,value2) is duplicated.
根据索引名找到表名,删除表中重复数据,执行reindx table 表名
参考sql:
delete from 表名 where ctid not in (select max(ctid) from 表名 group by column1,column2);
执行reindex 错误二:
ERROR: index "xxx" contains unexpected zero page at block 117
HINT: Please REINDEX it.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."xxx" x WHERE "sched_name"::pg_catalog.text OPERATOR(pg_catalog.=) $1::pg_catalog.text AND "trigger_name"::pg_catalog.text OPERATOR(pg_catalog.=) $2::pg_catalog.text AND "trigger_group"::pg_catalog.text OPERATOR(pg_catalog.=) $3: :pg_catalog.text FOR KEY SHARE OF x"
根据提示 ,reindex index xxx ,执行完后根据需要继续执行reindex database xxx
执行reindex 错误三:
failed to find parent tuple for heap-only tuple at (200,24) in table "xxx"
笨办法:把表格xxx数据全部导出,然后在用delete语句删除数据,在将数据导入表中即可。
优雅方法:暂未找到,还望知道的人不吝告知。
2.pg启动失败,使用sysstemctl status postgresal 和journalctl -ex未看到明显报错
查看pg数据库运行日志,发现no dicitionary pg_logical/snapshots
进入pg数据存储路径,发现该文件夹存在,用户组和权限都正常。
使用netstat -tnlp | grep 5432查看端口占用,发现有java程序监听着,杀死程序,pg可以正常启动