wal2json

wal2json is an output plugin for PostgreSQL logical decoding. It provides access to tuples produced by INSERT and UPDATE operations. Depending on the configured replica identity, UPDATE/DELETE old row versions can also be accessed.

Changes can be consumed via:

1. The streaming protocol (using logical replication slots).
2. A special SQL API.

wal2json supports two primary output formats:

• format version 1: Produces one JSON object per transaction. All new/old tuples are contained within this single object. Options include transaction timestamp, schema-qualification, data types, and transaction IDs.
• format version 2: Produces one JSON object per tuple. It can optionally include JSON objects representing the beginning and end of a transaction.

Ensure PostgreSQL 9.4+ is installed with header files. If using a repository, install the development package (e.g., postgresql17-devel for yum or postgresql-server-dev-17 for apt) and add the bin directory to your PATH.

Example for a custom PostgreSQL installation in /home/euler/pg17:

$ tar -zxf wal2json-wal2json_2_6.tar.gz
$ cd wal2json-wal2json_2_6
$ export PATH=/home/euler/pg17/bin:$PATH
$ make
$ make install

To use pg_recvlogical, you must first configure a replication connection in pg_hba.conf.

For PostgreSQL 9.4, 9.5, 9.6:

local    replication     myuser                     trust

For PostgreSQL 10 or later:

local    mydatabase      myuser                     trust

Ensure max_wal_senders is set to at least 1 in postgresql.conf and restart PostgreSQL.

Usage Example:

1. Create a slot:

$ pg_recvlogical -d postgres --slot test_slot --create-slot -P wal2json

2. Start streaming with options:

$ pg_recvlogical -d postgres --slot test_slot --start -o pretty-print=1 -o add-msg-prefixes=wal2json -f -

3. Drop the slot when finished:

$ pg_recvlogical -d postgres --slot test_slot --drop-slot

Use the PostgreSQL yum repository to install the package for your specific PostgreSQL version (e.g., version 17).

$ sudo yum install wal2json_17

To build out-of-tree:

1. Edit the wal2json.vcxproj file and change c:\pg\17 to your PostgreSQL prefix directory.
2. Open the project file in MS Visual Studio and compile.
3. Copy the resulting wal2json.dll to the directory returned by pg_config --pkglibdir.

Use the PostgreSQL apt repository to install the package for your specific PostgreSQL version (e.g., version 17).

$ sudo apt-get install postgresql-17-wal2json

To use wal2json, you must set wal_level to logical. For PostgreSQL versions 9.4, 9.5, and 9.6, you must also explicitly set max_replication_slots and max_wal_senders. For version 10 or later, the default values are sufficient.

A restart of PostgreSQL is required after changing these parameters.

wal_level = logical
# Required for versions 9.4, 9.5, 9.6
max_replication_slots = 10
max_wal_senders = 10

wal_level = logical
#
# these parameters only need to set in versions 9.4, 9.5 and 9.6
# default values are ok in version 10 or later
#
max_replication_slots = 10
max_wal_senders = 10

You can retrieve logical changes directly via SQL using pg_logical_slot_get_changes.

Workflow:

1. Create a slot: SELECT pg_create_logical_replication_slot('slot_name', 'wal2json');
2. Retrieve changes: SELECT data FROM pg_logical_slot_get_changes('slot_name', NULL, NULL, 'option', 'value', ...);
3. Drop the slot: SELECT pg_drop_replication_slot('slot_name');

Example (Format Version 2):

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'format-version', '2', 'add-msg-prefixes', 'wal2json');