Use the wal2json extension to export logical log records as a file in the JSON format - ApsaraDB RDS

This topic describes how to use the wal2json extension provided by ApsaraDB RDS for PostgreSQL to export logical log records as a file in JSON format.

Prerequisites

Your RDS instance runs PostgreSQL 10 or later.
Note
The extension is not supported for RDS instances that run PostgreSQL 16 or 17.
The wal_level parameter is set to logical. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

Background information

wal2json is a logical decoding extension. You can use the extension to access tuples generated by using the INSERT and UPDATE statements and parse log records produced by write-ahead logging (WAL).

The wal2json extension produces a JSON object for each transaction. All new and old tuples are available in the JSON object. Other options that include properties such as transaction timestamp, schema-qualified, data type, and transaction ID are also available in the JSON object. For more information, see Execute SQL statements to obtain JSON objects.

Execute SQL statements to obtain JSON objects

Use DMS to log on to an RDS instance.

Execute the following statements to create a table and initialize the wal2json extension:

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');

Execute the following statements 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;

Execute the following statement to export logical log records as a file in JSON format:
```
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
```
Note
If you want to stop exporting logical log records and release the resources, you can execute the following statement:
```
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
```