ApsaraDB RDS:Logical decoding (wal2json)

Prerequisites

Before you begin, make sure that:

• Your instance runs RDS PostgreSQL 10 or later.

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

  • RDS PostgreSQL 17 does not currently support this plugin.

• The wal_level parameter is set to logical. For instructions, see Set instance parameters.

How it works

wal2json is a logical decoding plugin that captures row-level changes from WAL for INSERT and UPDATE operations. For each transaction, it generates one JSON object containing all new and old tuples. The JSON object can optionally include transaction timestamps, schema qualifications, data types, and transaction IDs.

Unlike most PostgreSQL extensions, wal2json is not loaded with CREATE EXTENSION. Instead, it activates through a logical replication slot.

Read changes as JSON

This procedure creates a logical replication slot, makes some changes, and reads those changes as JSON using wal2json.

1. Log in to your RDS instance through DMS.

2. Create two test tables — one with a primary key, one without:

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

3. Create a logical replication slot using wal2json:

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

   The slot name test_slot is referenced in later steps to read and drop the slot.

4. Make some data changes:

   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;

5. Read the changes as pretty-printed JSON:

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

   The output contains one JSON object per transaction. Each change entry shows the operation type, the table name, and the column values.

6. When you finish, drop the replication slot to release resources:

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

What's next

• Set instance parameters