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-binary
include-rewrites
(PostgreSQL 11 and later)include-sequences
(PostgreSQL 15 and later)include-timestamp
include-xids
only-local
(PostgreSQL 9.6 and later)skip-empty-xacts
stream-changes
(PostgreSQL 14 and later)
All options are boolean.
Change history
- PostgreSQL 15
- option
include-sequences
added (commit 80901b32)
- option
- PostgreSQL 14
- option
stream-changes
added (commit 7259736a)
- option
- PostgreSQL 11
- option
include-rewrites
added (commit 325f2ec5)
- option
- PostgreSQL 9.5
- option
only-local
added (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