Useful snippets when working with logical replication in Postgres

pg-logical-snippets.sql

-- 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';