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.

This entry was posted on Friday, June 3rd, 2005 at 11:04 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.