Run sanity checks to check source and destination is in sync

postgresql-sanity-checks-for-logical-replication.sql

-- Sanity check with schemas and tables --------------------------

-- Check counts of objects in the database
SELECT
	n.nspname as schema_name
	,CASE c.relkind
	   WHEN 'r' THEN 'table'
	   WHEN 'v' THEN 'view'
	   WHEN 'i' THEN 'index'
	   WHEN 's' THEN 'special'
	END as object_type
	,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','s')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
GROUP BY  n.nspname,
	CASE c.relkind
	   WHEN 'r' THEN 'table'
	   WHEN 'v' THEN 'view'
	   WHEN 'i' THEN 'index'
	   WHEN 's' THEN 'special'
	END
ORDER BY n.nspname,
	CASE c.relkind
	   WHEN 'r' THEN 'table'
	   WHEN 'v' THEN 'view'
	   WHEN 'i' THEN 'index'
	   WHEN 's' THEN 'special'
	END;
  

-- Check the counts of a couple of important tables

SELECT COUNT(*) FROM <TABLE_1>;
SELECT COUNT(*) FROM <TABLE_2>;
SELECT COUNT(*) FROM <TABLE_3>;

-- Check the max of a couple of important tables

SELECT MAX(id) FROM <TABLE_1>;
SELECT MAX(id) FROM <TABLE_2>;
SELECT MAX(id) FROM <TABLE_3>;