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。增加这个参数可能导致崩溃恢复所需的时间。
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表示事务编号
开启归档
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'
配置完成之后,如下
再将库重启生效
pg_ctl -D /opt/pgdata restart &
测试归档开启之后,有没有将wal日志拷贝到归档目录之中
测试:
当前正在使用的wal日志为00000001000000000000000C,
将wal日志切换,切换后为00000001000000000000000D。
观察到归档目录中,已经将00000001000000000000000C拷贝到归档目录中
查看wal日志列表标签:16777216,wal,PostgreSQL,08,WAL,2021,日志,12,pg From: https://blog.51cto.com/u_13874232/5751914
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)