PostgreSQL数据同步利器：wal2json让变更捕获变得简单

【免费下载链接】wal2json JSON output plugin for changeset extraction 项目地址: https://gitcode.com/gh_mirrors/wa/wal2json

在当今数据驱动的时代，PostgreSQL作为一款强大的开源关系型数据库，被广泛应用于各种业务场景。而wal2json作为一款高效的PostgreSQL变更捕获插件，能够将数据库的写入操作日志（WAL）转换为JSON格式，为数据同步、实时分析等需求提供了极大的便利。本文将为您详细介绍wal2json的核心功能、安装配置方法以及实际应用场景，帮助您轻松掌握这一PostgreSQL数据同步利器。

wal2json简介：PostgreSQL变更捕获的终极解决方案 🚀

wal2json是PostgreSQL的一个逻辑解码输出插件，它能够访问INSERT和UPDATE产生的元组，并且根据配置的副本标识，还可以访问UPDATE/DELETE的旧行版本。通过wal2json，用户可以使用流协议（逻辑复制槽）或特殊的SQL API来消费这些变更数据。

wal2json提供了两种输出格式：

• 格式版本1：每个事务生成一个JSON对象，包含所有新旧元组，还可以选择包含事务时间戳、模式限定、数据类型和事务ID等属性。
• 格式版本2：每个元组生成一个JSON对象，还可以选择包含事务开始和结束的JSON对象，以及各种可选属性。

快速安装：三种简单方法搞定wal2json 🛠️

方法一：通过系统包管理器安装

如果您使用的是Red Hat/CentOS系统，可以直接通过yum命令安装：

sudo yum install wal2json_17

对于Debian/Ubuntu系统，则可以使用apt-get命令：

sudo apt-get install postgresql-17-wal2json

方法二：从Git仓库编译安装

如果您需要获取最新的修复和功能，可以克隆Git仓库进行编译安装。首先克隆仓库：

git clone https://gitcode.com/gh_mirrors/wa/wal2json

然后进入目录，设置PostgreSQL路径并编译安装：

cd wal2json
export PATH=/path/to/postgresql/bin:$PATH
make
make install

方法三：Windows系统安装

在Windows系统上，可以将wal2json目录放在PostgreSQL的contrib目录下，修改contrib的Makefile（SUBDIRS变量），然后按照Windows源代码安装说明进行编译。或者，编辑wal2json.vcxproj文件，将c:\pg\17更改为PostgreSQL的前缀目录，用MS Visual Studio编译后，将wal2json.dll复制到pg_config --pkglibdir目录。

配置指南：轻松开启PostgreSQL变更捕获 📝

postgresql.conf配置

要使用wal2json，需要在postgresql.conf中至少设置以下两个参数：

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

注意：max_replication_slots和max_wal_senders参数仅在PostgreSQL 9.4、9.5和9.6版本中需要设置，在10及以上版本中使用默认值即可。

修改参数后，需要重启PostgreSQL服务使配置生效。

wal2json参数说明

wal2json提供了丰富的参数来定制输出格式，以下是一些常用参数：

• include-xids：是否在每个变更集中添加xid，默认值为false。
• include-timestamp：是否在每个变更集中添加时间戳，默认值为false。
• include-schemas：是否在每个变更中添加模式，默认值为true。
• include-types：是否在每个变更中添加类型，默认值为true。
• pretty-print：是否为JSON结构添加空格和缩进，默认值为false。
• format-version：定义使用哪种格式，默认值为1。
• actions：定义要发送哪些操作，默认包括所有操作（insert、update、delete和truncate）。

实战示例：两种方式获取PostgreSQL变更数据 🌟

方式一：使用pg_recvlogical工具

首先，在pg_hba.conf中添加复制连接规则（PostgreSQL 9.4、9.5和9.6）：

local    replication     myuser                     trust

对于PostgreSQL 10及以上版本：

local    mydatabase      myuser                     trust

