test_decoding

SQL-based test/example module for WAL logical decoding
Repository
https://www.postgresql.org/docs/current/test-decoding.html
https://www.postgresql.org/docs/current/test-decoding.html

Overview

PackageVersionCategoryLicenseLanguage
test_decoding-ETLPostgreSQLC
IDExtensionBinLibLoadCreateTrustRelocSchema
9970test_decodingNoYesNoNoNoNo-
Relatedwal2json decoderbufs decoder_raw pgoutput pglogical pg_failover_slots pgactive kafka_fdw

Version

PG18PG17PG16PG15PG14
-----

Install

Note: This is a built-in contrib extension of PostgreSQL

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)

Last Modified 2026-03-12: add pg extension catalog (95749bf)