Chasing Nuts

If I have one major complaint of PostgreSQL/Slony-I it is that you have to explicitly tell it to replicate each table and sequence in your database. So many times I have talked to people that somehow missed an object and now are in a real pinch. With some help I now have two SELECT statements that I always keep handy when working with Slony.

SELECT relname
FROM pg_catalog.pg_class
LEFT JOIN pg_catalog.pg_namespace n ON (relnamespace=n.oid)
LEFT OUTER JOIN _CLUSTERNAME.sl_table ON(relfilenode=tab_reloid)
WHERE tab_reloid IS NULL
AND relkind IN (’r')
AND nspname NOT IN (’information_schema’, ‘pg_catalog’, ‘_CLUSTERNAME’)
ORDER BY relname ASC;


SELECT relname
FROM pg_catalog.pg_class
LEFT JOIN pg_catalog.pg_namespace n ON (relnamespace=n.oid)
LEFT OUTER JOIN _CLUSTERNAME.sl_sequence ON(relfilenode=seq_reloid)
WHERE seq_reloid IS NULL
AND relkind IN (’S')
AND nspname NOT IN (’information_schema’, ‘pg_catalog’, ‘_CLUSTERNAME’)
ORDER BY relname ASC;

Be sure you substitute CLUSTERNAME with the name of your Slony cluster. The first one finds any TABLE not being replicated and the second finds any SEQUENCE not being replicated.