Check published and subscribed tables for PRIMARY KEYs

This problem can happen when you published some tables but later when you perform UPDATE operation you get error like this: ERROR: cannot update table “xxxxxx” because it does not have a replica identity and publishes updatesHINT: To enable updating the...

Event triggers in PostgreSQL 11

Based on documentation and several different examples on web I created this code for event triggers for PG 11. Maybe it can be useful for someone else too: — auditing table — CREATE TABLE IF NOT EXISTS public.ddl_history (ddl_date TIMESTAMP, ddl_tag TEXT,...

Repair referenced sequences on tables

Lately I had to repair one interesting problem. One colleague created staging schema and copied existing table structures but did not change sequences referenced in default values for IDs. So I had to repair it. I also had to set properly current value of staging...

Problems with autovacuum during high load on server

I last days we had big spikes in number of inserts on some of our databases causing rapid increase in “database age” (i.e. growth of transaction ID) and some of our databases have reached alerting level of transaction ID value set to 1 billion . See in the...

How to check blocked queries

Lately I had to several cases with the same reason – hanging selects blocking changes in data. All cases on PostgreSQL 11. It almost looks like PG 11 does not recognize that client connection ended and is not listening anymore – all hanging processes had...