Posts

Back Up All PostgreSQL Databases with pg_dumpall Before a major-version upgrade or a disaster-recovery rehearsal, a DBA needs a single artifact that captures the whole cluster: every database, plus the global objects that live outside any one database. pg_dumpall produces exactly that — one plain-SQL script that …

PostgreSQL Advisory Locks with pg_advisory_lock Two scheduled jobs fire at the same minute and both try to process the same nightly batch. Nothing in the data stops them — there is no single row to lock, because the work has not produced rows yet. Row and table locks are the wrong tool here. What you want is a named …

List All PostgreSQL Triggers with Their State A disabled trigger is invisible until something it should have done goes missing. The audit row that never got written, the derived column that stopped updating, the constraint check that silently stopped firing — each traces back to a trigger left in the disabled state …

PostgreSQL Column-Level Permissions Audit Query Which roles can read which columns? Table-level grants are easy to list, but they hide a finer layer of access. A role denied SELECT on a table can still hold SELECT on two of its columns, and that is exactly where personal data quietly stays reachable after a wider grant …

List PostgreSQL Wait Events with the pg_wait_events View PostgreSQL 17 added pg_wait_events, a system view that enumerates every wait event the running server can record. Prior to 17 the same information lived only in the documentation — no in-database way to confirm a wait name or attach a description to it. The view …

Identify Running PostgreSQL Autovacuum Worker Sessions Autovacuum lag is a slow accumulation. Every UPDATE rewrites the row and creates a new HOT chain, the launcher falls behind during write spikes, and over weeks a busy OLTP cluster ends up with three workers stuck on a handful of huge tables while smaller ones wait …

Monitor PostgreSQL WAL Archiver Status with pg_stat_archiver pg_stat_archiver is the canonical place to check whether PostgreSQL's continuous archiving is keeping up — a single-row view that exposes last_archived_wal, last_failed_wal, and the rolling failure count without parsing log files. On a healthy cluster the row …

Monitor PostgreSQL Replication Lag with pg_stat_replication A standby lagging more than a few seconds is rarely a transient hiccup — it is a query backlog on the standby, a network ceiling between the nodes, a misconfigured hot_standby_feedback, or a single-threaded apply storm. pg_stat_replication joined to …

Tune PostgreSQL Background Writer with pg_stat_bgwriter Where pg_stat_statements does query-level aggregation and pgBadger does after-the-fact log analysis, pg_stat_bgwriter operates at the buffer-cache layer — answering whether the bgwriter and checkpointer are keeping shared_buffers clean enough that foreground …

Understanding PostgreSQL Wait Events for Performance Analysis Every active PostgreSQL backend is either executing work or waiting. When a session waits, PostgreSQL records the reason in pg_stat_activity using two columns: wait_event_type and wait_event. Aggregating these across all sessions gives a clear picture of …