PostgreSQL WAL日志解析与应用 王硕 山东瀚高基础软件股份有限公司 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 CONTENTS Part 01 Part 02 Part 03 WAL 日志简介 WAL 日志工作原理 利用 WAL 日志我们可以做什么？ 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 Part 01 WAL 日志简介 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会  Write Ahead Log Files • WAL 日志一般存储在$PGDATA/pg_xlog内，他们一般以类似于 000000010 LogId LogSeg • WAL 日志文件XLOG 文件是一个逻辑概念，每一个XLOG文件， 大小为4G(16*256) ，由256个segment组成； • Segment由2048个Block组成，其大小为16M ； • Block为WAL日志的最小单位， 其大小8k，由PageHeaderData 、 XlogRecord、 XLogRecData组成。

2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 简介 逻辑复制的功能是从PG的WAL日志中，读取数 据库更新信息，然后“翻译”（Decode）成逻 辑的形式，可发送到远程从库做数据同步。 PG WAL Other DB 2016Postgres中国用户大会 Postgres Conference China 2016 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 逻辑复制特点 它兼顾有基于触发器复制技术的灵活性 同时又有基于日志复制技术的高效性 它使用发布/订阅模型对选择性的数据 复制相对物理复制来说是非常方便 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 2016Postgres中国用户大会 Postgres Conference China 2016 中国用户大会 • WAL • DISK LOGICAL • understandably • application Slot • APPLY • Other SQL/tuple 逻辑解码 持久化WAL解析成便于理解的格式 不必知道内部细节 一个槽表示一个更改流 这些流可以根据需要更改成用户需要的状态

.................................................................. 927 28.3. Write-Ahead Logging (WAL) .................................................................... 927 28.4. Asynchronous Commit ................................ 928 28.5. WAL Configuration ................................................................................. 929 28.6. WAL Internals ................................. ......................... 2659 63.1. Generic WAL Records ............................................................................ 2659 63.2. Custom WAL Resource Managers ..........................

Logging (WAL) ............................................................... 880 28.4. Asynchronous Commit ........................................................................ 881 28.5. WAL Configuration Configuration ............................................................................ 882 28.6. WAL Internals ................................................................................... 885 29. Logical ............................ 2512 63.1. Generic WAL Records ...................................................................... 2512 63.2. Custom WAL Resource Managers .............................

.................................................................. 900 30.3. Write-Ahead Logging (WAL) .................................................................... 900 30.4. Asynchronous Commit ................................ 901 30.5. WAL Configuration ................................................................................. 902 30.6. WAL Internals ................................. ....................... 2582 65. Generic WAL Records ...................................................................................... 2585 66. Custom WAL Resource Managers ......................

Logging (WAL) ............................................................... 856 30.4. Asynchronous Commit ........................................................................ 857 30.5. WAL Configuration Configuration ............................................................................ 858 30.6. WAL Internals ................................................................................... 861 31. Logical ......................... 2447 65. Generic WAL Records ................................................................................. 2451 66. Custom WAL Resource Managers .........................

.................................................................. 858 30.3. Write-Ahead Logging (WAL) .................................................................... 858 30.4. Asynchronous Commit ................................ 859 30.5. WAL Configuration ................................................................................. 860 30.6. WAL Internals ................................. Estimation Functions ............................................................... 2484 63. Generic WAL Records ...................................................................................... 2487

Logging (WAL) ............................................................... 817 30.4. Asynchronous Commit ........................................................................ 818 30.5. WAL Configuration Configuration ............................................................................ 819 30.6. WAL Internals ................................................................................... 822 31. Logical Cost Estimation Functions ......................................................... 2350 63. Generic WAL Records ................................................................................. 2353 64.

.................................................................. 831 29.2. Write-Ahead Logging (WAL) .................................................................... 833 29.3. Asynchronous Commit ................................ 833 29.4. WAL Configuration ................................................................................. 835 29.5. WAL Internals ................................. Estimation Functions ............................................................... 2399 62. Generic WAL Records ...................................................................................... 2403

Logging (WAL) ............................................................... 790 29.3. Asynchronous Commit ........................................................................ 790 29.4. WAL Configuration Configuration ............................................................................ 791 29.5. WAL Internals ................................................................................... 794 30. Logical Cost Estimation Functions ......................................................... 2265 62. Generic WAL Records ................................................................................. 2269 63.