A Full Bundle

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.

PG Phriday: Perfectly Logical

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. But is it easy to use? Let’s give it a try on our trusty sensor_log table.