Query to determine the state of each table in logical replication for a subscription

postgresql-check-replication-state-in-subscription.sql

/*
 * To be run in the destination (subscriber) database.
 * It gives you the state of each replication relation in a subscription.
 * srsubstate: i = initialize, d = data is being copied, s = synchronized, r = ready (normal replication)
 * https://www.postgresql.org/docs/13/catalog-pg-subscription-rel.html
 */

SELECT a.subname, b.srsubstate, count(*)
FROM pg_subscription_rel b, pg_subscription a
WHERE b.srsubid=a.oid
GROUP BY 1,2,3
ORDER BY 2,3;

/*
 * Example output
 * If all values are in the `r` substate, the snapshot COPY is complete and data is normally 
 * replicating for all tables in the subscription.

   subname | srsubstate | count
 ----------+------------+-------
   sub_all | r          |  1035
(1 row)
*/