Use logical decoding to output files in JSON format-ApsaraDB RDS(RDS)-阿里云帮助中心

RDS for PostgreSQL provides the wal2json plugin to output logical log files in JSON format.

For inquiries, discussions, or feedback, join the RDS for PostgreSQL plugin discussion group on DingTalk (ID: 103525002795) for more information about plugins.

Prerequisites

The instance runs RDS for PostgreSQL 10 or later.

For RDS for PostgreSQL 16, the minor version must be 20240830 or later.

Note
RDS for PostgreSQL 17 does not currently support this plugin.
The wal_level instance parameter must be set to logical. For more information, see Set instance parameters.

Background information

wal2json is a logical decoding plugin that accesses tuples generated by INSERT and UPDATE operations by parsing the WAL.

The wal2json plugin generates a JSON object for each transaction, which contains all new and old tuples. You can also include additional properties such as the transaction timestamp, schema, data types, and transaction ID. For more information, see Get JSON objects using SQL.

Get JSON objects using SQL

Log on to an RDS for PostgreSQL database using DMS.

Run the following commands to create tables and initialize the plugin.

CREATE TABLE table2_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
CREATE TABLE table2_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT);
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

Note

The wal2json plugin does not need to be created by using CREATE EXTENSION. Instead, the plugin is loaded for use through a logical replication slot.

Run the following commands to modify the data.

BEGIN;
INSERT INTO table2_with_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table2_with_pk (b, c) VALUES('Tuning', now());
INSERT INTO table2_with_pk (b, c) VALUES('Replication', now());
DELETE FROM table2_with_pk WHERE a < 3;
INSERT INTO table2_without_pk (b, c) VALUES(2.34, 'Tapir');
UPDATE table2_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

Run the following command to output the log data in JSON format.
```
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
```
The command returns WAL log data in JSON format. This data details the insert operation on the target table with fields such as "kind": "insert", "schema": "public", "table": "table2_with_pk", "columnnames": ["a", "b", "c"], and "columntypes": ["integer", "character varying(30)", "timestamp without time zone"].
Note
To stop the output and release resources, run the following command:
```
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
```