### Install wal2json from Source

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

Steps to clone the repository, compile, and install the wal2json plugin from its source code.

```bash
git clone https://github.com/eulerto/wal2json.git
cd wal2json
make
make install
```

--------------------------------

### wal2json Configuration Example

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Example of setting the maximum number of replication slots for wal2json.

```ini
max_replication_slots = 10
```

--------------------------------

### Build and Install wal2json from Source (Custom PostgreSQL Path)

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Compiles and installs wal2json when PostgreSQL is installed in a custom directory. Ensure PostgreSQL header files are installed.

```bash
export PATH=/home/euler/pg17/bin:$PATH
make
make install
```

--------------------------------

### SQL Drop Table Command Example

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Example of an SQL DROP TABLE command.

```sql
DROP TABLE
```

--------------------------------

### Example SQL for Data Changes

Source: https://github.com/eulerto/wal2json/blob/master/README.md

SQL statements to create tables and insert data, demonstrating changes that wal2json can capture. Includes a logical emit message example.

```sql
CREATE TABLE table1_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
CREATE TABLE table1_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT);

BEGIN;
INSERT INTO table1_with_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table1_with_pk (b, c) VALUES('Tuning', now());
INSERT INTO table1_with_pk (b, c) VALUES('Replication', now());
SELECT pg_logical_emit_message(true, 'wal2json', 'this message will be delivered');

```

--------------------------------

### Build and Install wal2json from Source (PostgreSQL Apt Repository)

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Compiles and installs wal2json using the PostgreSQL apt repository. Requires postgresql-server-dev-17 package.

```bash
sudo apt-get install postgresql-server-dev-17
export PATH=/usr/lib/postgresql/17/bin:$PATH
make
make install
```

--------------------------------

### Stream to file and tail output

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

This example shows how to stream logical changes to a file and then tail the file to monitor the output in real-time.

```bash
pg_recvlogical -d postgres --slot my_slot --start \
  -o format-version=2 \
  -f /tmp/changes.jsonl & 

tail -f /tmp/changes.jsonl
```

--------------------------------

### Start wal2json with pg_recvlogical

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Commands to start the wal2json plugin using pg_recvlogical. The first command creates a slot, and the second starts streaming changes with pretty-printing and message prefix filtering.

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

--------------------------------

### wal2json Change Action Example

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Example of a wal2json output for a change action.

```json
{"action":"C"}
```

--------------------------------

### Initialize Logical Slot with Plugin Parameters (SQL)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/configuration.md

Example of passing plugin parameters like format-version, pretty-print, and include-xids when initializing a logical slot using SQL.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot_name', NULL, NULL, 
  'format-version', '2', 'pretty-print', '1', 'include-xids', '1');
```

--------------------------------

### Version 2 INSERT Record Example

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Provides an example of an INSERT record in Version 2, detailing the action, schema, table, and an array of column objects with their names, types, and values.

```json
{
  "action": "I",
  "schema": "public",
  "table": "users",
  "columns": [
    {"name": "id", "type": "integer", "value": 1},
    {"name": "name", "type": "text", "value": "Alice"},
    {"name": "created_at", "type": "timestamp without time zone", "value": "2023-06-15T10:30:45.123456"}
  ]
}
```

--------------------------------

### wal2json output for example 2 (partial)

Source: https://github.com/eulerto/wal2json/blob/master/README.md

The JSON output from wal2json for the second example, showing a non-transactional message and the start of change data. This output is truncated in the source.

```json
{
    "change": [
        {
            "kind": "message",
            "transactional": false,
            "prefix": "wal2json",
            "content": "this non-transactional message will be delivered even if you rollback the transaction"
        }
    ]
}
psql:/tmp/example2.sql:17: WARNING:  table "table2_without_pk" without primary key or replica identity is nothing
{
    "change": [
        {

```

--------------------------------

### Install wal2json on Debian/Ubuntu

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Installs the wal2json package using apt-get on Debian-based systems.

```bash
$ sudo apt-get install postgresql-17-wal2json
```

--------------------------------

### Connecting with pg_recvlogical

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Command-line examples for connecting to a PostgreSQL database using pg_recvlogical to consume logical decoding output.

```bash
# Connect to "postgres" database with user "postgres"
pg_recvlogical -d postgres -U postgres --slot myslot --start
```

```bash
# Explicit connection string
pg_recvlogical -d 'dbname=postgres user=postgres host=localhost' \
  --slot myslot --start
```

--------------------------------

### Per-Transaction Callback Chain Example (Format v2)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/plugin-initialization.md

Illustrates the sequence of callbacks for an INSERT followed by an UPDATE within a single transaction using format version 2.

```text
startup_cb (once at slot creation)
  └─ Initialize JsonDecodingData
     set include-xids = true
     set format-version = 2
     
begin_cb (transaction 2000)
  └─ Format v2: emit {"action": "B", "xid": 2000}
  
change_cb (INSERT into orders)
  └─ Format v2: emit {"action": "I", "schema": "public", "table": "orders", ...}
  
change_cb (UPDATE to inventory)
  └─ Format v2: emit {"action": "U", "schema": "public", "table": "inventory", ...}
  
commit_cb (end of transaction 2000)
  └─ Format v2: emit {"action": "C", "xid": 2000}
```

--------------------------------

### Build and Install wal2json from Source (PostgreSQL Yum Repository)

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Compiles and installs wal2json using the PostgreSQL yum repository. Requires postgresql17-devel package.

```bash
sudo yum install postgresql17-devel
export PATH=/usr/pgsql-17/bin:$PATH
make
make install
```

--------------------------------

### SQL commands for wal2json example 2

Source: https://github.com/eulerto/wal2json/blob/master/README.md

A comprehensive SQL script for setting up tables, creating a logical replication slot, performing data operations, emitting messages, and retrieving changes using wal2json.

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

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

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());
SELECT pg_logical_emit_message(true, 'wal2json', 'this message will be delivered');
SELECT pg_logical_emit_message(true, 'pgoutput', 'this message will be filtered');
DELETE FROM table2_with_pk WHERE a < 3;
SELECT pg_logical_emit_message(false, 'wal2json', 'this non-transactional message will be delivered even if you rollback the transaction');

INSERT INTO table2_without_pk (b, c) VALUES(2.34, 'Tapir');
-- it is not added to stream because there isn't a pk or a replica identity
UPDATE table2_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1', 'add-msg-prefixes', 'wal2json');
SELECT 'stop' FROM pg_drop_replication_slot('test_slot');

DROP TABLE table2_with_pk;
DROP TABLE table2_without_pk;
```

--------------------------------

### wal2json Stop Command Example

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Example of the 'stop' command for wal2json.

```text
stop
```

--------------------------------

### wal2json Insert Action Example

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Example of a wal2json output for an insert action on a table without a primary key.

```json
{"action":"I","schema":"public","table":"table3_without_pk","columns":[{"name":"a","type":"integer","value":1},{"name":"b","type":"numeric(5,2)","value":2.34},{"name":"c","type":"text","value":"Tapir"}]}
```

--------------------------------

### Initialize Logical Slot with Plugin Parameters (pg_recvlogical)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/configuration.md

Example of passing plugin parameters using the -o option with pg_recvlogical for logical decoding.

```bash
pg_recvlogical -d postgres --slot slot_name --start \
  -o format-version=2 -o pretty-print=1 -o include-xids=1 -f -
```

--------------------------------

### SQL commands for wal2json example 1

Source: https://github.com/eulerto/wal2json/blob/master/README.md

This SQL script sets up tables, performs data manipulation, and emits messages to be captured by wal2json. It's intended for use with psql.

```sql
max_replication_slots = 10

SELECT pg_logical_emit_message(true, 'pgoutput', 'this message will be filtered');
DELETE FROM table1_with_pk WHERE a < 3;
SELECT pg_logical_emit_message(false, 'wal2json', 'this non-transactional message will be delivered even if you rollback the transaction');

INSERT INTO table1_without_pk (b, c) VALUES(2.34, 'Tapir');
-- it is not added to stream because there isn't a pk or a replica identity
UPDATE table1_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

DROP TABLE table1_with_pk;
DROP TABLE table1_without_pk;
```

--------------------------------

### Configuring pg_hba.conf for Replication

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Examples of how to configure the pg_hba.conf file for replication connections, depending on your PostgreSQL version.

```ini
# For PostgreSQL 9.4-9.6:
local   replication     myuser                     trust
local   replication     myuser                     md5
```

```ini
# For PostgreSQL 10+:
local   mydatabase      myuser                     trust
local   mydatabase      myuser                     md5
```

--------------------------------

### Format Version 2 with Metadata Example

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

Demonstrates the JSON output for format version 2, including transaction metadata like BEGIN (B), INSERT (I), UPDATE (U), and COMMIT (C) markers with associated details.

```json
{"action":"B","xid":100,"timestamp":"2023-06-15T10:30:45.123456+00:00"}
{"action":"I","schema":"public","table":"users","columns":[{"name":"id","type":"integer","value":1},{"name":"name","type":"text","value":"Alice"}]}
{"action":"U","schema":"public","table":"users","columns":[{"name":"id","type":"integer","value":1},{"name":"name","type":"text","value":"Alice Smith"}],"identity":[{"name":"id","type":"integer","value":1}]}
{"action":"C","xid":100,"lsn":"0/1A2B3C4D","timestamp":"2023-06-15T10:30:45.123456+00:00"}
```

--------------------------------

### Example of Emitting Logical Message

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Demonstrates emitting a logical message with a specific prefix and content.

```sql
SELECT pg_logical_emit_message(true, 'myapp', 'Order 12345 processed');
```

--------------------------------

### Begin Transaction Markers

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Emitted at the start of a transaction. Fields like 'xid' and 'timestamp' are included based on configuration options.

```json
{"action":"B"}
```

```json
{"action":"B","xid":100}
```

```json
{"action":"B","xid":100,"timestamp":"2023-06-15T10:30:45.123456"}
```

--------------------------------

### psql output for wal2json example 2

Source: https://github.com/eulerto/wal2json/blob/master/README.md

The output from executing the second SQL script using psql. It includes table creation, slot initialization, data operations, and warnings.

```text
$ psql -At -f /tmp/example2.sql postgres
CREATE TABLE
CREATE TABLE
init
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
3/78C2CA50
3/78C2CAA8
DELETE 2
3/78C2CBD8
INSERT 0 1
UPDATE 1
COMMIT
```

--------------------------------

### Install wal2json on Red Hat/CentOS

Source: https://github.com/eulerto/wal2json/blob/master/README.md

Installs the wal2json package using yum on Red Hat-based systems.

```bash
$ sudo yum install wal2json_17
```

--------------------------------

### Version 1 Write-In-Chunks Mode Output Example

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Illustrates the immediate, individual JSON output for each change when 'write-in-chunks' mode is enabled in Version 1.

```json
{"change": [{"kind": "insert", ...}]}
{"change": [{"kind": "insert", ...}]}
{"change": [{"kind": "update", ...}]}
```

--------------------------------

### Combining Filters for Complex Scenarios

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Filters can be combined to create complex inclusion and exclusion rules. This example starts with a whitelist of schemas and then removes specific patterns.

```sql
-- Replicate main tables, exclude internal/temporary
'add-tables', 'public.*,app_schema.*'
'filter-tables', 'public.internal_*,temp.*'
```

--------------------------------

### psql output for wal2json example 1

Source: https://github.com/eulerto/wal2json/blob/master/README.md

The output from executing the first SQL script using psql. It shows table creation, data modification confirmations, and the JSON output from wal2json.

```text
$ psql -At -f /tmp/example1.sql postgres
CREATE TABLE
CREATE TABLE
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
3/78BFC828
3/78BFC880
DELETE 2
3/78BFC990
INSERT 0 1
UPDATE 1
COMMIT
DROP TABLE
DROP TABLE
```

--------------------------------

### Format Version 2: INSERT Example

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

Illustrates the JSON structure for an INSERT operation in format version 2. Each change is a separate JSON object.

```json
{"action":"I","schema":"public","table":"users","columns":[{"name":"id","type":"integer","value":1},{"name":"name","type":"text","value":"Alice"}]}
```

--------------------------------

### Real-Time Replication to Target Database

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Replicate changes to another system in real-time using a bash script. This example filters for insert/update actions and applies them to a target database via psql.

```bash
#!/bin/bash
pg_recvlogical -d source_db --slot repl_slot --start \
  -o format-version=2 \
  -f - | \
  jq -c \
    'select(.action == "I" or .action == "U") | 
    {table: .table, action: .action, data: .columns}' \
  | \
  while read change; do
    # Apply to target database
    psql -d target_db -c "INSERT INTO audit (change) VALUES ('$change')"
  done
```

--------------------------------

### Route Transaction Begin to Version-Specific Handlers

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/internal-callbacks.md

The pg_decode_begin_txn function routes the start of a transaction to either the v1 or v2 handler based on the configured format version. This function is called at the start of each transaction.

```c
static void pg_decode_begin_txn(LogicalDecodingContext *ctx,
                                ReorderBufferTXN *txn)
{
  JsonDecodingData *data = ctx->output_plugin_private;
  
  if (data->format_version == 1) {
    pg_decode_begin_txn_v1(ctx, txn);
  } else {
    pg_decode_begin_txn_v2(ctx, txn);
  }
}
```

--------------------------------

### Advancing a Logical Slot with LSN

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Provides an SQL example of how to use the 'pg_logical_slot_advance' function with a specific LSN value.

```sql
SELECT pg_logical_slot_advance('slot', '0/1A2B3C4D'::pg_lsn);
```

--------------------------------

### Resuming Logical Decoding from a Specific LSN

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

SQL examples demonstrating how to save and resume logical decoding from a specific Log Sequence Number (LSN) using PostgreSQL functions.

```sql
-- Save LSN after processing batch
SELECT MAX(data->'nextlsn') as last_lsn FROM processed_changes;
-- Result: "0/1A2B3C4D"

-- Later, resume from this point
SELECT pg_logical_slot_advance('slot', '0/1A2B3C4D'::pg_lsn);

-- Get changes after this point
SELECT data FROM pg_logical_slot_get_changes('slot', '0/1A2B3C4D', NULL);
```

--------------------------------

### Include Transactional Messages

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Demonstrates including transactional messages using 'add-msg-prefixes'. Note that the example also filters the 'admin' message due to prefix mismatch.

```sql
SELECT pg_logical_emit_message(true, 'app', 'Msg 1');
SELECT pg_logical_emit_message(false, 'admin', 'Msg 2');

SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-msg-prefixes', 'app'
);
```

--------------------------------

### Create and Start Streaming with pg_recvlogical

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Combine slot creation and streaming into a single command using `pg_recvlogical`. The `--create-slot` and `-P wal2json` flags are used for this purpose. The `&` symbol runs the command in the background.

```bash
pg_recvlogical -d postgres --slot my_slot --create-slot -P wal2json \
  --start \
  -o format-version=2 \
  -o include-xids=1 \
  -f /tmp/changes.jsonl &
```

--------------------------------

### Format Version 1: INSERT Example

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

Shows the JSON structure for an INSERT operation in format version 1. Each transaction is a single JSON object containing a 'change' array.

```json
{
  "change": [
    {
      "kind": "insert",
      "schema": "public",
      "table": "users",
      "columnnames": ["id", "name"],
      "columntypes": ["integer", "text"],
      "columnvalues": [1, "Alice"]
    }
  ]
}
```

--------------------------------

### Pretty-Print Enabled JSON Output

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Example of JSON output when pretty-printing is enabled (pretty-print=1). This format is human-readable with indentation and newlines.

```json
{
  "action": "I",
  "schema": "public",
  "table": "users",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "value": 1
    }
  ]
}
```

--------------------------------

### Example Data Structure (JSON)

Source: https://github.com/eulerto/wal2json/blob/master/README.md

This JSON structure represents sample data captured by wal2json, including insert and delete operations on tables with and without primary keys, as well as emitted messages.

```json
{
	"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": "message",
	"transactional": true,
	"prefix": "wal2json",
	"content": "this message will be delivered"
}
,
{
	"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"]
}
]
}
```

--------------------------------

### Get Changes with Default Configuration

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/configuration.md

Retrieves changes from a logical slot using all default wal2json settings. This includes format version 1, no extra metadata, and all operations and tables.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot_name', NULL, NULL);
```

--------------------------------

### Pretty-Print Change Records

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Use the 'pretty-print' option with pg_logical_slot_peek_changes to make the JSON output human-readable. This example also demonstrates decoding a hex-encoded substring within the change data.

```sql
SELECT
  data,
  pg_catalog.convert_from(pg_catalog.decode(
    substring(data from 2 for 20), 'hex'), 'UTF8')
FROM pg_logical_slot_peek_changes('slot', NULL, NULL,
  'pretty-print', '1',
  'format-version', '2'
) LIMIT 1;
```

--------------------------------

### Pretty-Print Disabled JSON Output (Default)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Example of JSON output when pretty-printing is disabled (pretty-print=0), which is the default. This format is compact with no whitespace, suitable for production use.

```json
{"action":"I","schema":"public","table":"users","columns":[{"name":"id","type":"integer","value":1}]}
```

--------------------------------

### Testing Filter Expressions with Get Changes

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Once confident with the filter behavior tested via `peek_changes`, use `pg_logical_slot_get_changes` to retrieve the actual filtered changes.

```sql
-- Verify tables are present
-- Then run get_changes when confident
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-tables', 'public.orders'
);
```

--------------------------------

### Consume Changes in Batch Mode

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

SQL query to retrieve changes from a replication slot using the 'pg_logical_slot_get_changes' function. This example specifies format version 2, includes transaction IDs, and enables pretty-printing.

```sql
SELECT data FROM pg_logical_slot_get_changes('my_slot', NULL, NULL,
  'format-version', '2',
  'include-xids', '1',
  'pretty-print', '1'
);
```

--------------------------------

### Option Parsing in pg_decode_startup

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/plugin-initialization.md

Parses user-defined options from ctx->output_plugin_options during plugin startup. Handles format-version and include-xids options.

```c
foreach(option, ctx->output_plugin_options) {
  DefElem *elem = lfirst(option);
  
  if (strcmp(elem->defname, "format-version") == 0) {
    parse_int(strVal(elem->arg), &data->format_version, 0, NULL);
  }
  else if (strcmp(elem->defname, "include-xids") == 0) {
    parse_bool(strVal(elem->arg), &data->include_xids);
  }
  // ... more options
}
```

--------------------------------

### Version 1 Output Buffering Behavior (SQL)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Demonstrates the SQL commands that lead to buffered changes in Version 1, with the JSON output emitted only upon COMMIT.

```sql
BEGIN;
INSERT INTO users VALUES (1, 'Alice');  -- buffered
INSERT INTO users VALUES (2, 'Bob');    -- buffered
UPDATE users SET name = 'Alice Smith' WHERE id = 1;  -- buffered
COMMIT;  -- <- JSON object with all 3 changes emitted here
```

--------------------------------

### Deprecated Parameter Usage

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Example of a deprecated parameter 'include-unchanged-toast' in pg_logical_slot_get_changes. This parameter should be removed as TOAST handling is now automatic.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'include-unchanged-toast', '1'
);
```

--------------------------------

### Error Reporting with ereport

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/plugin-initialization.md

Example of using PostgreSQL's ereport macro for error handling, specifically for invalid parameter values.

```c
ereport(ERROR,
  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
   errmsg("could not parse value \"%s\" for parameter \"%s\"",
     strVal(elem->arg), elem->defname)));
```

--------------------------------

### Register Output Plugin Callbacks

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/internal-callbacks.md

Initializes the output plugin by registering various callback functions with PostgreSQL's logical decoding framework. This function is called once when the plugin is loaded.

```c
void _PG_output_plugin_init(OutputPluginCallbacks *cb)
{
  cb->startup_cb = pg_decode_startup;
  cb->shutdown_cb = pg_decode_shutdown;
  cb->begin_cb = pg_decode_begin_txn;
  cb->change_cb = pg_decode_change;
  cb->commit_cb = pg_decode_commit_txn;
  
  #if PG_VERSION_NUM >= 90500
  cb->filter_by_origin_cb = pg_filter_by_origin;
  #endif
  
  #if PG_VERSION_NUM >= 90600
  cb->message_cb = pg_decode_message;
  #endif
  
  #if PG_VERSION_NUM >= 110000
  cb->truncate_cb = pg_decode_truncate;
  #endif
}
```

--------------------------------

### wal2json output for example 1

Source: https://github.com/eulerto/wal2json/blob/master/README.md

The JSON output generated by wal2json corresponding to the operations in the first SQL script. It details messages, inserts, and deletes.

```json
{
	"change": [
	]
}
{
	"change": [
	]
}
{
    "change": [
        {
            "kind": "message",
            "transactional": false,
            "prefix": "wal2json",
            "content": "this non-transactional message will be delivered even if you rollback the transaction"
        }
    ]
}
WARNING:  table "table1_without_pk" without primary key or replica identity is nothing
{
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "table1_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [1, "Backup and Restore", "2018-03-27 11:58:28.988414"]
		}
		,{ 
			"kind": "insert",
			"schema": "public",
			"table": "table1_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [2, "Tuning", "2018-03-27 11:58:28.988414"]
		}
		,{ 
			"kind": "insert",
			"schema": "public",
			"table": "table1_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [3, "Replication", "2018-03-27 11:58:28.988414"]
		}
        ,{ 
            "kind": "message",
            "transactional": true,
            "prefix": "wal2json",
            "content": "this message will be delivered"
        }
		,{ 
			"kind": "delete",
			"schema": "public",
			"table": "table1_with_pk",
			"oldkeys": {
				"keynames": ["a", "c"],
				"keytypes": ["integer", "timestamp without time zone"],
				"keyvalues": [1, "2018-03-27 11:58:28.988414"]
			}
		}
		,{ 
			"kind": "delete",
			"schema": "public",
			"table": "table1_with_pk",
			"oldkeys": {
				"keynames": ["a", "c"],
				"keytypes": ["integer", "timestamp without time zone"],
				"keyvalues": [2, "2018-03-27 11:58:28.988414"]
			}
		}
		,{ 
			"kind": "insert",
			"schema": "public",
			"table": "table1_without_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "numeric(5,2)", "text"],
			"columnvalues": [1, 2.34, "Tapir"]
		}
	]
}
{
    "change": [
    ]
}
{
    "change": [
    ]
}
```

--------------------------------

### SQL to Include a Table from Any Schema

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Use 'add-tables' with a wildcard '*' in the schema position, like '*.users', to match tables with that name across any schema.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-tables', '*.users'
);
```

--------------------------------

### SQL Script for wal2json with Format Version 2

Source: https://github.com/eulerto/wal2json/blob/master/README.md

This SQL script sets up tables, creates a logical replication slot, performs various DML operations and emits messages, then retrieves changes using wal2json with format-version 2. It demonstrates transactional and non-transactional messages, and handling of updates without primary keys.

```sql
CREATE TABLE table3_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
CREATE TABLE table3_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT);

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

