Logical replication using text_decoding and pgoutput

logical-replication.md

Commands to play with logical replication

Using test_decoding plug-in

SELECT pg_create_logical_replication_slot('demo_slot', 'test_decoding');

SELECT pg_logical_emit_message(true, 'context', 'Hello World!');

SELECT pg_logical_slot_peek_changes('demo_slot', NULL, NULL, 'include-xids', '0');

SELECT pg_logical_slot_get_changes('demo_slot', NULL, NULL);

SELECT pg_drop_replication_slot('demo_slot');

Using pgoutput plug-in

-- For emited messages, it is OK to use a nonexisting publication from pg_logical_slot_(peek|get)_binary_changes,
-- but if other changes are published, peek and get fail with 'publication does not exist'.
CREATE PUBLICATION SomePublication; 
SELECT pg_create_logical_replication_slot('pgout_slot','pgoutput');

SELECT pg_logical_emit_message(true, 'aContext', 'a transactional message');

SELECT get_byte(data, 1), encode(substr(data, 23, 23), 'escape')  FROM pg_logical_slot_peek_binary_changes('pgout_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'SomePublication', 'messages', 'true') OFFSET 1 LIMIT 1;

-- Next call clears the change log but does not return any data
SELECT pg_logical_slot_get_binary_changes('pgout_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'SomePublication');

SELECT pg_drop_replication_slot('pgout_slot');
DROP PUBLICATION SomePublication;

List replication slots and publications

SELECT * FROM pg_replication_slots;
SELECT * FROM pg_publication;

Relevant links:

• The Wonders of Postgres Logical Decoding Messages
• Logical Replication Improvements In PostgreSQL-14
• Getting postgres logical replication changes using pgoutput plugin
• pgPedia Logical replication