PostgreSQL WAL日志解析与应用 王硕 山东瀚高基础软件股份有限公司 ## CONTENTS Part 01 WAL 日志简介 Part 02 WAL 日志工作原理 Part 03 利用 WAL 日志我们可以做什么？ ## Part 01 WAL 日志简介 www.highgo.com ## Write Ahead Log Files - WAL 日志一般存储在$ 8a5f37b8956629f5ab2ca83436c8eb/p4_1.jpg) - WAL 日志文件XLOG 文件是一个逻辑概念，每一个XLOG 文件，大小为4G(16*256)，由256个segment组成； • Segment由2048个Block组成，其大小为16M； - Block为WAL日志的最小单位，其大小8k，由PageHeaderData、XlogRecord、XLogRecData组成。 oldtuples standby oldtuples ## Part 02 WAL 日志工作原理 ## Write Ahead Log Files  ## Functions of WAL ## • XLogInsert 插入一条XLOG记录，记录

max_prepared_transactions 1945 max_resource_portals_per_transaction 1945 max_resource_ queues 1946 max_slot_wal_keep_size 1946 max_stack_depth 1947 max_statement_mem 1947 memory_spill_ratio 1947 optimizer random_page_cost 1962 readable_external_table_timeout 1962 repl_catchup_within_range 1963 wal_sender_timeout 1963 resource_cleanup_gangs_on_wait 1963 resource_select_only 1963 runaway_de verify_gpfdists_cert 1975 vmem_process_interrupt 1976 wait_for_replication_threshold 1976 wal_keep_segments 1976 wal_receiver_status_interval 1976 work_mem 1977 writable_external_table_bufsize 1977

Increase maintenance_work_mem .....364 14.4.6. Increase checkpoint_segments .....364 14.4.7. Disable WAL archival and streaming replication .....364 14.4.8. Run ANALYZE Afterwards .....364 14.4.9. Some 24.3. Continuous Archiving and Point-In-Time Recovery (PITR) ..... 521 24.3.1. Setting up WAL archiving ..... 522 24.3.2. Making a Base Backup ..... 524 24.3.3. Recovering using a Continuous Reliability.....580 29.2. Write-Ahead Logging (WAL).....581 29.3. Asynchronous Commit.....582 29.4. WAL Configuration.....583 29.5. WAL Internals.....586 30. Regression Tests.....587

Checksums ..... 880 28.3. Write-Ahead Logging (WAL) ..... 880 28.4. Asynchronous Commit ..... 881 28.5. WAL Configuration ..... 882 28.6. WAL Internals ..... 885 29. Logical Replication ..... 2509 63. Write Ahead Logging for Extensions ..... 2512 63.1. Generic WAL Records ..... 2512 63.2. Custom WAL Resource Managers ..... 2513 64. Built-in Index Access Methods ..... 2515 64 Top-level Object ..... 2604 69.2. Backup Manifest File Object ..... 2604 69.3. Backup Manifest WAL Range Object ..... 2605 Appendixes ..... 2606 A. PostgreSQL Error Codes ..... 2612 B. Date/Time

## 实现PostgreSQL逻辑复制实战 王青松 神州飞象(北京)数据科技有限公司 ## 简介 PG WAL Other DB 逻辑复制的功能是从PG的WAL日志中，读取数据库更新信息，然后“翻译”（Decode）成逻辑的形式，可发送到远程从库做数据同步。 ## 为什么要选择逻辑复制？  ## 自动导出快照 当使用流复制接口创建一个新的复制槽时， 将会自动产生一个快照。 ## 无DB限制 Pg的逻辑复制可以根据需求把WAL日志直接翻译成可以直接解析的SQL语句，并通过槽技术向不同DB进行传递，从而实现不同DB数据库的复制。 ## 如何自定义输出插件 我们所要实现以下几个回调函数： LogicalDecodeStartupCB

