Pglogical

In the process of working on a customer migration and came up with a neat query for finding tables that don’t have a primary key:

SELECT c.oid::REGCLASS::TEXT AS table
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  LEFT JOIN pg_constraint p ON (p.conrelid=c.oid AND p.contype = 'p')
 WHERE c.relkind IN ('p', 'r')
   AND n.nspname NOT IN ('pglogical', 'information_schema', 'bdr')
   AND n.nspname NOT LIKE 'pg\_%'
   AND p.conrelid IS NULL;

For anyone out there that is using pglogical, this is how you figure out if there are any tables that won’t work in the default replication set.

One of the things Postgres has been “missing” for a while is logical replication based on activity replay. Until fairly recently, in order to replicate single tables from one database to another, we had to encumber the table with performance-robbing triggers coupled to a third party daemon to manage transport. Those days might finally be behind us thanks to pglogical.