BEGIN;
INSERT INTO table3_with_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table3_with_pk (b, c) VALUES('Tuning', now());
INSERT INTO table3_with_pk (b, c) VALUES('Replication', now());
SELECT pg_logical_emit_message(true, 'wal2json', 'this message will be delivered');
SELECT pg_logical_emit_message(true, 'pgoutput', 'this message will be filtered');
DELETE FROM table3_with_pk WHERE a < 3;
SELECT pg_logical_emit_message(false, 'wal2json', 'this non-transactional message will be delivered even if you rollback the transaction');

INSERT INTO table3_without_pk (b, c) VALUES(2.34, 'Tapir');
-- it is not added to stream because there isn't a pk or a replica identity
UPDATE table3_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

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

DROP TABLE table3_with_pk;
DROP TABLE table3_without_pk;
```

--------------------------------

### Development Configuration with Full Metadata

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

This SQL query retrieves changes with all available metadata, formatted for readability. It's useful for understanding the data flow during development.

```sql
SELECT data FROM pg_logical_slot_get_changes('dev_slot', NULL, NULL,
  'format-version', '2',
  'pretty-print', '1',
  'include-xids', '1',
  'include-timestamp', '1',
  'include-schemas', '1',
  'include-types', '1',
  'include-typmod', '1',
  'include-lsn', '1'
);
```

--------------------------------

### Version 1 Write-In-Chunks Mode Configuration

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Shows the configuration parameter to enable 'write-in-chunks' mode for Version 1, which disables buffering and emits changes immediately.

```sql
'write-in-chunks', '1'
```

--------------------------------

### SQL Syntax for Table Whitelisting

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

This is the general syntax for using the 'add-tables' parameter to specify which tables to include.

```sql
'add-tables', 'schema.table1,schema.table2,...'
```

--------------------------------

### Create a Logical Replication Slot

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

SQL command to create a new logical replication slot named 'my_slot' using the 'wal2json' output plugin.

```sql
SELECT 'init' FROM pg_create_logical_replication_slot('my_slot', 'wal2json');
```

--------------------------------

### Get Changes with LSN Limits

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Retrieve changes up to a specific LSN or XID using `pg_logical_slot_get_changes`. This is useful for reading recent changes, resuming from a specific point, or batch processing.

```sql
SELECT data FROM pg_logical_slot_get_changes(
  'my_slot',
  '0/00000A00',   -- upto_lsn: stop after this LSN
  NULL,           -- upto_xid: NULL = no limit
  'include-lsn', '1',
  'format-version', '1'
);
```

--------------------------------

### Version 2 Typical Sequence

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Shows a typical sequence of Version 2 output, which uses JSONL format with optional transaction markers (BEGIN 'B', COMMIT 'C') and individual change records.

```json
{"action":"B","xid":100}
{"action":"I",...}
{"action":"I",...}
{"action":"U",...}
{"action":"C","xid":100}
```

--------------------------------

### Transaction Begin Marker (Format Version 2)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/types.md

Marks the beginning of a transaction. Includes optional transaction ID and timestamp if configured.

```json
{
  "action": "B"
}
```

```json
{
  "action": "B",
  "xid": 2147483648,
  "timestamp": "2023-06-15T10:30:45.123456+00:00"
}
```

--------------------------------

### Get Changes from a Slot (Destructive)

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Use `pg_logical_slot_get_changes` to retrieve and consume changes from a slot. This function advances the slot's confirmed flush LSN. Changes are returned as JSON objects.

```sql
SELECT data FROM pg_logical_slot_get_changes(
  'my_slot',           -- slot_name
  NULL,                -- upto_lsn
  NULL,                -- upto_xid
  'format-version', '2'
);
```

```sql
BEGIN;
SELECT data FROM pg_logical_slot_get_changes('app_changes', NULL, NULL,
  'format-version', '2', 'pretty-print', '1'
) AS change(data);
-- Application processes changes here
COMMIT;
```

--------------------------------

### Initialize Decoding Context

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/internal-callbacks.md

Called once when decoding begins. It allocates and initializes the plugin's state, parses plugin options, and sets the output type. The plugin state is stored in ctx->output_plugin_private.

```c
static void pg_decode_startup(LogicalDecodingContext *ctx,
                              OutputPluginOptions *opt,
                              bool is_init)
{
  JsonDecodingData *data = palloc0(sizeof(JsonDecodingData));
  
  // Create memory context for allocations
  data->context = AllocSetContextCreate(TopMemoryContext,
                    "wal2json context",
                    ALLOCSET_DEFAULT_SIZES);
  
  // Set defaults
  data->include_xids = false;
  data->include_timestamp = false;
  data->format_version = 1;
  // ... more defaults
  
  // Parse options
  ListCell *option;
  foreach(option, ctx->output_plugin_options) {
    DefElem *elem = lfirst(option);
    if (strcmp(elem->defname, "include-xids") == 0) {
      parse_bool(strVal(elem->arg), &data->include_xids);
    }
    // ... more option parsing
  }
  
  ctx->output_plugin_private = data;
  opt->output_type = OUTPUT_PLUGIN_TEXTUAL_OUTPUT;
}
```

--------------------------------

### SQL to Include Specific Tables

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Use 'add-tables' with a comma-separated list of schema-qualified table names to include only those specific tables.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-tables', 'public.orders,public.customers,public.products'
);
```

