10_PostgreSQL full_page_writes 关闭时page检验问题_bug or not?

内容概述

在PG数据库中关闭full_page_writes特性，破坏page后半部分，数据库正常启动，存在数据丢失，数据不一致的情况。

关闭full_page_writes特性，破坏page后半部分

参数修改

[postgres@pgsql ~]$ psql
psql (15.0)
Type "help" for help.

postgres=# show full_page_writes ;
 full_page_writes 
------------------
 on
(1 row)

postgres=# alter system set full_page_writes=off;
ALTER SYSTEM
postgres=# show full_page_writes ;               
 full_page_writes 
------------------
 on
(1 row)

postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# show full_page_writes ; 
 full_page_writes 
------------------
 off
(1 row)

postgres=# 

异常模拟

### step1. 创建table t1切换日志并查询当前日志
drop table t1;
create table t1(a1 varchar);
select pg_switch_wal();
insert into t1 values('11111111');
checkpoint;
SELECT pg_walfile_name(pg_current_wal_lsn());
postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000002
(1 row)

postgres=# 
### step2. 查询表的路径
select pg_relation_filepath('t1');
postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/5/16388
(1 row)

postgres=#
### step3. 插入一条记录，并生成新的日志文件
select pg_switch_wal();
SELECT pg_walfile_name(pg_current_wal_lsn());
postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000003
(1 row)

postgres=# insert into t1 values(22222222);
### stop4. 关闭数据库
[postgres@enmo pgwal]$ pg_ctl stop -m immediate

### step5. 检查数据文件中信息
[postgres@enmo pgwal]$ ls -ltr $PGDATA/base/5/16388
-rw------- 1 postgres dba 8192 Oct 26 21:39 /u01/pg15/pgdata/base/5/16388
[postgres@enmo pg15]$ dd if=$PGDATA/base/5/16388 bs=8192 skip=0 count=1 |hexdump -C
00000000  00 00 00 00 68 00 00 02  00 00 00 00 1c 00 d8 1f  |....h...........|
00000010  00 20 04 20 00 00 00 00  d8 9f 42 00 00 00 00 00  |. . ......B.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001fd0  00 00 00 00 00 00 00 00  d5 02 00 00 00 00 00 00  |................|
00001fe0  00 00 00 00 00 00 00 00  01 00 01 00 02 08 18 00  |................|
00001ff0  13 31 31 31 31 31 31 31  31 00 00 00 00 00 00 00  |.11111111.......|  <--数据文件中只有“11111111”
00002000
[postgres@enmo pg15]$

### step6. 检查日志文件中信息

[postgres@enmo pg15]$ dd if=$PGDATA/pg_wal/000000010000000000000003 bs=8192 skip=0 count=1 |hexdump -C
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.6352e-05 s, 225 MB/s
00000000  10 d1 06 00 01 00 00 00  00 00 00 03 00 00 00 00  |................|
00000010  00 00 00 00 00 00 00 00  36 a6 32 24 66 3a 59 63  |........6.2$f:Yc|
00000020  00 00 00 01 00 20 00 00  32 00 00 00 00 00 00 00  |..... ..2.......|
00000030  50 b2 01 02 00 00 00 00  10 08 00 00 24 7e 48 a5  |P...........$~H.|
00000040  ff 18 00 00 00 00 00 00  00 00 00 f3 95 bf e1 02  |................|
00000050  00 00 e1 02 00 00 e0 02  00 00 00 00 00 00 00 00  |................|
00000060  40 00 00 00 e1 02 00 00  28 00 00 03 00 00 00 00  |@.......(.......|
00000070  00 0a 00 00 dc 3c b3 1d  00 20 0f 00 7f 06 00 00  |.....<... ......|
00000080  05 00 00 00 00 40 00 00  00 00 00 00 ff 03 01 00  |.....@..........|
00000090  02 08 18 00 13 32 32 32  32 32 32 32 32 02 00 00  |.....22222222...|   <--WAL文件中只有“22222222”
000000a0  22 00 00 00 e1 02 00 00  60 00 00 03 00 00 00 00  |".......`.......|
000000b0  00 01 00 00 06 89 b3 07  ff 08 53 61 b6 17 ef 8e  |..........Sa....|
000000c0  02 00 00 00 00 00 00 00  32 00 00 00 00 00 00 00  |........2.......|
000000d0  a0 00 00 03 00 00 00 00  10 08 00 00 34 1b 26 29  |............4.&)|
000000e0  ff 18 00 00 00 00 00 00  00 00 00 f3 95 bf e2 02  |................|
000000f0  00 00 e2 02 00 00 e1 02  00 00 00 00 00 00 00 00  |................|
00000100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000
[postgres@enmo pg15]$ 

### step7. partially破坏page
[postgres@enmo pgwal]$ dd if=/dev/zero of=$PGDATA/base/5/16388 bs=512 skip=0 seek=8 count=8 conv=notrunc
8+0 records in
8+0 records out
4096 bytes (4.1 kB) copied, 4.9061e-05 s, 83.5 MB/s
[postgres@enmo pgwal]$ 

### step8. 再次检查数据文件中信息
[postgres@pgsql ~]$ dd if=$PGDATA/base/5/16388 bs=8192 skip=0 seek=0 count=1 |hexdump -C
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.7357e-05 s, 219 MB/s
00000000  00 00 00 00 68 00 00 02  00 00 00 00 1c 00 d8 1f  |....h...........|
00000010  00 20 04 20 00 00 00 00  d8 9f 42 00 00 00 00 00  |. . ......B.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000
[postgres@pgsql ~]$

### step 9. 启动数据库
[postgres@pgsql ~]$ pg_ctl start
waiting for server to start....2022-10-27 06:25:22.026 CST [1684] LOG:  starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-10-27 06:25:22.028 CST [1684] LOG:  listening on IPv6 address "::1", port 5432
2022-10-27 06:25:22.028 CST [1684] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-10-27 06:25:22.029 CST [1684] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-10-27 06:25:22.031 CST [1687] LOG:  database system was interrupted; last known up at 2022-10-27 06:15:33 CST
2022-10-27 06:25:22.037 CST [1687] LOG:  database system was not properly shut down; automatic recovery in progress
2022-10-27 06:25:22.038 CST [1687] LOG:  redo starts at 0/2000090
2022-10-27 06:25:22.038 CST [1687] LOG:  invalid record length at 0/30000C8: wanted 24, got 0
2022-10-27 06:25:22.038 CST [1687] LOG:  redo done at 0/30000A0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2022-10-27 06:25:22.039 CST [1685] LOG:  checkpoint starting: end-of-recovery immediate wait
2022-10-27 06:25:22.041 CST [1685] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.003 s; sync files=3, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB
2022-10-27 06:25:22.042 CST [1684] LOG:  database system is ready to accept connections
 done
server started
[postgres@pgsql ~]$

### step 10. 检查数据一致性
[postgres@pgsql ~]$ psql
psql (15.0)
Type "help" for help.

postgres=# select * from t1;
    a1    
----------
 22222222
(1 row)

postgres=#