然后创建并启动逻辑复制槽：

pg_recvlogical -d postgres --slot test_slot --create-slot -P wal2json
pg_recvlogical -d postgres --slot test_slot --start -o pretty-print=1 -o add-msg-prefixes=wal2json -f -

在另一个终端中执行数据库操作，例如：

CREATE TABLE table1_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
INSERT INTO table1_with_pk (b, c) VALUES('Backup and Restore', now());
COMMIT;

此时，在pg_recvlogical终端中可以看到类似以下的输出：

{
    "change": [
        {
            "kind": "insert",
            "schema": "public",
            "table": "table1_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [1, "Backup and Restore", "2023-10-01 10:00:00.123456"]
        }
    ]
}

方式二：使用SQL函数

创建逻辑复制槽：

SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

执行数据库操作后，通过以下SQL获取变更数据：

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1', 'add-msg-prefixes', 'wal2json');

使用完毕后，删除复制槽：

SELECT 'stop' FROM pg_drop_replication_slot('test_slot');

高级应用：自定义wal2json输出 🎨

过滤表和消息

wal2json提供了灵活的过滤功能，可以通过filter-tables和add-tables参数来包含或排除特定表的变更。例如，只包含public模式下的table1表：

pg_recvlogical -d postgres --slot test_slot --start -o add-tables=public.table1 -f -

对于消息，可以使用filter-msg-prefixes和add-msg-prefixes参数来过滤。例如，只包含前缀为wal2json的消息：

pg_recvlogical -d postgres --slot test_slot --start -o add-msg-prefixes=wal2json -f -

格式版本2的优势

格式版本2相比版本1有一些优势，例如每个元组生成一个JSON对象，便于流式处理。使用格式版本2的示例：

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'format-version', '2');

输出类似于：

{"action":"I","schema":"public","table":"table1_with_pk","columns":[{"name":"a","type":"integer","value":1},{"name":"b","type":"character varying(30)","value":"Backup and Restore"},{"name":"c","type":"timestamp without time zone","value":"2023-10-01 10:00:00.123456"}]}

常见问题与解决方案 ❓

问题1：更新没有主键的表时没有输出

这是因为wal2json需要主键或副本标识来捕获UPDATE和DELETE操作。可以为表添加主键，或者设置副本标识：

ALTER TABLE table_without_pk REPLICA IDENTITY FULL;

问题2：JSON输出中数字类型有精度问题

可以使用numeric-data-types-as-string参数，将数字数据类型以字符串形式输出：

pg_recvlogical -d postgres --slot test_slot --start -o numeric-data-types-as-string=1 -f -

问题3：需要包含事务相关信息

可以使用include-xids和include-timestamp参数来包含事务ID和时间戳：

pg_recvlogical -d postgres --slot test_slot --start -o include-xids=1 -o include-timestamp=1 -f -

总结：wal2json——PostgreSQL数据同步的得力助手 🚀

wal2json作为一款功能强大的PostgreSQL变更捕获插件，为数据同步、实时分析、数据备份等场景提供了高效、灵活的解决方案。通过本文的介绍，您已经了解了wal2json的安装配置、使用方法和高级特性。无论是通过pg_recvlogical工具还是SQL函数，都可以轻松获取PostgreSQL的变更数据，并根据实际需求进行定制化配置。

如果您正在寻找一种简单、高效的PostgreSQL数据变更捕获方案，不妨尝试wal2json，相信它会成为您数据同步工作中的得力助手！

想要了解更多关于wal2json的详细信息，可以查看项目中的README.md文件，其中包含了更全面的参数说明和示例。同时，sql目录下的各种测试脚本，如sql/insert1.sql和sql/update1.sql，也可以帮助您更好地理解wal2json的使用方法。

【免费下载链接】wal2json JSON output plugin for changeset extraction 项目地址: https://gitcode.com/gh_mirrors/wa/wal2json