认识PostgreSQL|认识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
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
【认识PostgreSQL|认识PostgreSQL WAL(1)】
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: Heaplen (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)
推荐阅读
- dubbo基本认识
- “留一手”的误区认识
- 学习基金第五课:认识巴菲特赌输了的指数基金|学习基金第五课:认识巴菲特赌输了的指数基金 2018-10-12
- 32号-1.21-认识我的情绪
- 毛_草稿
- 认识职业发展
- 闰蜜
- 这五种盐城打桩机,你认识几种
- 认识图表
- 【思维导图实战派】刻意练习计划“遇见……”|【思维导图实战派】刻意练习计划“遇见……” 1/300 人教版数学五下第三单元《正方体和长方体的认识》