PolarDB:wal2json (decoding to JSON)

Prerequisites

Before you begin, make sure that you have:

• A PolarDB for PostgreSQL cluster running a supported minor engine version

• The wal_level parameter set to logical. Set this in the console under Set cluster parameters. This change requires a cluster restart.

Applicability

The following minor engine versions support wal2json:

To check your version, run SHOW polardb_version; or view it in the console. If your version is not supported, upgrade the minor engine version.

How it works

wal2json is a logical decoding output plugin for PostgreSQL. Rather than using the default pgoutput format, it converts WAL entries into JSON, which makes WAL changes easy to integrate with systems that consume JSON data.

For each transaction, wal2json generates one JSON object containing all changed tuples (rows). You can include additional metadata in the output — such as transaction IDs, timestamps, schemas, and data types — using the parameters described below.

wal2json supports two ways to consume changes:

• SQL API — Use pg_logical_slot_get_changes() to pull changes on demand. This approach works well for batch processing and one-off queries.

• Streaming protocol — Use logical replication slots with a streaming consumer (for example, pg_recvlogical). This approach suits real-time CDC pipelines.

The plugin handles the following DML operations: INSERT, UPDATE, DELETE, and TRUNCATE (format version 2 only).

Usage notes

Full row logging by default

PolarDB for PostgreSQL sets REPLICA_IDENTITY_FULL on all tables by default. This means every UPDATE and DELETE writes the full row to the WAL — not just the changed columns. To log only changed columns, disable the polar_create_table_with_full_replica_identity parameter. This parameter cannot be changed in the console. Contact us for assistance.

Tables without a primary key

For tables without a primary key, wal2json relies on REPLICA_IDENTITY to determine what row information is included in UPDATE and DELETE output. With the default REPLICA_IDENTITY_FULL setting on PolarDB for PostgreSQL, full row data is logged for all tables, including those without a primary key.

Retrieve changes using SQL

wal2json is not installed with CREATE EXTENSION. Load it through a logical replication slot instead.

The following example uses an orders table to show how wal2json captures INSERT and UPDATE operations:

1. Create the table and a logical replication slot:

   -- Create a sample table
   CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       product_name VARCHAR(100),
       quantity INT,
       status TEXT
   );
   
   -- Create a logical replication slot using the wal2json plugin
   SELECT 'init' FROM pg_create_logical_replication_slot('orders_slot', 'wal2json');

2. Make changes to the table:

   BEGIN;
   INSERT INTO orders (product_name, quantity, status) VALUES ('Laptop', 2, 'pending');
   INSERT INTO orders (product_name, quantity, status) VALUES ('Mouse', 5, 'pending');
   UPDATE orders SET status = 'confirmed' WHERE product_name = 'Laptop';
   COMMIT;

3. Retrieve the changes as JSON:

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

   The output looks like this:

   {
       "change": [
           {
               "kind": "insert",
               "schema": "public",
               "table": "orders",
               "columnnames": ["order_id", "product_name", "quantity", "status"],
               "columntypes": ["integer", "character varying(100)", "integer", "text"],
               "columnvalues": [1, "Laptop", 2, "pending"]
           },
           {
               "kind": "insert",
               "schema": "public",
               "table": "orders",
               "columnnames": ["order_id", "product_name", "quantity", "status"],
               "columntypes": ["integer", "character varying(100)", "integer", "text"],
               "columnvalues": [2, "Mouse", 5, "pending"]
           },
           {
               "kind": "update",
               "schema": "public",
               "table": "orders",
               "columnnames": ["order_id", "product_name", "quantity", "status"],
               "columntypes": ["integer", "character varying(100)", "integer", "text"],
               "columnvalues": [1, "Laptop", 2, "confirmed"],
               "oldkeys": {
                   "keynames": ["order_id"],
                   "keytypes": ["integer"],
                   "keyvalues": [1]
               }
           }
       ]
   }

4. When you are done, drop the replication slot:

   -- Returns 'stop' on success
   SELECT 'stop' FROM pg_drop_replication_slot('orders_slot');

Parameters

All parameters are passed as key-value pairs to pg_logical_slot_get_changes(). For example:

SELECT data FROM pg_logical_slot_get_changes(
    'my_slot', NULL, NULL,
    'pretty-print', '1',
    'include-xids', '1'
);

Example: using include-xids

This example shows how to pass parameters to retrieve transaction IDs alongside change data.

1. Create a table, a replication slot, and 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. Query the slot with include-xids enabled and verify that exactly one distinct transaction ID appears:

   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

• Set cluster parameters — enable wal_level = logical

• wal2json on GitHub — upstream documentation and design principles