PostgreSQL物理坏块和文件损坏案例分享

Stella981
• 阅读 594

作者简介

王睿操,平安好医数据库架构岗,多年postgresql数据库运维开发工作。曾就职于中国民航信息,迪卡侬。对其他数据库产品也有一定的涉猎。

背景

笔者最近发现很多朋友经常遇到PostgreSQL坏块或者数据混乱的情况,网上中文资料比较少,于是整理了一下笔者遇到各种各样的报错以及解决方案

案例一:物理坏块

逻辑备份时报错

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

原因:数据库产生坏行(可能是硬件损坏,可能是一个bug(piece of memory gets overwritten by random data pg9.2之前版本),也有可能是不正确的硬件配置)

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

解决方式:删除损坏行

create extension hstore;(过程省略)

1、定义函数

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、通过函数查找问题行

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)';

在我们这里需要对xxxx表格进行处理

3、然后再执行pg_dump命令

详细分析可见:https://www.postgresql.org/message-id/54889986.3000308%40gmail.com

案例二:pgclog因断电文件损坏

pg_clog损坏

报错信息:Could not read from file ""pg_clog/0646"" at offset 243287

服务器异常断电,这台因为是测试库,所以没备份以及备库(所以对于dba来说备份就是生命啊,不管是测试库还是生产库一定要做好备份)

  1. 对数据库进行全库物理备份(为之后操作做保险)

  2. 用dd进行伪造这个数据块(数据块伪造全部提交),并且更改权限

    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_clog/0646

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

案例三:toast表损坏

missing chunk number x for toast value x in pg_toast_x

某张表关联的toast表发现数据损坏

解决方案引自:http://m.2cto.com/database/201802/720718.html

1、定位是哪张表的toast有问题:

select 2619::regclass;
   regclass
--------------
 pg_statistic

2、找到哪个表有问题后,先对该表做一下简单的修复

REINDEX table pg_toast.pg_toast_2619;
REINDEX table pg_statistic;
VACUUM ANALYZE pg_statistic;

3、定位该表中损坏的数据行。执行

DO $$
declare
  v_rec record;
BEGIN    
  for v_rec in SELECT * FROM pg_statistic loop
    raise notice ‘Parameter is:‘, v_rec.ctid;
    raise notice ‘Parameter is:’, v_rec;
  end loop;
END;
$$
LANGUAGE plpgsql;

4、将第3步中定位的记录删除:

delete from pg_statistic where ctid ='(50,3)';

5、重复执行第3,4步,直到全部有问题的记录被清除。

6、至此,toast问题就解决完了,解决之后,对数据库进行一次完整的维护或者索引重建。

其实一般来说,数据库会根据归档或者wal去自行将postgres中未提交事务进行回滚操作,笔者这个环境当时是因为缺失了归档,所以只能手动将混乱数据进行删除。

最后,笔者想说,很多情况下都是因为没有一个靠谱的备份而导致很多问题,所以建议大家不管什么情况,备份为先,检查备份很重要!

转载自:

http://blog.sina.com.cn/s/blog\_67d069a90102vibc.html

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Java修道之路,问鼎巅峰,我辈代码修仙法力齐天
<center<fontcolor00FF7Fsize5face"黑体"代码尽头谁为峰,一见秃头道成空。</font<center<fontcolor00FF00size5face"黑体"编程修真路破折,一步一劫渡飞升。</font众所周知,编程修真有八大境界:1.Javase练气筑基2.数据库结丹3.web前端元婴4.Jav
Easter79 Easter79
2年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这