Implementing Logical Replication

wal_level = logical

max_replication_slots = 20
max_wal_senders       = 20

CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

ALTER PUBLICATION pub_orders ADD TABLE customers;
ALTER PUBLICATION pub_orders DROP TABLE order_items;

CREATE PUBLICATION pub_all FOR ALL TABLES;
-- PG 15+: FOR TABLES IN SCHEMA app

-- PG 15+ row filters & column lists
CREATE PUBLICATION pub_us
   FOR TABLE orders (id, status, total_cents) WHERE (region = 'US');

CREATE SUBSCRIPTION sub_orders
   CONNECTION 'host=primary dbname=shop user=repl password=...'
   PUBLICATION pub_orders;

ALTER SUBSCRIPTION sub_orders
   CONNECTION 'host=primary-new dbname=shop user=repl password=...';

ALTER SUBSCRIPTION sub_orders SET PUBLICATION pub_orders_v2;

CREATE SUBSCRIPTION sub_orders ... WITH (copy_data = true);   -- default
-- Skip copy if data already loaded:
CREATE SUBSCRIPTION sub_orders ... WITH (copy_data = false, create_slot = true);

SELECT subname, pid, received_lsn, latest_end_lsn, last_msg_send_time
  FROM pg_stat_subscription;
SELECT * FROM pg_subscription;

ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION;
-- Adds/removes tables on subscriber to match publisher

ALTER SUBSCRIPTION sub_orders DISABLE;
ALTER SUBSCRIPTION sub_orders SET (slot_name = NONE);  -- if slot already gone
DROP SUBSCRIPTION sub_orders;

DROP PUBLICATION pub_orders;