How to decode log files by using wal2json-PolarDB(PolarDB)-阿里云帮助中心

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

Scope of application

The following minor engine versions of PolarDB for PostgreSQL support wal2json:

PostgreSQL 18 (minor engine version 2.0.18.1.1.0 and later)
PostgreSQL 17 (minor engine version 2.0.17.7.5.0 and later)
PostgreSQL 16 (minor engine version 2.0.16.6.2.0 and later)
PostgreSQL 15 (minor engine version 2.0.15.12.4.0 and later)
PostgreSQL 14 (minor engine version 2.0.14.5.1.0 and later)
PostgreSQL 11 (minor engine version 2.0.11.9.29.0 and later)

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。

Background information

wal2json is a logical decoding output plugin. It provides the following features:

Access tuples generated by INSERT and UPDATE.
Access old row versions of UPDATE and DELETE based on the configured replica identity.
Consume changes by using the streaming protocol (logical replication slots) or a dedicated SQL API.

The wal2json plugin generates a JSON object for each transaction. The JSON object contains all new and old tuples. Additional options can include properties such as transaction timestamps, qualified schemas, data types, and transaction IDs. For more information, see Retrieve JSON objects by using SQL.

Usage notes

Because PolarDB for PostgreSQL uses REPLICA_IDENTITY_FULL as the replication method, the full row data is displayed during updates and deletes, instead of only the columns that changed. To log only the columns that changed, disable the polar_create_table_with_full_replica_identity parameter. This parameter cannot be modified in the console. Contact us for assistance.
The wal2json plugin depends on the logical decoding feature. The value of the wal_level parameter must be set to logical.

Note
You can set the wal_level parameter in the console. For more information, see Set cluster parameters. After you modify this parameter, the cluster restarts. Plan your operations and proceed with caution.

Retrieve JSON objects by using SQL

The wal2json plugin does not require CREATE EXTENSION for installation. Instead, it is loaded through a logical replication slot.

Create a logical replication slot with the wal2json plugin, and then run the following commands to retrieve JSON objects from WAL.

-- 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 of the wal2json type
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

-- Commit the transaction to write 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;

-- Retrieve JSON objects from WAL
SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

The following output is returned:

{
    "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"]
        }
    ]
}

Delete the replication slot named test_slot and return the string 'stop'.
```
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
```

Parameters

The following table describes the wal2json parameters.

Parameter	Description
change	A WAL entry for a single DML operation, such as INSERT, UPDATE, DELETE, or TRUNCATE.
changeset	A collection of change entries.
include-xids	Specifies whether to add the transaction ID (xid) to each changeset. Default value: false. Valid values: true: adds xid to each changeset. false (default): does not add xid to each changeset.
include-timestamp	Specifies whether to add a timestamp to each changeset. Default value: false. Valid values: true: adds a timestamp to each changeset. false (default): does not add a timestamp to each changeset.
include-schemas	Specifies whether to add the schema name to each change. Default value: true. Valid values: true (default): adds the schema name to each change. false: does not add the schema name to each change.
include-types	Specifies whether to add data types to each change. Default value: true. Valid values: true (default): adds data types to each change. false: does not add data types to each change.
include-typmod	Specifies whether to add type modifiers to types that have modifiers, such as varchar(20) instead of varchar. Default value: true. Valid values: true (default): adds type modifiers to types that have modifiers. false: does not add type modifiers to types that have modifiers.
include-type-oids	Specifies whether to add type OIDs. Default value: false. Valid values: true: adds type OIDs. false (default): does not add type OIDs.
include-not-null	Specifies whether to add `not null` constraint information as columnoptionals. Default value: false. Valid values: true: adds `not null` constraint information as columnoptionals. false (default): does not add `not null` constraint information as columnoptionals.
pretty-print	Specifies whether to add whitespace and indentation to format the JSON output. Default value: false. Valid values: true: adds whitespace and indentation to format the JSON output. false (default): does not add whitespace or indentation to format the JSON output.
write-in-chunks	Specifies whether to emit output after each change instead of after each changeset. Default value: false. Valid values: true: emits output after each change instead of after each changeset. false (default): emits output after each changeset instead of after each change.
include-lsn	Specifies whether to add the next LSN (nextlsn) to each changeset. Default value: false. Valid values: true: adds nextlsn to each changeset. false (default): does not add nextlsn to each changeset.
filter-tables	Excludes specific tables. Default value: empty, which means no tables are filtered. Note Separate multiple tables with commas. Each table must include the schema name. `.foo` matches the table foo in all schemas, and `bar.` matches all tables in the schema. Special characters (spaces, single quotes, commas, periods, and asterisks) must be escaped with a backslash. Schema and table names are case-sensitive. The table Foo bar in the public schema must be specified as `public.Foo\bar`.
add-tables	Specifies the tables to decode. By default, all tables in all schemas are decoded. The syntax is the same as filter-tables.
filter-msg-prefixes	Excludes rows with specific message prefixes. This parameter is typically used in the `pg_logical_slot_peek_changes()` function. Default value: empty, which means no messages are filtered. Separate multiple prefixes with commas.
add-msg-prefixes	Includes only rows with specific message prefixes. This parameter is typically used in the `pg_logical_slot_peek_changes()` function. Default value: all prefixes. Separate multiple prefixes with commas. You must use `filter-msg-prefixes` before this parameter.
format-version	Specifies the output format version. Default value: 1. Valid values: 1: uses output format version 1. 2: uses output format version 2.
actions	Specifies the operations to include in the output. Default value: all (INSERT, UPDATE, DELETE, and TRUNCATE). If you use `format-version 1`, TRUNCATE is not enabled.

Example

This section uses include-xids as an example to show how to use parameters.

Create a table and a logical replication slot, and 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);

Specify the parameter name and value in the function.

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

Design principles

For more information and design principles, see Official documentation.