欢迎访问Greenplum中文社区：cn.greenplum.org ## Greenplum分布式事务和两阶段提交协议 ## Outline • 事务实现原理和Write Ahead Logging(WAL) • 分布式事务和两阶段提交的原理 Greenplum两阶段提交协议的实现 Greenplum两阶段提交协议的优化 ## 事务的属性:ACID |属性|含义|数据库系统的实现| |---|---|---| VACUUM”: zheap, in-place update ## 思考： 1. MySQL同样采用MVCC，事务恢复的时候为什么需要undo log？ 2. 出现新硬件(NVRAM)并不断得到广泛应用，WAL是否适合新硬件特点？ (业内的探索: CMU, VLDB 2016, Write-Behind Logging) ## V ersion Storage  • 分布式事务和两阶段提交的原理 Greenplum两阶段提交协议的实现 Greenplum两阶段提交协议的优化 ## 分布式事务 分布式事务，分布式环境下的事务，是一个典型的嵌套式事务，一个事务由多个工作节点的子事务组成。

Data Checksums ..... 927 28.3. Write-Ahead Logging (WAL) ..... 927 28.4. Asynchronous Commit ..... 928 28.5. WAL Configuration ..... 929 28.6. WAL Internals ..... 933 29. Logical Replication ... ..... 2655 63. Write Ahead Logging for Extensions ..... 2659 63.1. Generic WAL Records ..... 2659 63.2. Custom WAL Resource Managers ..... 2660 64. Built-in Index Access Methods ..... 2663 64 Top-level Object ..... 2756 69.2. Backup Manifest File Object ..... 2756 69.3. Backup Manifest WAL Range Object ..... 2757 Appendixes ..... 2758 A. PostgreSQL Error Codes ..... 2765 B. Date/Time

Checksums ..... 856 30.3. Write-Ahead Logging (WAL) ..... 856 30.4. Asynchronous Commit ..... 857 30.5. WAL Configuration ..... 858 30.6. WAL Internals ..... 861 31. Logical Replication Checks ..... 2446 64.6. Index Cost Estimation Functions ..... 2447 65. Generic WAL Records ..... 2451 66. Custom WAL Resource Managers ..... 2453 67. B-Tree Indexes ..... 2455 67.1. Introduction Top-level Object ..... 2546 77.2. Backup Manifest File Object ..... 2546 77.3. Backup Manifest WAL Range Object ..... 2547 I. Appendixes ..... 2548 A. PostgreSQL Error Codes ..... 2555 B. Date/Time

Checksums ..... 900 30.3. Write-Ahead Logging (WAL) ..... 900 30.4. Asynchronous Commit ..... 901 30.5. WAL Configuration ..... 902 30.6. WAL Internals ..... 905 31. Logical Replication Checks ..... 2581 64.6. Index Cost Estimation Functions ..... 2582 65. Generic WAL Records ..... 2585 66. Custom WAL Resource Managers ..... 2587 67. B-Tree Indexes ..... 2589 67.1. Introduction Top-level Object ..... 2685 77.2. Backup Manifest File Object ..... 2685 77.3. Backup Manifest WAL Range Object ..... 2686 Appendixes ..... 2687 A. PostgreSQL Error Codes ..... 2694 B. Date/Time

Reliability ..... 831 29.2. Write-Ahead Logging (WAL) ..... 833 29.3. Asynchronous Commit ..... 833 29.4. WAL Configuration ..... 835 29.5. WAL Internals ..... 837 30. Logical Replication Index Uniqueness Checks ..... 2398 61.6. Index Cost Estimation Functions ..... 2399 62. Generic WAL Records ..... 2403 63. B-Tree Indexes ..... 2405 63.1. Introduction ..... 2405 63.2. Behavior Top-level Object ..... 2484 72.2. Backup Manifest File Object ..... 2484 72.3. Backup Manifest WAL Range Object ..... 2485 II. Appendixes ..... 2486 A. PostgreSQL Error Codes ..... 2493 B. Date/Time