Logical replication and change data capture

conceptThe concept

The publisher needs wal_level = logical. A publication lists tables (optionally with a row filter and column list, since 15). A subscription on another server connects, copies the initial data, then applies changes from a logical replication slot. Tables are matched by name; the subscriber can have extra columns and its own indexes.

UPDATE and DELETE need a replica identity to find the row on the subscriber: the primary key by default, a unique index, or FULL (whole old row, slow). Not replicated: DDL, sequence values (until PostgreSQL 19, which can publish sequences), TRUNCATE in older versions, and large objects. Schema changes must be applied to the subscriber first.

Change data capture tools such as Debezium use the same machinery (logical decoding with the pgoutput plugin) to stream changes into Kafka and other systems.

whyWhy it matters

Physical replication requires identical major versions and copies everything. Logical replication lets you upgrade from 13 to 18 with minutes of downtime, move one schema to a new cluster, or feed a reporting database with only the tables it needs.

Use caseReal-world scenario

The shop upgrades from 14 to 18: build an 18 cluster, load the schema, subscribe to all tables, wait for catch-up, stop writes, sync sequences, switch the application, and keep the old cluster as fallback.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE PUBLICATION shop_reporting
  FOR TABLE orders (id, customer_id, status, created_at, total) WHERE (status <> 'cancelled'),
            customers (id, country, tier);
SELECT pubname, schemaname, tablename, attnames, rowfilter FROM pg_publication_tables WHERE pubname = 'shop_reporting';
ROLLBACK;

SELECT c.relname,
       CASE c.relreplident WHEN 'd' THEN 'default (primary key)' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity,
       EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary) AS has_pk
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
ORDER BY 1;

-- on the new cluster, after loading the schema with pg_dump --schema-only
CREATE SUBSCRIPTION shop_upgrade
  CONNECTION 'host=old-primary dbname=shop user=replicator sslmode=verify-full'
  PUBLICATION shop_all
  WITH (copy_data = true, streaming = parallel);

-- watch catch-up
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time FROM pg_stat_subscription;

-- at cutover, sequences are not replicated: copy them
SELECT 'SELECT setval(' || quote_literal(schemaname || '.' || sequencename) || ', ' || last_value || ');'
FROM pg_sequences WHERE last_value IS NOT NULL;

internalsUnder the hood

Logical slots hold back catalog_xmin as well as WAL, so a stuck subscriber also bloats system catalogs. Subscription conflicts (for example a duplicate key on the subscriber) stop the apply worker until resolved; since 17, logical slots can be synchronised to physical standbys so subscriptions survive a publisher failover.

Avoid / preferDo this, not that

-- cut over to the subscriber and start inserting

SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));
-- on the subscriber, for every sequence, before taking writes

ALTER TABLE orders ADD COLUMN channel text;  -- on the publisher only

-- apply the DDL on the subscriber first, then on the publisher

PitfallsCommon mistakes

• Forgetting that sequences are not replicated. After cutover, new inserts collide with existing ids.
• Running DDL on the publisher without applying it to the subscriber first, which stops replication.
• Replicating tables without a primary key or replica identity, so UPDATE and DELETE fail on the publisher with an error once a publication includes the table.

LimitsWhen not to use it

Logical replication does not carry DDL, sequences or large objects. For a full physical copy of a cluster, streaming replication is simpler and complete.

SecuritySecurity notes

A publication exposes rows to any subscriber that can connect with the right role. Use row filters and column lists to publish only what the consumer needs, and since PostgreSQL 16 consider run_as_owner and subscription owner privileges carefully.

ChallengeTry it yourself

BEGIN;
CREATE PUBLICATION orders_pub FOR TABLE orders (id, customer_id, status, total)
  WHERE (status IN ('paid', 'delivered'));
SELECT pubname, tablename, attnames, rowfilter
FROM pg_publication_tables WHERE pubname = 'orders_pub';
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• High availability and replication
• Logical replication