--------------------------------

### Understanding LSN Format and Progression

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Illustrates the format of Log Sequence Numbers (LSN) in PostgreSQL WAL and how they progress with data changes.

```text
0/1A2B3C4D
│  │
│  └─ offset (lower 32 bits)
└──── xlogid (upper 32 bits)
```

```text
0/00000000  # Start of timeline
0/00001000  # 4096 bytes into timeline
1/00000000  # Wrap around to next xlogid
```

```text
Start of slot: 0/00000000
After change 1: 0/00000100
After change 2: 0/00000200
... 
Slot advances as changes are consumed
```

--------------------------------

### Test Action Filtering

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Use pg_logical_slot_peek_changes with the 'actions' option to verify that filtering by insert, update, or delete operations is functioning as expected.

```sql
-- Verify action filtering
SELECT data FROM pg_logical_slot_peek_changes('slot', NULL, NULL,
  'actions', 'insert,update'
) LIMIT 5;
```

--------------------------------

### Required PostgreSQL Configuration

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/configuration.md

Configure these parameters in postgresql.conf to enable wal2json. A server restart is necessary after changes.

```sql
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
```

--------------------------------

### SQL Syntax for Action Filtering

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

This is the general syntax for applying action filters when retrieving changes from a logical slot.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'actions', 'insert,update,delete'
);
```

--------------------------------

### List All Replication Slots

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

Query the `pg_replication_slots` system view to retrieve a list of all available replication slots, including their names, plugins, types, and temporary status.

```sql
SELECT slot_name, plugin, slot_type, temporary FROM pg_replication_slots;
```

--------------------------------

### SQL to Include Tables from a Specific Schema

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Employ the 'add-tables' parameter with a wildcard '*' to include all tables within a specified schema, such as 'public.*'.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-tables', 'public.*'
);
```

