test_decoding

A contrib module providing a sample logical decoding output plugin

test_decoding is a contrib module providing sample output plugin code for logical decoding.

test_decoding was added in PostgreSQL 9.4.

Usage

test_decoding receives WAL changes via a logical replication slot and emits them as human-readable text. If does not have any practical applications, but serves as an example for developing output plugins.

Options

Although not documented, as of PostgreSQL 14 test_decoding accepts the following options, which can be provided via pg_logical_slot_get_changes() et al:

All options are boolean.

Change history

Examples

Create a logical replication slot with test_decoding specified as the plugin:

postgres=# SELECT * FROM pg_create_logical_replication_slot('test_slot_1', 'test_decoding');
  slot_name  |    lsn
-------------+-----------
 test_slot_1 | 0/3000758
(1 row)

Create some WAL activity, here by inserting a row into an existing table:

postgres=# INSERT INTO foo VALUES (1, clock_timestamp());
INSERT 0 1

This WAL activity can then be queried via the previously created logical replication slot using pg_logical_slot_get_changes():

postgres=# SELECT * FROM pg_logical_slot_get_changes('test_slot_1', NULL, NULL, 'include-xids', '0');
    lsn    | xid |                                       data
-----------+-----+-----------------------------------------------------------------------------------
 0/3023580 | 726 | BEGIN
 0/3023580 | 726 | table public.foo: INSERT: id[integer]:1 val[text]:'2021-08-09 10:00:38.622597+01'
 0/3023610 | 726 | COMMIT
(3 rows)

As the output plugin has now consumed the changes, a subsequent call to pg_logical_slot_get_changes() (assuming no further WAL was written) will return an empty result set:

postgres=# SELECT * FROM pg_logical_slot_get_changes('test_slot_1', NULL, NULL, 'include-xids', '0');
 lsn | xid | data
-----+-----+------
(0 rows)

Categories

Contrib module, Logical replication

See also

pg_logical_slot_get_changes(), pg_logical_slot_peek_changes(), pg_logical_slot_get_binary_changes(), pg_logical_slot_peek_binary_changes()