pg_statistic系统表主键冲突

pg_statistic系统表主键冲突

报错信息

1、数据库报错如下

错误 23505 重复键违反唯一约束"pg_statistic_relid_att_inh_index" 键值"(starelid, staattnum, stainherit)=(18413, 1, f)" 已经存在    对表"db_sqlfx.public.t_sql_project"进行自动清理

报错原因：pg_statistic索引检测数据冲突，导致插入的时候报错

排查

数据库信息

  • 查看数据库中存在很多插入的进程kill不掉，许多插入已经阻塞了很久了。pg_terminate_backend不掉。只有先kill -9掉这些插入的进程。


  • 因为是pg_statistic表，前面有介绍这张表的数据是可以vacuum analyze生成的，所以尝试清空这个表来解决，清空后发现还是有一样的问题！

vacuumdb

  • 1、对整个库进行vacuumdb发现报错：报错 uncommitted xmin 166723893 from before xid cutoff 166732460 needs to be frozen

如果是普通的表，那么我们可以尝试使用下面的方法进行

根据报错的xmin 定位到问题表

select reltoastrelid,oid,relname from pg_class where xmin='166723893';

通过报错的xmin 166723893 定位到问题事务的表为pg_toast_13422580

通过该表获取对应主表是t_xxx，备份t_xxx数据，然后truncate table t_xxx，再重建表t_xxx并导入数据，再手动执行vacuum freeze pg_class 报错解决

但是这里比较特殊，因为这个表已经不存在了，继续往下看

  • 2、尝试使用pg_dump 备份库报错：报错原因是根据这个id：18311可以看到有对应的toast表，但是在pg_class中找不到对应oid=18304的主表

[thunisoft@gauss01 ~]$ pg_dump -Usa -d db_sqlfx -f /home/thunisoft/db_sqlfx.dump

pg_dump: error: query failed: 错误:  could not open relation with OID 18304

pg_dump: error: query was: SELECT pg_catalog.pg_get_viewdef('18311'::pg_catalog.oid) AS viewdef

  • 3、尝试使用zero_demage_pages=on来跳过--这一步应该没啥用，因为不是损坏的页。而是整个对象没有了。将zero_demage_pages设置为on以后重新操作上面的步骤，还是一样报错，pg_dump一样报错。18304不知道是哪个表

  • 4、vacuumdb -d db_sqlfx -F -v -z，执行vacuumdb整个库的时候发现到，t_sql_report报错

信息:  正在积极清理"public.t_sql_report"

vacuumdb: error: vacuuming of table "public.t_sql_report" in database "db_sqlfx" 
failed: 错误:  uncommitted xmin 166723931 from before xid cutoff 166732331 needs to be frozen

  • 单独备份这张表，然后truncate这张表

  • 5、继续vacuumdb报错变了，t_sql_report表不再报错，而是pg_statistic_relid_att_inh_index表报错：

vacuumdb: error: vacuuming of table "public.t_sql_file_path" in database "db_sqlfx"
failed: 错误:  重复键违反唯一约束"pg_statistic_relid_att_inh_index"

DETAIL:  键值"(starelid, staattnum, stainherit)=(18410, 1, f)" 已经存在

  • 这里看来是pg_statistic表的索引损坏导致的，那么可以考虑重建索引

修改postgresql.conf设置ignore_system_indexes='on'

清空pg_statistic表的数据然后重建索引：

db_sqlfx=# reindex index  pg_statistic_relid_att_inh_index;

REINDEX

  • 6、再次执行vacuumdb操作

信息:  正在积极清理"public.t_sql_datasource"

vacuumdb: error: vacuuming of table "public.t_sql_datasource" in database "db_sqlfx" 
failed: 错误:  uncommitted xmin 166723949 from before xid cutoff 166732432 needs to be frozen

  • 单独备份这张表，然后truncate这张表

  • 7、信息:  正在积极清理"pg_catalog.pg_class"

vacuumdb: error: vacuuming of table "pg_catalog.pg_class" in database "db_sqlfx" 
failed: 错误:  uncommitted xmin 166723893 from before xid cutoff 166732460 needs to be frozen

  • 这次报错和前面不一样，是pg_class系统表里面的

--根据xmin查询发现是一张toast表和索引
db_sqlfx=# select reltoastrelid,oid,relname from pg_class where xmin='166723893'; 

 reltoastrelid |   oid    |       relname        

---------------+----------+----------------------

             0 | 43441904 | i_uid

             0 |    18310 | pg_toast_18304_index

(2 rows)

  • 但是根据toast表在pg_class中找不到父表，这次的报错和开始一样，报错了18340这个对象不存在

db_sqlfx=# select * from pg_class where oid = '18304';

18034没有这张表

  • 8、尝试删掉pg_class中的这两条oid记录

#allow_system_table_mods='on'
db_sqlfx=# delete from pg_class where oid in('43441904','18310');

DELETE 2

  • 删掉后还是报错uncommitted xmin 166723893，因为pg_calss没法vacuum full，暂时没太好的办法。

https://dba.stackexchange.com/questions/246618/getting-error-could-not-open-relation-with-oid-6701547-while-performing-vacuum

could not open relation with OID 18304

这个报错意味着包含表数据的文件已经消失、如果没有备份则无法恢复数据

通常的原因是以下之一：

  • 硬件问题：这可能会导致数据损坏，并且文件系统检查可能已经删除了文件

  • 软件错误

  • 人工干预：有人可能不小心删除了文件

解决办法

  • 如果是非系统表那么可以找到异常的行，然后删除，就可以执行vacuum freeze来解决

  • 如果是系统表只能备份出需要的表，然后还原，并且是使用pg_dump备份，pg_basebackup不能解决问题

  • 备份出所有表，可以使用pg_dump或者pg_dumpall

  • 这里我们采用pg_dump然后指定需要的表

select  string_agg(tablename,' -t ')  from pg_tables where schemaname='public' ;

pg_dump -Usa -d db_sqlfx -f /home/thunisoft/dump_db_sqlfx.dump -t test_xmin -t t_test

  • 然后还原即可，再次执行vacuumdb正常

  • 最后还原参数：重启

  • #zero_damaged_pages= 'off'

  • #allow_system_table_mods='off'

  • #ignore_system_indexes='off'

 

uncommitted xmin 这个问题德哥和灿灿都遇到过，算是比较常见的一个问题

参考资料：

https://stackoverflow.com/questions/66046459/how-to-fix-uncommitted-xmin-from-before-xid-cutoff-needs-to-be-frozen-automati

https://dba.stackexchange.com/questions/246749/uncommitted-xmin-during-vacuum-what-to-do

https://www.modb.pro/db/91545

posted @ 2023-05-06 09:46  binbinx  阅读(532)  评论(0)    收藏  举报