Use logical decoding to output files in JSON format - ApsaraDB RDS

RDS PostgreSQL provides the wal2json plugin, which outputs logical log files in JSON format for easy viewing.

Prerequisites

The instance is RDS PostgreSQL 10 or later.
For RDS PostgreSQL 16, the minor version must be 20240830 or later.
Note
RDS PostgreSQL 17 does not currently support this plugin.
The instance parameter wal_level is set to logical. For more information, see Set instance parameters.

Background information

wal2json is a logical decoding plugin that lets you access tuples generated by INSERT and UPDATE operations and parse the content in WAL.

The wal2json plugin generates a JSON object for each transaction. The JSON object provides all new/old tuples, and additional options can include transaction timestamps, schema qualifications, data types, transaction IDs, and other properties. For more information, see Obtain JSON objects through SQL.

Obtain JSON objects through SQL

Log on to an RDS database through 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 through CREATE EXTENSION. Instead, it is loaded through a logical replication slot for use.

Run the following commands to change 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 log information in JSON format.

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

解析日志

Note

To stop the output and release resources, run the following command:

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