test_decoding
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:
force-binaryinclude-rewrites(PostgreSQL 11 and later)include-timestampinclude-xidsonly-local(PostgreSQL 9.6 and later)skip-empty-xactsstream-changes(PostgreSQL 14 and later)
All options are boolean.
Change history
- PostgreSQL 14
- option
stream-changesadded (commit 7259736a)
- option
- PostgreSQL 11
- option
include-rewritesadded (commit 325f2ec5)
- option
- PostgreSQL 9.5
- option
only-localadded (commit 5aa23504)
- option
- PostgreSQL 9.4
- added (commit b89e1510)
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)
For a more detailed example, see the PostgreSQL documentation section Logical Decoding Examples.
References
- PostgreSQL documentation: test_decoding
- PostgreSQL documentation: Logical Decoding Examples