Add a table to a publication and refresh the subscription (REFRESH PUBLICATION)

Command

-- Publisher:
ALTER PUBLICATION pub_app ADD TABLE orders;
-- PG15+: all tables in a schema: ALTER PUBLICATION pub_app ADD TABLES IN SCHEMA app_schema;
SELECT pubname, schemaname, tablename FROM pg_publication_tables WHERE pubname = 'pub_app';
-- Subscriber (the table structure must already exist):
ALTER SUBSCRIPTION sub_app REFRESH PUBLICATION WITH (copy_data = true);
SELECT srrelid::regclass AS table_name, srsubstate FROM pg_subscription_rel ORDER BY 1;

What it does

Adds a new table to the publication on the publisher; on the subscriber, REFRESH PUBLICATION includes the new tables in the subscription and starts the initial copy (tablesync) with copy_data = true. srsubstate: i = init, d = data copy, f = copy finished, s = synchronized, r = ready.

When to use it

When a new table is added to a running logical replication, or a table is removed from the publication.

Caution

Even with FOR ALL TABLES or TABLES IN SCHEMA publications, the subscriber does not pick up a new table until REFRESH is run. REFRESH fails if the table does not exist on the subscriber; DDL is not replicated. If a published table has no PRIMARY KEY or REPLICA IDENTITY, UPDATE/DELETE fail on the publisher. The initial copy of a large table holds a slot and snapshot on the publisher for a long time.