--------------------------------

### Initialize OutputPluginCallbacks in wal2json

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/plugin-initialization.md

Registers callback functions for logical decoding events, including startup, transaction begin/commit, changes, shutdown, and specific features like origin filtering and message handling.

```c
void _PG_output_plugin_init(OutputPluginCallbacks *cb) {
  cb->startup_cb = pg_decode_startup;
  cb->begin_cb = pg_decode_begin_txn;
  cb->change_cb = pg_decode_change;
  cb->commit_cb = pg_decode_commit_txn;
  cb->shutdown_cb = pg_decode_shutdown;
  cb->filter_by_origin_cb = pg_filter_by_origin;  // PG 9.5+
  cb->message_cb = pg_decode_message;              // PG 9.6+
  cb->truncate_cb = pg_decode_truncate;            // PG 11+
}
```

--------------------------------

### Output of SQL Script with wal2json Format Version 2

Source: https://github.com/eulerto/wal2json/blob/master/README.md

This output shows the result of executing the SQL script using psql, including table creation confirmations, emitted message identifiers, and the JSON-formatted change data retrieved from the replication slot with format-version 2.

```text
CREATE TABLE
CREATE TABLE
init
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
3/78CB8F30
3/78CB8F88
DELETE 2
3/78CB90B8
INSERT 0 1
UPDATE 1
COMMIT
psql:/tmp/example3.sql:20: WARNING:  no tuple identifier for UPDATE in table "public"."table3_without_pk"
{"action":"M","transactional":false,"prefix":"wal2json","content":"this non-transactional message will be delivered even if you rollback the transaction"}
{"action":"B"}
{"action":"I","schema":"public","table":"table3_with_pk","columns":[{"name":"a","type":"integer","value":1},{"name":"b","type":"character varying(30)","value":"Backup and Restore"},{"name":"c","type":"timestamp without time zone","value":"2019-12-29 04:58:34.806671"}]}
{"action":"I","schema":"public","table":"table3_with_pk","columns":[{"name":"a","type":"integer","value":2},{"name":"b","type":"character varying(30)","value":"Tuning"},{"name":"c","type":"timestamp without time zone","value":"2019-12-29 04:58:34.806671"}]}
{"action":"I","schema":"public","table":"table3_with_pk","columns":[{"name":"a","type":"integer","value":3},{"name":"b","type":"character varying(30)","value":"Replication"},{"name":"c","type":"timestamp without time zone","value":"2019-12-29 04:58:34.806671"}]}
{"action":"M","transactional":true,"prefix":"wal2json","content":"this message will be delivered"}
{"action":"D","schema":"public","table":"table3_with_pk","identity":[{"name":"a","type":"integer","value":1},{"name":"c","type":"timestamp without time zone","value":"2019-12-29 04:58:34.806671"}]}
{"action":"D","schema":"public","table":"table3_with_pk","identity":[{"name":"a","type":"integer","value":2},{"name":"c","type":"timestamp without time zone","value":"2019-12-29 04:58:34.806671"}]}
```

