PolarDB:wal2json

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) 2.0 cluster at revision version 2.0.14.1.0 or later

• The wal_level parameter set to logical

To check your revision version:

SHOW polar_version;

To set wal_level to logical, modify the parameter in the PolarDB console. See Configure cluster parameters.

How it works

wal2json uses logical decoding to capture row-level changes from WAL. It can:

• Access new row versions created by INSERT and UPDATE

• Access old row versions for UPDATE and DELETE, based on the table's REPLICA IDENTITY setting

• Stream changes through logical replication slots or the SQL API (pg_logical_slot_get_changes)

REPLICA IDENTITY behavior: PolarDB uses REPLICA_IDENTITY_FULL by default, so UPDATE and DELETE output includes the entire row — not just the changed columns. To output only the before/after columns for UPDATE, set the polar_create_table_with_full_replica_identity parameter to off. This parameter cannot be changed in the console — contact support to modify it.

No `CREATE EXTENSION` needed: Load wal2json by creating a logical replication slot — no extension installation required.

For more information, see Execute SQL statements to obtain JSON objects.

Capture WAL changes as JSON

The following steps walk through the complete workflow: create a replication slot, generate changes, read the JSON output, and clean up.

Step 1: Create test tables and a replication slot.

-- Create tables with and without primary keys
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);

-- Create a logical replication slot using wal2json
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

Step 2: Commit a transaction to write changes to WAL.

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;

Step 3: Read the JSON output from WAL.

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

Sample output:

{
    "change": [
        {
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [1, "Backup and Restore", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [2, "Tuning", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_with_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
            "columnvalues": [3, "Replication", "2018-03-27 12:05:29.914496"]
        }
        ,{
            "kind": "delete",
            "schema": "public",
            "table": "table2_with_pk",
            "oldkeys": {
                "keynames": ["a", "c"],
                "keytypes": ["integer", "timestamp without time zone"],
                "keyvalues": [1, "2018-03-27 12:05:29.914496"]
            }
        }
        ,{
            "kind": "delete",
            "schema": "public",
            "table": "table2_with_pk",
            "oldkeys": {
                "keynames": ["a", "c"],
                "keytypes": ["integer", "timestamp without time zone"],
                "keyvalues": [2, "2018-03-27 12:05:29.914496"]
            }
        }
        ,{
            "kind": "insert",
            "schema": "public",
            "table": "table2_without_pk",
            "columnnames": ["a", "b", "c"],
            "columntypes": ["integer", "numeric(5,2)", "text"],
            "columnvalues": [1, 2.34, "Tapir"]
        }
    ]
}

Step 4: Drop the replication slot when done.

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

Parameters

Examples

The following example uses include-xids to verify that all changes in a transaction share the same xid.

1. Create a table and a replication slot, then insert a row.

   DROP TABLE IF EXISTS tbl;
   CREATE TABLE tbl (id int);
   SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'wal2json');
   INSERT INTO tbl VALUES (1);

2. Read changes and verify that exactly one xid is present.

   SELECT
     count(*) = 1,
     count(distinct ((data::json)->'xid')::text) = 1
   FROM pg_logical_slot_get_changes(
     'regression_slot', NULL, NULL,
     'format-version', '1',
     'include-xids', '1');

What's next

For the full list of wal2json options and upstream release notes, see the official wal2json documentation.