Postgres operations

v0.2.18

Deep Postgres operations — activity introspection, session/lock diagnostics, table + index analytics (bloat, dead tuples, unused indexes), WAL + replication state, progress views, EXPLAIN, and a curated set of operator-tier mutators (cancel/terminate backend, ANALYZE, VACUUM, REINDEX CONCURRENTLY). Authenticates via PG* env vars on the runner host.

41 allowed by default 7 need approval by default

Pack ID

postgres

Vendor

emisar

linux

Actions

Required binaries. Install these on the host before relying on the pack — an action that calls a missing one fails at run time.


              psql

Install

emisar pack install fetches this pack, re-validates it, and verifies its content hash against the --hash below — the exact bytes this page was rendered against, so a tampered copy is rejected — before copying it into the runner's packs dir. The command reloads a running daemon itself; no manual restart.

on the runner host

sudo emisar pack install postgres --hash sha256:20be09890329e4de3ab5377065741dd8ede6bd004e445488cefb0be2a8949495

Setup

Authenticates via libpq PG* environment variables on the runner host. psql reads them directly — the runner only forwards the ones you allowlist in inherit_env .

Environment

Set these on the runner host, then add each name to execution.inherit_env so the value reaches the action.

PGHOST required

Server host or socket directory.
PGPORT default 5432

Server port.
PGUSER required

Role to connect as; needs privileges for the actions you enable.
PGPASSWORD

Password for PGUSER . Omit if using ~/.pgpass or trust/peer auth.
PGDATABASE default same as `PGUSER`

Default database.

Notes

Create the role with CREATE ROLE emisar LOGIN PASSWORD '...'; GRANT pg_monitor TO emisar; — pg_monitor covers the read actions without handing out superuser.
Alternative to PGPASSWORD : put credentials in ~/.pgpass (mode 0600) on the runner host — read from disk, so it needs no inherit_env entry.
High-risk mutators (terminate_backend, vacuum_table, reindex_concurrent) need a role with the matching privileges.

Verify it works

Runs postgres.uptime, a low-risk read that confirms the pack can reach its target. Run it on the host once the pack is installed; pack install runs it for you.

on the runner host

sudo emisar pack verify postgres

Install and configure a pack walks through the whole sequence on a host.

Actions 48 total

View on GitHub

postgres.activity_detail exec medium

pg_stat_activity (per-backend)

Show per-backend detail: pid, user, app, state, wait_event, query age, query text. Rated medium because the output carries live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.activity_states exec low

Backend counts by state

Show a quick "how many idle / active / idle-in-transaction" health snapshot.
View source on GitHub
postgres.analyze_table exec medium

ANALYZE <schema>.<table>

Refresh planner statistics for one table.
View source on GitHub
postgres.backend_holding_xmin exec medium

Backend holding the oldest xmin

Show the backend whose snapshot is preventing vacuum from cleaning dead tuples cluster-wide. Rated medium because the output carries live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.bgwriter_stats exec low

pg_stat_bgwriter

Show background writer + checkpoint stats since stats reset.
View source on GitHub
postgres.cancel_query exec high

Cancel a running query

Send SIGINT to one backend via `pg_cancel_backend(pid)`. The query aborts but the connection survives. Use to clear a stuck SELECT or a blocker surfaced by `postgres.locks`. If cancel doesn't take effect within seconds the backend is likely stuck in a kernel call — escalate to `kill_idle` (terminate) only as a last resort.
View source on GitHub
postgres.connections exec low

Postgres connection summary

Count pg_stat_activity rows grouped by state and application_name. Surfaces idle-in-transaction backends, connection storms, and per-app traffic skew. Read-only. Pair with `postgres.kill_idle` if the long-idle-in-transaction count is non-zero.
View source on GitHub
postgres.database_stats exec low

pg_stat_database

Show per-DB: commits/rollbacks, blks_read/hit, deadlocks, conflicts, temp file usage.
View source on GitHub
postgres.db_sizes exec low

All databases by size

List every database with pg_database_size.
View source on GitHub
postgres.dead_tuples_top exec low

Top tables by dead-tuple ratio

List tables where n_dead_tup / (n_live_tup + n_dead_tup) is high. Bloat suspects.
View source on GitHub
postgres.duplicate_indexes exec low

