postgres运维故障处理

该笔记用于记录postgres运维过程中遇到的简单问题，持续更新。

参考：

https://www.rockdata.net/zh-cn/tutorial/troubleshooting-corrupted-blocks/

https://www.rockdata.net/zh-cn/tutorial/troubleshooting-corrupted-toast/

https://www.rockdata.net/zh-cn/tutorial/troubleshooting-txn-wraparound/

数据块损坏

当查询某一张表，或者查询某个数据范围，出现以下错误，说明数据块损坏了，具体的原因可能是磁盘或者文件系统。

ERROR: invalid page in block 54206178 of relation base/16389/2825248

若不知道是查询那张表报错，可以用以下sql查询

SELECT c.oid, n.nspname AS schema, c.relname,
  CASE c.relkind
  WHEN 'r' THEN 'table'
  WHEN 'i' THEN 'index'
  WHEN 't' THEN 'TOAST table'
  WHEN 'm' THEN 'materialized view'
  WHEN 'S' THEN 'sequence'
  ELSE 'other'
  END AS type
FROM pg_class AS c
JOIN pg_namespace AS n ON c.relnamespace = n.oid
WHERE c.relfilenode = 2825248;

查询出来之后，如果对象是索引则重建索引

REINDEX INDEX index_name;

若是物理表，使用vacuum重建表

VACUUM FULL some_table;

其次可以使用以下方法，对于有问题的数据页不抛出错误，可以正常查询，但是会少数据，将可以正常查询的数据导出来，重建表

SET zero_damaged_pages TO on;
CREATE TABLE new_table (LIKE bad_table INCLUDING ALL);

TOAST表损坏

查询表时，出现以下错误

ERROR: unexpected chunk size 226 (expected 175) in final chunk 1 for toast value 8846932 in pg_toast_2877446

同样的先确定物理表

SELECT c.oid, n.nspname AS schema, c.relname
FROM pg_class AS c
JOIN pg_namespace AS n ON c.relnamespace = n.oid
WHERE c.reltoastrelid = (
        SELECT oid FROM pg_class AS t
          WHERE t.relname = 'pg_toast_2877446'
      );

 先创建一张新表，将坏表的数据插入到新表，交换两张表

CREATE TABLE new_table (LIKE bad_table INCLUDING ALL);
SET enable_seqscan TO off;

DO $$
DECLARE
  row    bad_table%ROWTYPE;
  rowkey bad_table.pkey%TYPE;
BEGIN
  FOR rowkey IN SELECT pkey FROM bad_table LOOP
    BEGIN
      INSERT INTO new_table
        SELECT * FROM bad_table WHERE pkey = rowkey;
    EXCEPTION
      WHEN others THEN
        SELECT * INTO row
          FROM bad_table WHERE pkey = rowkey;
        row.bad_column := '!!! BAD DATA !!!';
		INSERT INTO new_table SELECT (row).*;
    END;
  END LOOP;
END;
$$;
ALTER TABLE bad_table RENAME TO old_table;
ALTER TABLE new_table RENAME TO bad_table;

事务ID回卷

导致事务 ID 回卷的问题。

1. Autovacuum 设置为关闭。
2. 长时间运行的事务。
3. 繁重的 DML 操作，迫使取消自动清理工作进程。
4. 许多会话或连接长时间保持锁定。

遇到以下报错，说明事务 ID 回卷， PostgreSQL 已停止接受 DML 语句，并已切换到了只读模式。

WARNING: database "postgres" must be vacuumed within 3000000 transactions (10184)
ERROR: database is not accepting commands to avoid wraparound data loss in database "postgres" (10182)
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

修复回卷

使用下面的查询，找到发生事务回卷的表的名称。下面查询的输出中发现属于 “pg_temp_” 模式的任何表，删除表是解决问题的唯一选择，PostgreSQL 不允许对其他会话中生成的临时表进行 VACUUM。

SELECT c.relnamespace::regnamespace as schema_name, c.relname as table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
       2^31 - 1000000 - greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as remaining
  FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
  WHERE c.relkind IN ('r', 'm') ORDER BY 4;

 日志输出中的提示是提交或回滚以前创建的预备事务。使用下面的查询，来检查孤立或过时的预备事务。查询输出的 gid，回滚任何预备事务

SELECT age(transaction), * FROM pg_prepared_xacts;
ROLLBACK PREPARED gid;

数据库停机，以单用户模式连接，并对每个数据库执行 VACUUM FULL 操作，包括template0 ，template1。

postgres --single -D /path/to/your/data/directory your_database_name
vacuum full;