zero_damaged_pages 隐含参数，处理磁盘页损坏(先占位)-CSDN博客

本文链接：https://blog.csdn.net/ctypyb2002/article/details/118567591

文章描述在CentOS7.6系统上安装并管理PostgreSQL12数据库的过程，包括创建临时表、插入数据，以及使用`dd`命令意外损坏数据页后的数据恢复尝试。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

os: centos 7.6
db: postgresql 12

版本

# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core) 
# 
# yum list installed |grep -i postgre
postgresql12.x86_64                12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-contrib.x86_64        12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-devel.x86_64          12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-docs.x86_64           12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-libs.x86_64           12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-llvmjit.x86_64        12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg12  
postgresql12-odbc-debuginfo.x86_64 12.02.0000-1PGDG.rhel7              @pgdg-common
postgresql12-plperl.x86_64         12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-plpython.x86_64       12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-plpython3.x86_64      12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-pltcl.x86_64          12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-server.x86_64         12.7-1PGDG.rhel7                    @pgdg12  
postgresql12-tcl.x86_64            2.7.5-1.rhel7                       @pgdg12  
postgresql12-test.x86_64           12.7-1PGDG.rhel7                    @pgdg12

创建初始化表

pgbenchdb=# create table tmp_t0(
id   int8,
name varchar(100)
);

pgbenchdb=# insert into tmp_t0 select id,md5(id::varchar) from generate_series(1,1000000) as id;

pgbenchdb=# checkpoint;

pgbenchdb=# select count(1) from tmp_t0;
  count  
---------
 1000000
(1 row)

pgbenchdb=# select pg_relation_filepath('public.tmp_t0'::regclass);
 pg_relation_filepath 
----------------------
 base/16384/40960
(1 row)

dd命令损坏数据页

$ cd $PGDATA/base/16384
$ pwd
/var/lib/pgsql/12/data/base/16384

$ ls -l |grep -i 40960
-rw------- 1 postgres postgres  76562432 Jul  8 11:07 40960
-rw------- 1 postgres postgres     40960 Jul  8 11:07 40960_fsm

$ dd if=/dev/zero of=/var/lib/pgsql/12/data/base/16384/40960 bs=512 count=100

$ ls -l |grep -i 40960
-rw------- 1 postgres postgres    409600 Jul  8 11:13 40960
-rw------- 1 postgres postgres     40960 Jul  8 11:07 40960_fsm

再次查询

pgbenchdb=# select count(1) from tmp_t0;
 count 
-------
  5350
(1 row)

pgbenchdb=# checkpoint;

pgbenchdb=# select count(1) from tmp_t0;
  count  
---------
 1000000
(1 row)

执行了 checkpoint 后发现又是100w条数据，怀疑是从 wal恢复。

dd命令损坏数据页2

pgbenchdb=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 00000001000000000000005C
(1 row)