test_decoding

SQL-based test/example module for WAL logical decoding

PostgreSQL

CONTRIB

test_decoding : SQL-based test/example module for WAL logical decoding

Overview

ID	Extension	Package	Version	Category	License	Language
9970	test_decoding	test_decoding	`-`	ETL	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s----	No	Yes	No	No	no	no

Relationships
See Also	wal2json decoderbufs pgoutput wal2mongo decoder_raw pg_walinspect pg_logicalinspect kafka_fdw pgmqtt pgq

Packages

PG18	PG17	PG16	PG15	PG14
-	-	-	-	-

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

This extension does not need CREATE EXTENSION DDL command

Usage

test_decoding: SQL-based test/example module for WAL logical decoding

A built-in PostgreSQL logical decoding output plugin that produces text representations of WAL changes. Primarily used for testing and as a reference implementation.

Configuration

In postgresql.conf:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Using with SQL Functions

-- Create a logical replication slot
SELECT * FROM pg_create_logical_replication_slot('test_slot', 'test_decoding');

-- Perform some changes
CREATE TABLE test_table (id serial PRIMARY KEY, data text);
INSERT INTO test_table (data) VALUES ('hello');
UPDATE test_table SET data = 'world' WHERE id = 1;
DELETE FROM test_table WHERE id = 1;

-- Peek at changes (without consuming)
SELECT * FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL);

-- Get and consume changes
SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL);

Output Format

BEGIN 1234
table public.test_table: INSERT: id[integer]:1 data[text]:'hello'
table public.test_table: UPDATE: id[integer]:1 data[text]:'world'
table public.test_table: DELETE: id[integer]:1
COMMIT 1234

Using with pg_recvlogical

# Create slot
pg_recvlogical -d postgres --slot test_slot --create-slot -P test_decoding

# Stream changes
pg_recvlogical -d postgres --slot test_slot --start -f -

# Drop slot
pg_recvlogical -d postgres --slot test_slot --drop-slot

Options

Pass options as key-value pairs:

SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL,
    'include-xids', '1',
    'skip-empty-xacts', '1',
    'include-timestamp', '1'
);

include-xids - include transaction IDs in output
skip-empty-xacts - skip transactions with no changes
include-timestamp - include commit timestamps

Notes

Ships with PostgreSQL (contrib module, no separate installation needed)
Intended for testing and debugging logical decoding
For production CDC, use purpose-built plugins (wal2json, pgoutput, decoderbufs)