认识PostgreSQL WAL（1）

WAL is an acronym of Write Ahead Logging, which is a protocol or a rule to write both changes and actions into a transaction log, whereas in PostgreSQL, WAL is an acronym of Write Ahead Log. There the term is used as synonym of transaction log, and also used to refer to an implemented mechanism related to writing action to a transaction log (WAL).

WAL日志作用

wal contain a history of all changes made to the database.
wal files are used by
– Recovery
– Incremental Backup and Point In Time Recovery
– Replication
Every change made to the database is written to wal log file
before it is written to the data file.
The wal buffer is flushed to the wal log file when a COMMIT
is issued.
A background log writer process to flush wal in case if the
database setting is such that wal should be flushed in a batch.
wal is not required for temporary tables

首先我们先直观的了解wal log都记录了什么东西，我们要用到pg_waldump 工具

session1:

limingyao=# create table tb1(id int);
CREATE TABLE
limingyao=# insert into tb1 select * from generate_series(1,100);
INSERT 0 100
limingyao=# select ctid, * from tb1;
limingyao=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name
--------------------------
 00000001000000000000005A
(1 row)

limingyao=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification
--------------------------+----------+------------------------
 00000001000000000000005A | 16777216 | 2018-09-12 11:31:48+08
(34 rows)

limingyao=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/5A03F038
(1 row)

limingyao=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification
--------------------------+----------+------------------------
 00000001000000000000005A | 16777216 | 2018-09-12 11:32:02+08
 00000001000000000000005B | 16777216 | 2018-09-12 11:32:09+08
(34 rows)

最后一个wal file 00000001000000000000005B 就是我们最新生成的，打开另一个terminal

session2:

pg_waldump -f PGDATA/pg_wal/00000001000000000000005B

session1:

limingyao=# SELECT pg_relation_filepath('tb1');
 pg_relation_filepath
----------------------
 base/16384/57579
(1 row)

limingyao=# select ctid, * from tb1 limit 10;
  ctid  | id
--------+----
 (0,1)  |  1
 (0,2)  |  2
 (0,3)  |  3
 (0,4)  |  4
 (0,5)  |  5
 (0,6)  |  6
 (0,7)  |  7
 (0,8)  |  8
 (0,9)  |  9
 (0,10) | 10
(10 rows)

limingyao=# delete from tb1 where id = 1;
DELETE 1

session2:

rmgr: Heap        len (rec/tot):     59/  3683, tx:      51999, lsn: 0/5B000140, prev 0/5B000108, desc: DELETE off 1 KEYS_UPDATED , blkref #0: rel 1663/16384/57579 blk 0 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:      51999, lsn: 0/5B000FA8, prev 0/5B000140, desc: COMMIT 2018-09-12 11:37:39.029272 CST

我们观察当删除一条记录的时候 wal 记录生成了两条log
rmgr: Heap 说明操作资源类型
tx: 事务id
lsn： lsn id (select * from page_head(get_raw_page('tb1', 0))) 看到
prev：上一条lsn id
desc：DELETE off 1 KEYS_UPDATED , blkref #0: rel 1663/16384/57579 blk 0 FPW
blkref： 1663/16384/57579 说明具体的表（ SELECT pg_relation_filepath('tb1');）
blk 0 表示表的第一页
off 1 ctid = （0，1）