paste this link into your ai. it will know what to do.
Diagnoses and troubleshoots PostgreSQL database issues including slow queries, high CPU, memory pressure, connection problems, locks, deadlocks, disk space, vacuum, autovacuum, table bloat, index optimization, performance tuning, backup, recovery, pg_dump, replication, replication lag, failover, security, permissions, pg_hba.conf, and general database health monitoring. Triggers when users mention PostgreSQL problems, database performance issues, or any database administration tasks.
Managing PostgreSQL
Quick Diagnosis
What's the issue?
| Symptom | Reference |
|---|---|
| Database slow, queries taking too long | [reference/slow-queries.md](reference/slow-queries.md) |
| High CPU usage | [reference/high-cpu.md](reference/high-cpu.md) |
| Low memory, OOM errors | [reference/memory.md](reference/memory.md) |
| Too many connections, connection errors | [reference/connections.md](reference/connections.md) |
| Queries blocked, deadlocks | [reference/locks.md](reference/locks.md) |
| Disk space running low | [reference/disk-space.md](reference/disk-space.md) |
| Table bloat, dead tuples | [reference/vacuum.md](reference/vacuum.md) |
| Index issues, missing indexes | [reference/indexes.md](reference/indexes.md) |
| Need to tune configuration | [reference/tuning.md](reference/tuning.md) |
| Backup or recovery needed | [reference/backup.md](reference/backup.md) |
| Replication lag, failover | [reference/replication.md](reference/replication.md) |
| Security, permissions, access | [reference/security.md](reference/security.md) |
| General health check | [reference/monitoring.md](reference/monitoring.md) |
Prerequisites
Required PostgreSQL version: 9.6+ (specific features may require newer versions as noted)
Recommended extensions:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Query analysis
CREATE EXTENSION IF NOT EXISTS pgstattuple; -- Bloat measurementNote: SQL queries can be run remotely via any PostgreSQL client. Shell commands require server filesystem access or cloud provider tools.
Emergency Quick Commands
Check What's Happening Now
-- Active queries (what's running?)
SELECT pid, state, NOW() - query_start AS duration, LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY duration DESC;
-- Blocked queries (who's waiting?)
SELECT pid, wait_event_type, wait_event, LEFT(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
-- Connection count
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;Kill a Problem Query
-- Cancel query (soft)
SELECT pg_cancel_backend(<pid>);
-- Terminate connection (hard)
SELECT pg_terminate_backend(<pid>);Check Resource Usage
-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;
-- Largest tables
SELECT schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size,
n_dead_tup AS dead_tuples
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
-- Connection utilization
SELECT count(*) AS current, current_setting('max_connections') AS max,
round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity;Common Workflows
Slow Query Investigation
Copy this checklist and track progress:
- [ ] Read reference/slow-queries.md
- [ ] Identify slow queries using pg_stat_statements
- [ ] Run EXPLAIN ANALYZE on problematic queries
- [ ] Check for missing indexes (reference/indexes.md)
- [ ] Check for stale statistics (run ANALYZE)Details: [reference/slow-queries.md](reference/slow-queries.md) | [reference/indexes.md](reference/indexes.md)
High CPU Investigation
Copy this checklist and track progress:
- [ ] Read reference/high-cpu.md
- [ ] Check active queries for long-running operations
- [ ] Check for lock contention (reference/locks.md)
- [ ] Check vacuum status (reference/vacuum.md)
- [ ] Review slow queries (reference/slow-queries.md)Details: [reference/high-cpu.md](reference/high-cpu.md) | [reference/locks.md](reference/locks.md) | [reference/vacuum.md](reference/vacuum.md)
Disk Space Emergency
Copy this checklist and track progress:
- [ ] Read reference/disk-space.md
- [ ] Identify largest tables and indexes
- [ ] Check for WAL accumulation (replication slots)
- [ ] Check for bloat (reference/vacuum.md)
- [ ] Consider dropping unused indexes (reference/indexes.md)Details: [reference/disk-space.md](reference/disk-space.md) | [reference/vacuum.md](reference/vacuum.md) | [reference/indexes.md](reference/indexes.md)
Connection Exhaustion
Copy this checklist and track progress:
- [ ] Read reference/connections.md
- [ ] Check connection states and sources
- [ ] Kill idle-in-transaction connections if needed
- [ ] Consider connection pooling (PgBouncer)Details: [reference/connections.md](reference/connections.md)
Database Maintenance
Copy this checklist and track progress:
- [ ] Vacuum bloated tables (reference/vacuum.md)
- [ ] Reindex bloated indexes (reference/indexes.md)
- [ ] Update statistics: ANALYZE
- [ ] Review autovacuum settingsDetails: [reference/vacuum.md](reference/vacuum.md) | [reference/indexes.md](reference/indexes.md)
Backup & Recovery
Copy this checklist and track progress:
- [ ] Read reference/backup.md
- [ ] Use pg_dump for logical backups
- [ ] Use pg_basebackup for physical backups
- [ ] Configure WAL archiving for PITRDetails: [reference/backup.md](reference/backup.md)
Replication Issues
Copy this checklist and track progress:
- [ ] Read reference/replication.md
- [ ] Check replication lag on primary
- [ ] Review replication slot status
- [ ] Check for WAL accumulationDetails: [reference/replication.md](reference/replication.md)
Key System Views
| View | Purpose |
|---|---|
pg_stat_activity | Current connections and queries |
pg_stat_user_tables | Table statistics, dead tuples |
pg_stat_user_indexes | Index usage statistics |
pg_stat_statements | Historical query statistics (extension) |
pg_locks | Current lock information |
pg_stat_replication | Replication status (primary) |
pg_stat_wal_receiver | Replication status (replica) |
pg_replication_slots | Replication slot status |
pg_settings | Configuration parameters |
Investigation Summary Template
After investigating any issue, provide a summary:
## Investigation Summary
**Issue Type:** [Slow queries / High CPU / Memory / Connections / Locks / Disk / etc.]
**Severity:** [Critical / Warning / Informational]
**Root Cause:**
[What caused the issue]
**Actions Taken:**
- [Action 1]
- [Action 2]
**Recommendations:**
1. [Short-term fix]
2. [Long-term improvement]
**Monitoring:**
[What to watch for]keep it where your ai can reach it.
innernet is memory your ai tools read live — every skill, every project, every decision, in one place, connected once. save this skill to yours, or publish one of your own as a link like this.