PostgreSQL的WAL日志管理

wal日志介绍

wal日志即write ahead log预写式日志,简称wal日志。wal日志可以说是PostgreSQL中十分重要的部分,相当于oracle中的redo日志。
当数据库中数据发生变更时:
(1)change发生时:先要将变更后内容计入wal buffer中,再将变更后的数据写入data buffer;
(2)commit发生时:wal buffer中数据刷新到磁盘;
(3)checkpoint发生时:将所有data buffer刷新的磁盘。

查看 online wal日志

postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
00000001000000000000000C | 16777216 | 2021-11-23 15:13:51+08
00000001000000000000000B | 16777216 | 2021-11-23 15:13:51+08
00000001000000000000000E | 16777216 | 2021-11-23 15:13:52+08
00000001000000000000000D | 16777216 | 2021-11-23 15:13:53+08
00000001000000000000000A | 16777216 | 2021-12-07 10:39:16+08
(5 rows)


postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
00000001000000000000000A
(1 row)


postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/A0001C0
(1 row)

wal日志大小设置

max_wal_size

在自动 WAL检查点之间允许WAL 增长到的最大尺寸。这是一个软限制,在特殊的情况 下 WAL 尺寸可能会超过max_wal_size, 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。

PostgreSQL的WAL日志管理

wal日志切换

postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/A0001D8
(1 row)

postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
00000001000000000000000C | 16777216 | 2021-11-23 15:13:51+08
00000001000000000000000E | 16777216 | 2021-11-23 15:13:52+08
00000001000000000000000D | 16777216 | 2021-11-23 15:13:53+08
00000001000000000000000A | 16777216 | 2021-12-08 11:23:21+08
00000001000000000000000B | 16777216 | 2021-12-08 11:23:21+08

查看wal日志内容

pg_waldump 00000001000000000000000B

两个commit之间为事务  XID表示事务编号

PostgreSQL的WAL日志管理

开启归档

show archive_command ;

cd $PGDATA
[postgres@VM-0-9-centos pgdata]$ cat postgresql.conf |grep archive_mode
#archive_mode = off # enables archiving; off, on, or always
[postgres@VM-0-9-centos pgdata]$ vi postgresql.conf

archive_mode = on
archive_command = 'cp %p /opt/arch/%f'

配置完成之后,如下

PostgreSQL的WAL日志管理

PostgreSQL的WAL日志管理

再将库重启生效

pg_ctl -D /opt/pgdata restart &

测试归档开启之后,有没有将wal日志拷贝到归档目录之中

测试:

当前正在使用的wal日志为00000001000000000000000C,

将wal日志切换,切换后为00000001000000000000000D。

查看wal日志列表
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
00000001000000000000000E | 16777216 | 2021-11-23 15:13:52+08
00000001000000000000000D | 16777216 | 2021-11-23 15:13:53+08
00000001000000000000000F | 16777216 | 2021-12-08 11:23:21+08
00000001000000000000000B | 16777216 | 2021-12-08 12:21:49+08
00000001000000000000000C | 16777216 | 2021-12-08 12:21:53+08
(5 rows)

进行切换
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/C000078
(1 row)

再次查看wal日志列表
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
00000001000000000000000E | 16777216 | 2021-11-23 15:13:52+08
00000001000000000000000F | 16777216 | 2021-12-08 11:23:21+08
00000001000000000000000B | 16777216 | 2021-12-08 12:21:49+08
00000001000000000000000C | 16777216 | 2021-12-08 12:22:08+08
00000001000000000000000D | 16777216 | 2021-12-08 12:22:14+08

(5 rows)
发表评论

相关文章