Skip to content

Instantly share code, notes, and snippets.

@kendru
Last active February 25, 2022 18:53
Show Gist options
  • Save kendru/cd75e420262786951ef939fdd36fc2d2 to your computer and use it in GitHub Desktop.
Save kendru/cd75e420262786951ef939fdd36fc2d2 to your computer and use it in GitHub Desktop.
Useful snippets when working with logical replication in Postgres
-- Get replication identity for a table.
SELECT CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END AS replica_identity
FROM pg_class
WHERE oid = 'my_table'::regclass;
-- Note 'my_table'::regclass is equivalent to (SELECT oid FROM pg_class WHERE relname = 'my_table')
-- Create a publication.
CREATE PUBLICATION cdc;
-- ...capturing only INSERTs
CREATE PUBLICATION cdc WITH (publish = 'insert');
-- Add a table to a publication.
ALTER PUBLICATION cdc ADD TABLE my_table;
-- List publications.
SELECT * FROM pg_publication;
-- List tables in a publication.
SELECT * FROM pg_publication_tables where pubname = 'my_publication';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment