postgresql-operations

name	postgresql-operations
description	Use when administering, tuning, backing up, restoring, monitoring, or troubleshooting PostgreSQL production systems. Load the absorbed PostgreSQL administration and performance reference files for operational runbooks, query tuning, vacuum, replication, and incident response.
metadata	{"portable":true,"compatible_with":["claude-code","codex"]}

PostgreSQL Operations

Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, [phone].

Use this parent skill as the active PostgreSQL operations entrypoint. Keep operational decisions tied to evidence: metrics, logs, query plans, lock graphs, replication state, backup history, and recovery objectives.

Use When

• Troubleshooting slow queries, lock contention, bloat, vacuum pressure, replication lag, or connection exhaustion.
• Designing backup, restore, HA, maintenance, monitoring, or capacity plans for PostgreSQL.
• Reviewing production database configuration, operational runbooks, and incident evidence.

Do Not Use When

• The task is unrelated to this parent skill or is better handled by a narrower active parent named in the workflow.
• The request only needs a trivial answer and no reference module needs to be loaded.

Required Inputs

• Gather the concrete system, repository, environment, constraints, and deliverable before loading references.
• Identify which absorbed reference file is needed; do not load every migrated reference by default.

Workflow

1. Load database-reliability for SLOs, error budgets, incident posture, and recovery evidence.
2. Load the task-specific reference:
   • references/postgresql-administration.md for administration, backup, restore, roles, maintenance, and replication.
   • references/postgresql-performance.md for query tuning, indexing, EXPLAIN review, statistics, vacuum, and capacity work.
3. Pair with postgresql-engineering when schema or SQL changes are part of the fix.
4. Pair with observability-monitoring when dashboards, alerting, traces, or metrics need implementation.

Quality Standards

• No operational recommendation without a measurement path and rollback plan.
• State RPO, RTO, maintenance window, blast radius, and verification command for every risky operation.
• Prefer small, testable changes over broad tuning sweeps.

Anti-Patterns

• Treating absorbed reference files as active skills or separate routing entrypoints.
• Loading every migrated child reference instead of the one that matches the task.
• Producing generic advice without constraints, evidence, or next verification steps.

Outputs

• Runbook, tuning plan, incident analysis, backup/restore plan, or operational review with verification commands.

References

• Load only the references/.md files named in the workflow when their depth is required.

Inputs

Input	Required	Purpose
PostgreSQL version, topology, and workload	yes	Bound operations
SLO, RPO, and RTO	yes	Set recovery policy
Metrics, logs, and backup inventory	yes	Ground diagnosis

Capability contract

Diagnose read-only by default. Failover, restore, vacuum changes, session termination, replication repair, and configuration reloads require explicit authority and rollback criteria.

Degraded mode

If telemetry or privileged access is unavailable, return inspection commands and ranked hypotheses; do not infer root cause or recoverability.

Decision rules

Condition	Action
Transaction blocks vacuum	Identify owner before termination
Replica lag threatens recovery	Protect WAL retention and investigate
Restore is untested	Report recovery objectives unverified

Domain Anti-Patterns

• Killing sessions without owner review. Fix: assess impact first.
• Disabling autovacuum globally. Fix: tune per table from evidence.
• Treating replication as backup. Fix: keep independent tested backups.
• Reindexing a hot table blindly. Fix: choose an online-safe method.
• Deleting WAL to free disk. Fix: preserve recovery and repair retention.