Extensions
test_decoding

test_decoding

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
decoder_raw
pgoutput
pglogical
pg_failover_slots
pgactive
kafka_fdw

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)
Last updated on
pg_bulkloadpgoutput