PostgreSQL Logical Replication Cheat Sheet

-- Publisher; wal_level changes take effect after restart.
SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;

-- Subscriber; allow headroom beyond steady-state apply workers.
SHOW max_active_replication_origins;
SHOW max_logical_replication_workers;
SHOW max_worker_processes;

Note: Defaults may demonstrate the feature but are not a capacity plan. Include table synchronization and parallel apply in the worker budget.

CREATE PUBLICATION customer_eu_pub
FOR TABLE app.customers (customer_id, region, display_name, updated_at)
  WHERE (region = 'EU')
WITH (publish = 'insert, update, delete');

SELECT pubname, pubinsert, pubupdate, pubdelete, pubtruncate
FROM pg_publication
WHERE pubname = 'customer_eu_pub';

Note: customer_id and region must satisfy the replica-identity rules for published updates and deletes. Test transitions into and out of the filter: PostgreSQL can transform such updates into inserts or deletes.

ALTER TABLE app.events
  ALTER COLUMN tenant_id SET NOT NULL,
  ALTER COLUMN event_id SET NOT NULL;

CREATE UNIQUE INDEX events_replica_key
ON app.events (tenant_id, event_id);

ALTER TABLE app.events
  REPLICA IDENTITY USING INDEX events_replica_key;

SELECT n.nspname, c.relname, c.relreplident
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'app' AND c.relname = 'events';

Note: Setting NOT NULL validates existing data and takes a strong table lock, so plan it separately on a busy table. The replica index must be unique, immediate, non-partial, and use only NOT NULL columns.

-- In psql, obtain the complete conninfo through a protected deployment channel.
\prompt -s 'Publisher conninfo: ' publisher_conninfo
CREATE SUBSCRIPTION app_sub
CONNECTION :'publisher_conninfo'
PUBLICATION app_pub
WITH (copy_data = true, streaming = parallel, run_as_owner = false);
\unset publisher_conninfo

SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
WHERE srsubid = (SELECT oid FROM pg_subscription WHERE subname = 'app_sub');

Note: For a non-superuser-owned PostgreSQL 18 subscription with password_required=true, the password must be in conninfo and is stored in pg_subscription; inject it through a protected administrative channel and tightly restrict catalog access.

SELECT p.pubname, n.nspname, c.relname
FROM pg_publication_rel AS pr
JOIN pg_publication AS p ON p.oid = pr.prpubid
JOIN pg_class AS c ON c.oid = pr.prrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE p.pubname = 'app_pub'
ORDER BY n.nspname, c.relname;

-- Run on the subscriber after compatible target tables exist.
ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION WITH (copy_data = true);

-- Publisher, in psql. Add a narrow pg_hba.conf rule separately.
CREATE ROLE logical_sub LOGIN REPLICATION;
\password logical_sub
GRANT CONNECT ON DATABASE app TO logical_sub;
GRANT USAGE ON SCHEMA app TO logical_sub;
GRANT SELECT ON app.customers, app.orders TO logical_sub;

-- Subscriber: retain the safer PostgreSQL 18 execution default.
ALTER SUBSCRIPTION app_sub
  SET (run_as_owner = false, password_required = true);

Note: Do not paste a real password into migration history. Validate current PostgreSQL security documentation when publisher roles do not have BYPASSRLS or when target tables use row-level security.

-- Subscriber
SELECT subname, worker_type, pid, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription
ORDER BY subname, worker_type;

-- Publisher
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'logical';

Note: A byte distance is not a time guarantee. Add an application-level freshness probe for the records your consumers actually require.

-- Subscriber: inspect whether the subscription requests failover handling.
SELECT subname, subenabled, subfailover
FROM pg_subscription
WHERE subname = 'app_sub';

-- Candidate publisher standby: verify the synchronized logical slot.
SELECT slot_name, slot_type, failover, synced, invalidation_reason, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'app_sub';

Note: Columns and failover-slot procedures are version-specific. Follow the documentation for the exact major release and never promote until slot synchronization and old-primary fencing are verified.