Duplicate indexes (same column set)

List indexes covering identical columns. One per group is redundant; drop after verifying.
View source on GitHub
postgres.explain_analyze exec high

EXPLAIN ANALYZE (FORMAT JSON) <query>

Run EXPLAIN ANALYZE — the query IS executed (with timing). Forced into a read-only transaction (default_transaction_read_only=on) so data-modifying CTEs and volatile writes are rejected by the server, but the read still runs and can be expensive. High-risk because it executes an arbitrary operator-supplied query on the live database; use explain_query for a plan without execution.
View source on GitHub
postgres.explain_query exec low

EXPLAIN (FORMAT JSON) <query>

Show the plan-only EXPLAIN. The query is NOT executed.
View source on GitHub
postgres.extensions_installed exec low

Installed extensions

List currently-loaded extensions with versions + schema.
View source on GitHub
postgres.hot_update_ratio exec low

HOT update ratio per table

Show HOT update ratio per table. HOT updates avoid index work and bloat. Low ratio on a hot table = missing fillfactor tuning or wrong index.
View source on GitHub
postgres.idle_in_transaction exec medium

Idle-in-transaction backends

List backends sitting in 'idle in transaction' state — they hold locks + bloat vacuum's xmin horizon. Rated medium because the output carries live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.index_sizes exec low

Top 50 indexes by size

List the largest indexes — candidates for bloat investigation.
View source on GitHub
postgres.invalid_indexes exec low

Invalid indexes (indisvalid = false)

List indexes from failed CREATE INDEX CONCURRENTLY — present but not used by the planner.
View source on GitHub
postgres.is_in_recovery exec low

pg_is_in_recovery()

Show whether this instance is a replica (boolean — true if in recovery).
View source on GitHub
postgres.kill_idle exec high

Terminate idle-in-transaction backends

Call `pg_terminate_backend(pid)` on every backend that has been in `idle in transaction` state longer than `older_than_seconds`. Frees up the locks they're holding. Application code on the killed connections will see "server closed the connection unexpectedly" and reconnect — any in-flight transaction rolls back. Always check the count via `postgres.connections` first. Do not run during normal traffic.
View source on GitHub
postgres.largest_tables_full exec low

Largest tables with vacuum/dead-tuple info

List top 50 tables by size with live + dead tuple counts and last vacuum timestamps.
View source on GitHub
postgres.last_vacuum_per_table exec low

Last vacuum/analyze per table

List tables ordered by oldest last-vacuum — find ones autovacuum hasn't touched.
View source on GitHub
postgres.lock_blocking_chains exec medium

Blocker → blocked chains

List each blocked backend with its blocker. Use to find the head of a stuck lock chain. Rated medium because the output carries live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.locks exec medium

Blocking lock graph

Show who's blocking whom. Joins pg_locks with pg_stat_activity to show blocker_pid → blocked_pid pairs plus the truncated SQL of each side. Read-only. Use before a `cancel_query`; you want to cancel the blocker, not the victim. Rated medium because the truncated SQL of each side is live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.longest_running_queries exec medium

Top 20 by query age

List backends in 'active' state, oldest first. Use to spot stuck/runaway work. Rated medium because the output carries live query text, which can include literal request values no redaction list can enumerate.
View source on GitHub
postgres.pg_hba_rules exec low

pg_hba_file_rules

List effective pg_hba rules as the server loaded them. Catches syntax errors that didn't make it in.
View source on GitHub
postgres.pg_stat_statements_reset exec medium

pg_stat_statements_reset()

Clear accumulated pg_stat_statements counters. Use to start a clean measurement window.
View source on GitHub
postgres.pg_stat_statements_top exec low

Top statements by total time

Show the top 30 normalized queries by total_exec_time. Requires pg_stat_statements extension to be loaded. Stays low — normalized queries replace literals with `$1`/`$2`, so the output is the query shape, not real request data.
View source on GitHub
postgres.progress_create_index exec low

pg_stat_progress_create_index

Show in-flight CREATE INDEX operations with phase + blocks scanned.
View source on GitHub
postgres.progress_vacuum exec low

pg_stat_progress_vacuum

Show in-flight VACUUMs with phase + heap_blks_scanned.
View source on GitHub
postgres.reindex_concurrent exec high