--------------------------------

### Real-time Monitoring with pg_recvlogical

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Monitor wal2json output in real-time using pg_recvlogical. This command connects to a specified PostgreSQL database and replication slot, streaming changes to standard output.

```bash
# Real-time monitoring
pg_recvlogical -d postgres --slot myslot --start \
  -o format-version=2 \
  -o pretty-print=1 \
  -f - | tail -f
```

--------------------------------

### Test Table Filtering

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Use pg_logical_slot_peek_changes with the 'add-tables' option to verify that table filtering is working correctly without consuming changes from the slot.

```sql
-- Verify table filtering works
SELECT data FROM pg_logical_slot_peek_changes('slot', NULL, NULL,
  'add-tables', 'public.orders'
) LIMIT 5;
```

--------------------------------

### SQL to Include Combined Table Patterns

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Combine specific tables and schema wildcards in the 'add-tables' parameter for flexible whitelisting, e.g., 'public.orders,audit.*,reports.summary'.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'add-tables', 'public.orders,audit.*,reports.summary'
);
```

--------------------------------

### Real-Time Change Stream with pg_recvlogical

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/README.md

Command to stream changes in real-time using format version 2 with pg_recvlogical. It connects to a database, uses a specific slot, and outputs to stdout.

```bash
pg_recvlogical -d mydb --slot cdc --start -o format-version=2 -f -
```

--------------------------------

### Set wal_level to logical

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Configure PostgreSQL to enable logical decoding by setting the wal_level parameter to 'logical' in the postgresql.conf file and then restarting the PostgreSQL service.

```ini
# In postgresql.conf
wal_level = logical

