盘古云课堂 ， 交易担保 ， 放心买 ， 有请大咖 首期第3讲 7月7日 20：00
小程序
背景
数据库备份报错 ERROR: invalid memory alloc request size 18446744073709551613
这种问题可以首先想到使用zero_damaged_pages自动修复，但遗憾的是并没有效果，这种方法不会对物理文件作修改，只是把内存上，损坏页面的缓存变为0。
如果这个方法解决了报错，请将这表备份出来重新恢复，或者select到另一张表。
块损坏会导致备份失败，所以从备份恢复的方案也不行，另外当这个表访问正常块时是正常的，只有当访问到异常的块才会报错，所以有一定的隐蔽性，如果不检查备份很难发现此类问题
解决办法：
方案一
使用比较粗暴的方式，这种方式适用于数据量较小的表，大表效率会非常低
1.查找损坏的表
运行 pg_dump，它将会检测出哪个表已损坏
2. 查找损坏的行
进行搜索以确定哪一行已损坏。通过查询您在步骤 1 中找到的损坏的表来执行此操作。
select * from <broken_table_name> limit <N>;--此处可以加offset进行判断
增加 N 直到你看到命令崩溃，减少 N 直到它工作。缩小直到找到导致崩溃的确切行。
3.删除损坏的行
查找断行的ctid
select ctid from <broken_table_name> limit <N>;
它是最后一个ctid
delete from <broken_table_name> where ctid = '(id, id}';
4. 重复这个过程，直到 pg_dump 工作
遗憾的是方案一并不可靠，删除损坏的块就会报错，损坏的快不能删除
方案二
创建hstore扩展
1、create extension hstore；
2、创建自定义函数：
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;
3、通过函数查找问题行：
js1=# 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)
js1=# select * from xxxxxxx where ctid = '(78498,1)';
ERROR: invalid memory alloc request size 18446744073709551613
js1=# delete from xxxxxx where ctid = '(78498,1)';
js1=# select find_bad_row('job.description');
NOTICE: rows processed: 100000
NOTICE: rows processed: 200000
NOTICE: rows processed: 300000
NOTICE: rows processed: 400000
NOTICE: rows processed: 500000
NOTICE: rows processed: 600000
NOTICE: rows processed: 700000
NOTICE: rows processed: 800000
find_bad_row
--------------
在我们这里需要对xxxx表格进行处理
4、然后再执行pg_dump命进行备份
参考：https://blog.dob.sk/2012/05/19/fixing-pg_dump-invalid-memory-alloc-request-size/
表总共有1400w数据，psql登录服务器修复到700W左右的时候就会断开连接，这里可以修改函数的count，让其从700w开始继续检测，这里没有继续跑
https://blog.csdn.net/weixin_30329623/article/details/101092428
方案二跑到700w的时候就断开连接了
server closed the connection unexpectedly this probably means the server terminated abnormally before or while processing the request
the connection to the server was lost.attempting reset:failed
方案三
该方法和方案二类似，是批量处理
创建自定义函数检测损坏的行
-
1、创建函数
CREATE OR REPLACE FUNCTION is_bad_row(tableName TEXT, tabName TEXT, tidInitial tid)
RETURNS integer
as $find_bad_row$
BEGIN
EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM ' || tableName || ' WHERE ctid = $1' USING tidInitial;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '% = %: %', tidInitial, SQLSTATE, SQLERRM;
RETURN 1;
END
$find_bad_row$
LANGUAGE plpgsql;
-
2、创建临时表存储ctid
create table bad_rows as
SELECT ctid as row_tid
FROM your_schema.your_table
where is_bad_row('your_schema.your_table', 'your_table', ctid) = 1
--当然这一步也可以分批次处理,根据ctid来分组处理，表太大，一个事物处理上面会很慢
create table bad_rows(ctid varchar(100));
insert into SELECT ctid as row_tid
FROM your_schema.your_table
where is_bad_row('your_schema.your_table', 'your_table', ctid) = 1 and ctid <'(10000,1)'
-
3、删除损坏的行
delete from your_schema.your_table where ctid in (select row_tid from bad_rows)
-
4、删除临时表
drop table bad_rows
使用stackoverflow上面的方式
使用这种方式报错设备空间不足，表太大导致
方案三也失败了
实际解决方案
通过上面三种方案来看都是想要找到坏块，然后删除掉坏块来解决这个问题，那么我们也可以忽略坏块把正常的数据导出来
ERROR: invalid memory alloc request size 18446744073709551613这个报错使用zero_damaged_pages不能自动忽略坏块
我们来尝试定位坏块
-
1、在执行vacuum的时候报错：
WARNING: invalid page in block 1441448 of relation base/17290/298138；zeroing out .
可以看到报错很明显，有坏块，那知道坏块就是1441448，那么我们就可以忽略这个坏块来解决了
-
2、删除过程中报错：
delete from t_xxx where ctid ='(1441448,1)';
ERROR ：invalid page in block 1441448 of relation base/17290/298138；
-
3、原因是没有设置
alter system set zero_damaged_pages='on'；select pg_reload_conf();，设置之后删除发现都是0，说明并没有真正删掉
postgres =#select count(*) from t_xxx;
count
-------------
14437462
(1 row)
postgres=# delete from t_xxx where ctid='(1441448,1)';
DELETE 0
postgres=# delete from t_xxx where ctid='(1441448,2)';
DELETE 0
postgres=# delete from t_xxx where ctid='(1441448,3)';
DELETE 0
并且使用select * from t_xxx where ctid > '(1441448,1)' and ctid <'(1441449,1)';
-
4、查询到这个块的时候仍然报错select * from t_xxx limit 42
select * from t_xxx limit 42
ERROR: invalid memory alloc request size 18446744073709551613
-
5、也就是这个块损坏了，虽然没有数据但是不能正常访问。
count(*)可以统计出数量
postgres =#select count(*) from t_xxx;
count
-------------
14437462
(1 row)
-
6、停掉应用
找到坏块之后，我们来处理数据，处理的步骤是忽略坏块，使用临时表将数据合并。
select * int t_1441448 from t_xxx where ctid <='(1441447,3)';
select * int t_1441449 from t_xxx where ctid >='(1441449,1)';
-
7、将两部分数据分别插入临时表，然后再处理
insert into t_1441448 select * from t_1441449 ;
-
8、修改原来的表名，然后将新的表名修改为原来的表名
alter table t_xxx rename to t_xxx_bak;
alter table t_1441448 rename to t_xxx;
-
9、关闭参数
sql
alter system set zero_damaged_pages='off'；select pg_reload_conf();
总共损失了差不多一页数据，有不到10条数据。
10、对整库进行备份，，让运维人员对整个磁盘设备进行检查是否有坏块，并修复坏块
当然在网上也看到了一些修复方法，使用dd抹除坏块但是不到万不得已，建议不要使用
更多资料参考下篇
参考资料：
https://developer.aliyun.com/article/43409
https://blog.csdn.net/weixin_30329623/article/details/101092428
https://stackoverflow.com/questions/32687602/postgresql-9-4-suddenly-invalid-memory-alloc-request-size
http://brandee-ecdlutg.blogspot.com/2011/05/database-postgresql-94-suddenly-invalid.html
https://blog.dob.sk/2012/05/19/fixing-pg_dump-invalid-memory-alloc-request-size/
https://confluence.atlassian.com/jirakb/invalid-memory-alloc-request-size-440107132.html
文章评论