逻辑复制使用复制槽和解码扩展插件来解析数据库中WAL日志记录的操作。
- 参数设置
wal_level=logical 修改之后需要重启PG数据库
max_replication_slots 默认为10个
- 创建逻辑复制槽
创建复制槽名称为 logical_slot，并且使用插件 test_decoding 进行逻辑解码
postgres=# SELECT pg_create_logical_replication_slot('logical_slot', 'test_decoding');
2019-04-05 12:34:12.053 CST [18658] LOG: logical decoding found consistent point at 0/1614040
2019-04-05 12:34:12.053 CST [18658] DETAIL: There are no running transactions.
2019-04-05 12:34:12.053 CST [18658] STATEMENT: SELECT pg_create_logical_replication_slot('logical_slot', 'test_decoding');
pg_create_logical_replication_slot
------------------------------------
(logical_slot,0/1614078)
(1 row)
- 查看当前逻辑复制槽的状态
postgres=# select * from pg_replication_slots where slot_name='logical_slot';
-[ RECORD 1 ]-------+--------------
slot_name | logical_slot
plugin | test_decoding
slot_type | logical
datoid | 12913
database | postgres
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 480
restart_lsn | 0/1614040
confirmed_flush_lsn | 0/1614078
- 解析 DML 和 DDL 语句
4.1 解析 DML 语句
postgres=# insert into sgw_dml_logic values(123);
INSERT 0 1
postgres=# SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
2019-04-05 12:45:40.150 CST [18658] LOG: starting logical decoding for slot "logical_slot"
2019-04-05 12:45:40.150 CST [18658] DETAIL: Streaming transactions committing after 0/1614120, reading WAL from 0/1614120.
2019-04-05 12:45:40.150 CST [18658] STATEMENT: SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
2019-04-05 12:45:40.150 CST [18658] LOG: logical decoding found consistent point at 0/1614120
2019-04-05 12:45:40.150 CST [18658] DETAIL: There are no running transactions.
2019-04-05 12:45:40.150 CST [18658] STATEMENT: SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
lsn | xid | data
-----------+-----+--------------------------------------------------
0/1614200 | 481 | BEGIN 481
0/1614200 | 481 | table public.sgw_dml_logic: INSERT: f1[integer]:123
0/16142D0 | 481 | COMMIT 481
(3 rows)
4.2 解析 DDL 语句
postgres=# create table sgw_ddl_logic(f1 varchar);
CREATE TABLE
postgres=# SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
2019-04-05 12:51:19.270 CST [18658] LOG: starting logical decoding for slot "logical_slot"
2019-04-05 12:51:19.270 CST [18658] DETAIL: Streaming transactions committing after 0/1614308, reading WAL from 0/16141C8.
2019-04-05 12:51:19.270 CST [18658] STATEMENT: SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
2019-04-05 12:51:19.270 CST [18658] LOG: logical decoding found consistent point at 0/16141C8
2019-04-05 12:51:19.270 CST [18658] DETAIL: There are no running transactions.
2019-04-05 12:51:19.270 CST [18658] STATEMENT: SELECT * FROM pg_logical_slot_peek_changes('logical_slot', NULL, NULL);
lsn | xid | data
-----------+-----+------------
0/1614418 | 482 | BEGIN 482
0/1635CA0 | 482 | COMMIT 482
(2 rows)
- 删除逻辑复制槽
postgres=# SELECT pg_drop_replication_slot('logical_slot');
pg_drop_replication_slot
(1 row)
pg_logical_slot_get_changes和pg_logical_slot_peek_changes 的区别：
前者消费这些changes，而后者以只读的方式查看，而不会remove。
逻辑解码插件：
test_decoding – the default plugin;
wal2json – shows the changes in JSON format ;
decoder_raw – reconstructs the query that has applied the change.
decoderbufs PostgreSQL逻辑解码器输出插件，用于将数据作为协议缓冲区传送
ali_decoding 阿里开源的逻辑解码插件
- 文档引用
walzjoson：https://www.helplib.com/GitHub/article_122007
decoderbufs：https://www.helplib.com/GitHub/article_115924
ali_decoding: https://www.cnblogs.com/kuang17/p/10136365.html