# Restart PostgreSQL
pg_ctl restart
```

--------------------------------

### Production CDC with Minimal Overhead

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/usage-examples.md

This SQL query retrieves changes with minimal essential information, optimized for performance and storage in production environments. It includes schema information but omits other verbose metadata.

```sql
SELECT data FROM pg_logical_slot_get_changes('prod_slot', NULL, NULL,
  'format-version', '2',
  'include-lsn', '1',
  'include-schemas', '1'
);
```

--------------------------------

### Development/Debugging Configuration

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/configuration.md

Configures pg_recvlogical for development or debugging. It outputs format version 2 with human-readable formatting and includes full metadata for easier inspection.

```bash
pg_recvlogical -d postgres --slot slot_name --start \
  -o format-version=2 \
  -o pretty-print=1 \
  -o include-xids=1 \
  -o include-timestamp=1 \
  -o include-schemas=1 \
  -o include-types=1 \
  -f output.jsonl
```

--------------------------------

### Enable Debug Logging

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/postgresql-integration.md

Set the PostgreSQL log level to DEBUG1 to enable wal2json's debug logging, which includes option parsing and other detailed information.

```sql
SET log_min_messages = DEBUG1;
```

--------------------------------

### Interaction of Both Message Filters

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/filtering-selection.md

Demonstrates the combined effect of 'filter-msg-prefixes' and 'add-msg-prefixes', where filtering occurs first, then whitelisting.

```sql
SELECT data FROM pg_logical_slot_get_changes('slot', NULL, NULL,
  'filter-msg-prefixes', 'debug,test',
  'add-msg-prefixes', 'app,service'
);
```

--------------------------------

### Transaction Sequence in Format V2

Source: https://github.com/eulerto/wal2json/blob/master/_autodocs/output-formats.md

Shows how multiple transactions are represented in Format V2. Each transaction is enclosed by a 'B' (begin) and 'C' (commit) action with the same transaction ID (xid).

```json
{"action":"B","xid":100}...{"action":"C","xid":100}
{"action":"B","xid":101}...{"action":"C","xid":101}
{"action":"B","xid":102}...{"action":"C","xid":102}
```