PostgreSQL Operations
Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.
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
- Load
database-reliability for SLOs, error budgets, incident posture, and recovery evidence.
- 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.
- Pair with
postgresql-engineering when schema or SQL changes are part of the fix.
- 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.
1---2name: postgresql-operations3description: 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.4---56# PostgreSQL Operations7Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.89Use 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.1011<!-- dual-compat-start -->12## Use When1314- Troubleshooting slow queries, lock contention, bloat, vacuum pressure, replication lag, or connection exhaustion.15- Designing backup, restore, HA, maintenance, monitoring, or capacity plans for PostgreSQL.16- Reviewing production database configuration, operational runbooks, and incident evidence.1718## Do Not Use When1920- The task is unrelated to this parent skill or is better handled by a narrower active parent named in the workflow.21- The request only needs a trivial answer and no reference module needs to be loaded.2223## Required Inputs2425- Gather the concrete system, repository, environment, constraints, and deliverable before loading references.26- Identify which absorbed reference file is needed; do not load every migrated reference by default.27## Workflow28291. Load `database-reliability` for SLOs, error budgets, incident posture, and recovery evidence.302. Load the task-specific reference:31 - `references/postgresql-administration.md` for administration, backup, restore, roles, maintenance, and replication.32 - `references/postgresql-performance.md` for query tuning, indexing, EXPLAIN review, statistics, vacuum, and capacity work.333. Pair with `postgresql-engineering` when schema or SQL changes are part of the fix.344. Pair with `observability-monitoring` when dashboards, alerting, traces, or metrics need implementation.3536## Quality Standards3738- No operational recommendation without a measurement path and rollback plan.39- State RPO, RTO, maintenance window, blast radius, and verification command for every risky operation.40- Prefer small, testable changes over broad tuning sweeps.4142## Anti-Patterns4344- Treating absorbed reference files as active skills or separate routing entrypoints.45- Loading every migrated child reference instead of the one that matches the task.46- Producing generic advice without constraints, evidence, or next verification steps.47## Outputs4849- Runbook, tuning plan, incident analysis, backup/restore plan, or operational review with verification commands.5051## References5253- Load only the references/<old-skill>.md files named in the workflow when their depth is required.54<!-- dual-compat-end -->5556## Inputs57| Input | Required | Purpose |58|---|---|---|59| PostgreSQL version, topology, and workload | yes | Bound operations |60| SLO, RPO, and RTO | yes | Set recovery policy |61| Metrics, logs, and backup inventory | yes | Ground diagnosis |6263## Capability contract64Diagnose read-only by default. Failover, restore, vacuum changes, session termination, replication repair, and configuration reloads require explicit authority and rollback criteria.6566## Degraded mode67If telemetry or privileged access is unavailable, return inspection commands and ranked hypotheses; do not infer root cause or recoverability.6869## Decision rules70| Condition | Action |71|---|---|72| Transaction blocks vacuum | Identify owner before termination |73| Replica lag threatens recovery | Protect WAL retention and investigate |74| Restore is untested | Report recovery objectives unverified |7576## Domain Anti-Patterns77- Killing sessions without owner review. Fix: assess impact first.78- Disabling autovacuum globally. Fix: tune per table from evidence.79- Treating replication as backup. Fix: keep independent tested backups.80- Reindexing a hot table blindly. Fix: choose an online-safe method.81- Deleting WAL to free disk. Fix: preserve recovery and repair retention.
peterbamuhigire