REINDEX INDEX CONCURRENTLY <schema>.<index>

Rebuild one index without blocking writes. Slower than plain REINDEX but no AccessExclusiveLock.
View source on GitHub
postgres.reload_conf exec high

Reload postgresql.conf

Call `pg_reload_conf()`. Re-reads the server config without restarting. Picks up changes to settings whose context is `sighup` (logging, autovacuum, work_mem, etc.); does NOT pick up settings marked `postmaster` (shared_buffers, listen_addresses) — those still require a restart. Safe in steady state but considered high-risk because a malformed config can break logging or reset connection limits.
View source on GitHub
postgres.replication_lag exec low

Replication lag (primary view)

Show replication slot health from the primary's perspective. Surfaces sent/write/flush/replay LSNs plus the lag in bytes per replica. Run on the primary. Read-only. A lag >10 MB or a stalled flush_lsn is the usual signal that a downstream replica is in trouble.
View source on GitHub
postgres.replication_slots exec low

pg_replication_slots

List logical + physical replication slots with retained WAL. Inactive slots that retain WAL forever are a disk-full risk.
View source on GitHub
postgres.seq_scan_offenders exec low

Tables with high seq-scan ratio

List tables where seq_scan / (seq_scan + idx_scan) > 50% AND seq_tup_read > 100k. Candidates for missing indexes.
View source on GitHub
postgres.settings_non_default exec low

pg_settings (non-default)

List settings the operator has changed from the compiled defaults.
View source on GitHub
postgres.slow_queries exec low

Top slow queries from pg_stat_statements

List the top N query fingerprints by mean execution time from pg_stat_statements. Requires the extension to be loaded (shared_preload_libraries = 'pg_stat_statements'); errors out cleanly if it isn't. Read-only. Stays low — pg_stat_statements fingerprints replace literals with `$1`/`$2`, so the output is the query shape and table/column names, not real request data.
View source on GitHub
postgres.ssl_connections exec low

pg_stat_ssl

List per-backend TLS state: version, cipher, client_serial.
View source on GitHub
postgres.table_io exec low

pg_statio_user_tables

Show per-table heap + index buffer reads vs hits. Bad cache hit rate? Find the table.
View source on GitHub
postgres.table_sizes exec low

Top tables by total size

List the top N tables by total size (heap + indexes + toast) for one schema. Use to find the table that's dominating disk before recommending vacuum, archive, or partitioning. Read-only.
View source on GitHub
postgres.terminate_backend exec high

pg_terminate_backend(pid)

Hard-disconnect one backend (SIGTERM). Use when pg_cancel_backend isn't enough (e.g., idle in transaction with a long held lock).
View source on GitHub
postgres.unused_indexes exec low

Unused indexes (idx_scan = 0)

List indexes never used since last stats reset. Drop candidates — but verify they're not for an unrelated path (e.g., uniqueness constraint).
View source on GitHub
postgres.uptime exec low

Postgres uptime and version

Show server uptime, version, and current connection count. Reads pg_stat_database + pg_postmaster_start_time(). Use as a first-touch sanity check before deeper diagnosis. Read-only.
View source on GitHub
postgres.vacuum_status exec low

Autovacuum + bloat snapshot

Show last-vacuum/last-autovacuum timestamps and dead-tuple counts for the top N tables in one schema, ordered by dead tuples. Use to decide whether to run VACUUM manually or tune autovacuum. Read-only.
View source on GitHub
postgres.vacuum_table exec high

VACUUM <schema>.<table>

Reclaim dead-tuple space in one table. Non-blocking (ShareUpdateExclusiveLock). Use VACUUM ANALYZE if planner stats are also stale.
View source on GitHub
postgres.wal_archive_status exec low

pg_stat_archiver

Show WAL archiver stats: archived/failed counts, last archived WAL, last failure.
View source on GitHub
postgres.wal_status exec low

Current WAL LSN + recovery state

Show a snapshot of current WAL LSN, last receive/replay LSNs, recovery state.
View source on GitHub
postgres.xid_wraparound_proximity exec low

How close are we to XID wraparound?

Show per-database age(datfrozenxid). 2^31 (~2.1B) is the wraparound limit. >1B = pay attention; >1.8B = emergency.
View source on GitHub

← Back to registry

Pack reference Publish your own