SRE文档

深浅模式

一、pg_statistic的toast表数据损坏 ​

问题现象 ​

在安装插件的时候使用\dx元命令的时候，突然发现报了一个错误：

bash
postgres=# \dx
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
postgres=# \dx
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619

根据提示来看，主表字段还留存着Toast Pointer，但Toast表中已经没有对应的Chunk条目，怀疑toast表存在损坏或者缺失数据。

toast表的表名是字符串"pg_toast"与表的oid拼接而成，根据这个pg_toast_2619的2619的oid，可以定位到是pg_statistic的表。

bash
postgres=# select 2619::regclass;
   regclass   
--------------
 pg_statistic
(1 row)
postgres=# select * from pg_statistic;
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
postgres=#
postgres=# select 2619::regclass;
   regclass   
--------------
 pg_statistic
(1 row)
postgres=# select * from pg_statistic;
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
postgres=#

问题原因 ​

对应toast表的数据可能存在损坏

处理方法 ​

需要定位损坏的行的位置，然后可以删除有问题的行。因为pg_statistic的toast数据是更新统计信息的时候插入的，损坏后可以直接清理。vacuum analyze后会重新生成。

其他系统表损坏比较建议建议zero_damaged_pages设置为on来跳过损坏的块，然后备份业务数据，之后恢复到一个新的环境。

可以暂时先做下简单的修复，看是否能解决问题，可能执行过程会出现报错：

postgres=# REINDEX TABLE pg_toast.pg_toast_2619;
postgres=# REINDEX TABLE pg_statistic;
postgres=# VACUUM ANALYZE pg_statistic;
postgres=# REINDEX TABLE pg_toast.pg_toast_2619;
postgres=# REINDEX TABLE pg_statistic;
postgres=# VACUUM ANALYZE pg_statistic;

使用存储过程

 DO $$
DECLARE
  rec record;
BEGIN
FOR rec in SELECT * FROM pg_statistic LOOP
  raise notice 'Parameter is: %', rec.ctid;
   raise notice 'Parameter is: %', rec;
END LOOP; 
END;
$$
LANGUAGE plpgsql;
 DO $$
DECLARE
  rec record;
BEGIN
FOR rec in SELECT * FROM pg_statistic LOOP
  raise notice 'Parameter is: %', rec.ctid;
   raise notice 'Parameter is: %', rec;
END LOOP; 
END;
$$
LANGUAGE plpgsql;

结果如下：

NOTICE:  Parameter is: (0,1)
NOTICE:  Parameter is: (1255,28,f,0.9830149,1294,-0.016985118,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,2)
NOTICE:  Parameter is: (1255,29,f,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,3)
NOTICE:  Parameter is: (2606,22,f,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,4)
NOTICE:  Parameter is: (1260,1,f,0,4,-1,2,3,0,0,0,609,609,0,0,0,0,0,0,0,0,,{0.4365325},,,,"{10,3373,3374,3375,3377,4200,4544,4549,4550,4569,4570,4571,6171,6181,6182,6304,16392,16393}",,,,)
NOTICE:  Parameter is: (0,5)
NOTICE:  Parameter is: (1260,2,f,0,64,-1,2,3,0,0,0,660,660,0,0,0,950,950,0,0,0,,{0.104231164},,,,"{pg_checkpoint,pg_create_subscription,pg_database_owner,pg_execute_server_program,pg_maintain,pg_monitor,pg_read_all_data,pg_read_all_settings,pg_read_all_stats,pg_read_server_files,pg_signal_backend,pg_stat_scan_tables,pg_use_reserved_connections,pg_write_all_data,pg_write_server_files,postgres,u1,u2}",,,,)
NOTICE:  Parameter is: (0,6)
NOTICE:  Parameter is: (1260,3,f,0,1,-0.11111111,1,3,0,0,0,91,58,0,0,0,0,0,0,0,0,{0.9444444},{0.68421054},,,,{f},,,,)
NOTICE:  Parameter is: (0,7)
NOTICE:  Parameter is: (1260,4,f,0,1,1,1,3,0,0,0,91,58,0,0,0,0,0,0,0,0,{1},{1},,,,{t},,,,)
 
... ...
 
