PostgreSQL wal2json 逻辑解码变更流推送实践

--- title: PostgreSQL wal2json 逻辑解码变更流推送实践 keywords: wal2json, logical decoding, replication slot, pg_recvlogical, pg_logical_slot_get_changes description: 使用 wal2json 输出插件创建逻辑复制槽并推送变更流，支持命令行与 SQL 方式读取。 tags: - PostgreSQL - logical decoding - pg_logical_slot_get_changes - pg_recvlogical - replication slot - wal2json - 数据库 - 逻辑解码 categories: - 文章资讯 - 技术教程 --- 前置配置（postgresql.conf）： ``` wal_level = logical max_replication_slots = 10 max_wal_senders = 10 ``` 创建 wal2json 复制槽（SQL）： ``` SELECT pg_create_logical_replication_slot('slot_wal2json', 'wal2json'); ``` 读取变更（SQL 方式）： ``` SELECT data FROM pg_logical_slot_peek_changes('slot_wal2json', NULL, NULL, 'pretty-print', '1'); ``` 命令行流式读取： ``` pg_recvlogical -d app -U replicator --slot slot_wal2json --start -o pretty-print=1 -o include-xids=0 -f - ``` 示例变更（插入触发）： ``` INSERT INTO public.orders(id, amount) VALUES (1, 9.99); ``` 清理复制槽： ``` SELECT pg_drop_replication_slot('slot_wal2json'); ```