NOTICE:  Parameter is: (16,6)
NOTICE:  Parameter is: (2618,7,f,0,5,2,1,3,0,0,0,98,664,0,0,0,950,950,0,0,0,{0.993007},{0.9716709},,,,{<>},,,,)
NOTICE:  Parameter is: (16,7)
NOTICE:  Parameter is: (2618,8,f,0,589,-1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows·
NOTICE:  Parameter is: (0,1)
NOTICE:  Parameter is: (1255,28,f,0.9830149,1294,-0.016985118,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,2)
NOTICE:  Parameter is: (1255,29,f,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,3)
NOTICE:  Parameter is: (2606,22,f,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
NOTICE:  Parameter is: (0,4)
NOTICE:  Parameter is: (1260,1,f,0,4,-1,2,3,0,0,0,609,609,0,0,0,0,0,0,0,0,,{0.4365325},,,,"{10,3373,3374,3375,3377,4200,4544,4549,4550,4569,4570,4571,6171,6181,6182,6304,16392,16393}",,,,)
NOTICE:  Parameter is: (0,5)
NOTICE:  Parameter is: (1260,2,f,0,64,-1,2,3,0,0,0,660,660,0,0,0,950,950,0,0,0,,{0.104231164},,,,"{pg_checkpoint,pg_create_subscription,pg_database_owner,pg_execute_server_program,pg_maintain,pg_monitor,pg_read_all_data,pg_read_all_settings,pg_read_all_stats,pg_read_server_files,pg_signal_backend,pg_stat_scan_tables,pg_use_reserved_connections,pg_write_all_data,pg_write_server_files,postgres,u1,u2}",,,,)
NOTICE:  Parameter is: (0,6)
NOTICE:  Parameter is: (1260,3,f,0,1,-0.11111111,1,3,0,0,0,91,58,0,0,0,0,0,0,0,0,{0.9444444},{0.68421054},,,,{f},,,,)
NOTICE:  Parameter is: (0,7)
NOTICE:  Parameter is: (1260,4,f,0,1,1,1,3,0,0,0,91,58,0,0,0,0,0,0,0,0,{1},{1},,,,{t},,,,)
 
... ...
 
NOTICE:  Parameter is: (16,6)
NOTICE:  Parameter is: (2618,7,f,0,5,2,1,3,0,0,0,98,664,0,0,0,950,950,0,0,0,{0.993007},{0.9716709},,,,{<>},,,,)
NOTICE:  Parameter is: (16,7)
NOTICE:  Parameter is: (2618,8,f,0,589,-1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,)
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows·

最后在ctid=(16,7)的后边停了下来，我们根据查询下发现ctid= '(16,7)'的是正常的数据，接下来的ctid= '(16,8)'是有问题的数据。

postgres=# select * from pg_statistic where ctid= '(16,7)';
 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | sta
op3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sta
values1 | stavalues2 | stavalues3 | stavalues4 | stavalues5 
----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+----
----+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+----
--------+------------+------------+------------+------------
     2618 |         8 | f          |           0 |      589 |          -1 |        0 |        0 |        0 |        0 |        0 |      0 |      0 |    
  0 |      0 |      0 |        0 |        0 |        0 |        0 |        0 |             |             |             |             |             |    
        |            |            |            | 
(1 row)
 
postgres=# select * from pg_statistic where ctid= '(16,8)';
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619
postgres=# select * from pg_statistic where ctid= '(16,7)';
 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | sta
op3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sta
values1 | stavalues2 | stavalues3 | stavalues4 | stavalues5 
----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+----
----+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+----
--------+------------+------------+------------+------------
     2618 |         8 | f          |           0 |      589 |          -1 |        0 |        0 |        0 |        0 |        0 |      0 |      0 |    
  0 |      0 |      0 |        0 |        0 |        0 |        0 |        0 |             |             |             |             |             |    
        |            |            |            | 
(1 row)
 
postgres=# select * from pg_statistic where ctid= '(16,8)';
ERROR:  missing chunk number 0 for toast value 32789 in pg_toast_2619

接下来删除此条有问题的数据,然后发现\dx的元命令可以执行了

postgres=# delete from pg_statistic where ctid= '(16,8)';
DELETE 1
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# delete from pg_statistic where ctid= '(16,8)';
DELETE 1
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

紧接着我们再次查询pg_statistic表，发现还存在问题数据。函数再执行一次，发现又报了问题

postgres=# select * from pg_statistic;
ERROR:  missing chunk number 0 for toast value 32791 in pg_toast_2619
 
NOTICE:  Parameter is: (18,15)
NOTICE:  Parameter is: (12624,1,f,0,5,-0.81349206,1,2,3,0,0,98,664,664,0,0,950,950,950,0,0,"{0.018518519,0.017195767,0.014550265,0.013227513,0.011904762,0.011904762,0.011904762,0.01058201,0.01058201,0.01058201,0.01058201,0.009259259,0.009259259,0.007936508,0.007936508,0.007936508,0.0066137565,0.005291005,0.005291005,0.003968254,0.003968254,0.003968254,0.0026455026,0.0026455026}",,{0.97739166},,,"{E061,E021,E081,E121,E051,E141,F051,E091,F031,F041,T321,E011,F021,E071,F131,F311,F261,E031,E101,E151,E152,F501,F302,S011}","{B011,B016,B033,B111,B117,B126,B203,B209,E161,F035,F054,F114,F171,F221,F271,F304,F341,F385,F393,F405,F432,F437,F471,F531,F641,F690,F711,F762,F812,F841,F847,F856,F862,F868,M005,M012,M018,M024,S023,S041,S091,S096,S161,S211,S251,S281,S404,T031,T044,T050,T061,T101,T133,T173,T180,T212,T218,T271,T322,T332,T434,T491,T522,T571,T612,T618,T624,T651,T662,T812,T824,T829,T835,T851,T865,T871,T877,X010,X016,X034,X041,X046,X052,X058,X068,X074,X081,X090,X111,X131,X142,X152,X170,X192,X205,X232,X254,X263,X281,X301,X410}",,,)
NOTICE:  Parameter is: (18,16)
NOTICE:  Parameter is: (12624,3,f,0,1,18,1,2,3,0,0,98,664,664,0,0,950,950,950,0,0,"{0.81349206,0.03042328,0.02910053,0.023809524,0.022486772,0.017195767,0.015873017,0.013227513,0.01058201,0.005291005,0.005291005,0.0026455026,0.0026455026,0.0026455026}",,{0.26829666},,,"{"""",01,02,03,04,05,06,07,08,09,10,11,12,13}","{14,16,17,19}",,,)
ERROR:  missing chunk number 0 for toast value 32791 in pg_toast_2619
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows
postgres=# select * from pg_statistic;
ERROR:  missing chunk number 0 for toast value 32791 in pg_toast_2619
 
NOTICE:  Parameter is: (18,15)
NOTICE:  Parameter is: (12624,1,f,0,5,-0.81349206,1,2,3,0,0,98,664,664,0,0,950,950,950,0,0,"{0.018518519,0.017195767,0.014550265,0.013227513,0.011904762,0.011904762,0.011904762,0.01058201,0.01058201,0.01058201,0.01058201,0.009259259,0.009259259,0.007936508,0.007936508,0.007936508,0.0066137565,0.005291005,0.005291005,0.003968254,0.003968254,0.003968254,0.0026455026,0.0026455026}",,{0.97739166},,,"{E061,E021,E081,E121,E051,E141,F051,E091,F031,F041,T321,E011,F021,E071,F131,F311,F261,E031,E101,E151,E152,F501,F302,S011}","{B011,B016,B033,B111,B117,B126,B203,B209,E161,F035,F054,F114,F171,F221,F271,F304,F341,F385,F393,F405,F432,F437,F471,F531,F641,F690,F711,F762,F812,F841,F847,F856,F862,F868,M005,M012,M018,M024,S023,S041,S091,S096,S161,S211,S251,S281,S404,T031,T044,T050,T061,T101,T133,T173,T180,T212,T218,T271,T322,T332,T434,T491,T522,T571,T612,T618,T624,T651,T662,T812,T824,T829,T835,T851,T865,T871,T877,X010,X016,X034,X041,X046,X052,X058,X068,X074,X081,X090,X111,X131,X142,X152,X170,X192,X205,X232,X254,X263,X281,X301,X410}",,,)
NOTICE:  Parameter is: (18,16)
NOTICE:  Parameter is: (12624,3,f,0,1,18,1,2,3,0,0,98,664,664,0,0,950,950,950,0,0,"{0.81349206,0.03042328,0.02910053,0.023809524,0.022486772,0.017195767,0.015873017,0.013227513,0.01058201,0.005291005,0.005291005,0.0026455026,0.0026455026,0.0026455026}",,{0.26829666},,,"{"""",01,02,03,04,05,06,07,08,09,10,11,12,13}","{14,16,17,19}",,,)
ERROR:  missing chunk number 0 for toast value 32791 in pg_toast_2619
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows

同样的再次删除问题的行，发现pg_statistic表已经可以正常访问了

postgres=# delete from pg_statistic where ctid= '(18,17)';
DELETE 1
postgres=# select * from pg_statistic;                    
 starelid | staattnum | stainherit | stanullfrac | stawidth |  stadistinct  | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | s
taop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 |                                                                        
... ...
postgres=# delete from pg_statistic where ctid= '(18,17)';
DELETE 1
postgres=# select * from pg_statistic;                    
 starelid | staattnum | stainherit | stanullfrac | stawidth |  stadistinct  | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | s
taop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 |                                                                        
... ...

完成以上步骤后对该表进行一次完整的维护和索引重建

postgres=# REINDEX TABLE pg_toast.pg_toast_2619;
postgres=# REINDEX TABLE pg_statistic;
postgres=# VACUUM ANALYZE pg_statistic;
postgres=# REINDEX TABLE pg_toast.pg_toast_2619;
postgres=# REINDEX TABLE pg_statistic;
postgres=# VACUUM ANALYZE pg_statistic;

二、业务表toast损坏恢复手段 ​

#!/bin/bash
 j=500         #500是表的总行数
for ((i=1; i<=j;i++)) 
do
  psql -U user_recover -d postgres -c "SELECT * FROM test_tab order by id  LIMIT 1 offset $i" >/dev/null || echo $i
done
#!/bin/bash
 j=500         #500是表的总行数
for ((i=1; i<=j;i++)) 
do
  psql -U user_recover -d postgres -c "SELECT * FROM test_tab order by id  LIMIT 1 offset $i" >/dev/null || echo $i
done

三、物理坏块 ​

3.1现象 ​

逻辑备份时报错

pg_dump: Dumping the contents of table "xxxx" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY xxxxxx (id, active_flag, bkd, blk, go_show, grs, lss, lsv, lt, no_show, value, wl, inv_seg_cabin_id, ind) TO stdout;
pg_dump: [parallel archiver] a worker process died unexpectedly
pg_dump: Dumping the contents of table "xxxx" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY xxxxxx (id, active_flag, bkd, blk, go_show, grs, lss, lsv, lt, no_show, value, wl, inv_seg_cabin_id, ind) TO stdout;
pg_dump: [parallel archiver] a worker process died unexpectedly

考虑了pg自带参数zero_damaged_pages，将这个参数修改为true，但发现仍然是报错，看了下官方文档，这种方法不会对物理文件作修改，只是把内存上，损坏页面的缓存变为0。如果这个方法解决了报错，请将这表备份出来重新恢复，或者select到另一张表

3.2解决方式 ​

删除损坏行

sql
-- 创建扩展
create extension hstore；
-- 创建扩展
create extension hstore；

1、定义函数： ​

sql
CREATE OR REPLACE FUNCTION
  find_bad_row(tableName TEXT)
  RETURNS tid
  as $find_bad_row$
DECLARE
  result tid;
  curs REFCURSOR;
  row1 RECORD;
  row2 RECORD;
  tabName TEXT;
  count BIGINT := 0;
BEGIN
  SELECT reverse(split_part(reverse($1), '.', 1)) INTO tabName;
  OPEN curs FOR EXECUTE 'SELECT ctid FROM ' || tableName;

  count := 1;
  FETCH curs INTO row1;
  WHILE row1.ctid IS NOT NULL LOOP
    result = row1.ctid;
    count := count + 1;
    FETCH curs INTO row1;
    EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM '
         || tableName || ' WHERE ctid = $1' INTO row2
         USING row1.ctid;
    IF count % 100000 = 0 THEN
      RAISE NOTICE 'rows processed: %', count;
    END IF;
  END LOOP;

  CLOSE curs;
  RETURN row1.ctid;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'LAST CTID: %', result;
      RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
  RETURN result;
END
$find_bad_row$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
  find_bad_row(tableName TEXT)
  RETURNS tid
  as $find_bad_row$
DECLARE
  result tid;
  curs REFCURSOR;
  row1 RECORD;
  row2 RECORD;
  tabName TEXT;
  count BIGINT := 0;
BEGIN
  SELECT reverse(split_part(reverse($1), '.', 1)) INTO tabName;
  OPEN curs FOR EXECUTE 'SELECT ctid FROM ' || tableName;

  count := 1;
  FETCH curs INTO row1;
  WHILE row1.ctid IS NOT NULL LOOP
    result = row1.ctid;
    count := count + 1;
    FETCH curs INTO row1;
    EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM '
         || tableName || ' WHERE ctid = $1' INTO row2
         USING row1.ctid;
    IF count % 100000 = 0 THEN
      RAISE NOTICE 'rows processed: %', count;
    END IF;
  END LOOP;

  CLOSE curs;
  RETURN row1.ctid;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'LAST CTID: %', result;
      RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
  RETURN result;
END
$find_bad_row$
LANGUAGE plpgsql;

2、通过函数查找问题行： ​

sql
han=# select find_bad_row('public.description');
NOTICE: LAST CTID: (78497,6)
NOTICE: XX000: invalid memory alloc request size 18446744073709551613
find_bad_row
--------------
(78497,6)
(1 row)

han=# select * from xxxxxxx where ctid = '(78498,1)';
ERROR: invalid memory alloc request size 18446744073709551613

han=# delete from xxxxxx where ctid = '(78498,1)';
han=# select find_bad_row('public.description');
NOTICE: LAST CTID: (78497,6)
NOTICE: XX000: invalid memory alloc request size 18446744073709551613
find_bad_row
--------------
(78497,6)
(1 row)

han=# select * from xxxxxxx where ctid = '(78498,1)';
ERROR: invalid memory alloc request size 18446744073709551613

han=# delete from xxxxxx where ctid = '(78498,1)';

3、然后再执行pg_dump命令 ​

四、pg_wal因断电文件损坏 ​

报错信息：Could not read from file ""pg_wal/0646"" at offset 243287

服务器异常断电，这台因为是测试库，所以没备份以及备库

  1. 对数据库进行全库物理备份（为之后操作做保险）
  2. 用dd进行伪造这个数据块（数据块伪造全部提交）,并且更改权限
shell
for i in {1..262144}; do printf '\125'; done > committed
ls -l committed
od -xv committed | head
od -xv committed | tail

$ ls -l committed
-rw-r--r-- 1 root root 262144 2009-06-25 11:01 committed

$ od -xv committed  | head
0000000 5555 5555 5555 5555 5555 5555 5555 5555
0000020 5555 5555 5555 5555 5555 5555 5555 5555
0000040 5555 5555 5555 5555 5555 5555 5555 5555
0000060 5555 5555 5555 5555 5555 5555 5555 5555
0000100 5555 5555 5555 5555 5555 5555 5555 5555
0000120 5555 5555 5555 5555 5555 5555 5555 5555
0000140 5555 5555 5555 5555 5555 5555 5555 5555
0000160 5555 5555 5555 5555 5555 5555 5555 5555
0000200 5555 5555 5555 5555 5555 5555 5555 5555
0000220 5555 5555 5555 5555 5555 5555 5555 5555

$ od -xv committed  | tail
0777560 5555 5555 5555 5555 5555 5555 5555 5555
0777600 5555 5555 5555 5555 5555 5555 5555 5555
0777620 5555 5555 5555 5555 5555 5555 5555 5555
0777640 5555 5555 5555 5555 5555 5555 5555 5555
0777660 5555 5555 5555 5555 5555 5555 5555 5555
0777700 5555 5555 5555 5555 5555 5555 5555 5555
0777720 5555 5555 5555 5555 5555 5555 5555 5555
0777740 5555 5555 5555 5555 5555 5555 5555 5555
0777760 5555 5555 5555 5555 5555 5555 5555 5555
1000000

chown postgres.postgres committed
chmod 600 committed

mv -i committed $PGDATA/pg_wal/0646
for i in {1..262144}; do printf '\125'; done > committed
ls -l committed
od -xv committed | head
od -xv committed | tail

$ ls -l committed
-rw-r--r-- 1 root root 262144 2009-06-25 11:01 committed

$ od -xv committed  | head
0000000 5555 5555 5555 5555 5555 5555 5555 5555
0000020 5555 5555 5555 5555 5555 5555 5555 5555
0000040 5555 5555 5555 5555 5555 5555 5555 5555
0000060 5555 5555 5555 5555 5555 5555 5555 5555
0000100 5555 5555 5555 5555 5555 5555 5555 5555
0000120 5555 5555 5555 5555 5555 5555 5555 5555
0000140 5555 5555 5555 5555 5555 5555 5555 5555
0000160 5555 5555 5555 5555 5555 5555 5555 5555
0000200 5555 5555 5555 5555 5555 5555 5555 5555
0000220 5555 5555 5555 5555 5555 5555 5555 5555

$ od -xv committed  | tail
0777560 5555 5555 5555 5555 5555 5555 5555 5555
0777600 5555 5555 5555 5555 5555 5555 5555 5555
0777620 5555 5555 5555 5555 5555 5555 5555 5555
0777640 5555 5555 5555 5555 5555 5555 5555 5555
0777660 5555 5555 5555 5555 5555 5555 5555 5555
0777700 5555 5555 5555 5555 5555 5555 5555 5555
0777720 5555 5555 5555 5555 5555 5555 5555 5555
0777740 5555 5555 5555 5555 5555 5555 5555 5555
0777760 5555 5555 5555 5555 5555 5555 5555 5555
1000000

chown postgres.postgres committed
chmod 600 committed

mv -i committed $PGDATA/pg_wal/0646

💡 说明

这个只能解决这个问题，不可以修复底层文件的损坏，所以如果有备份还是备份还原比较好