postgres-daily-check

# PostgreSQL Daily Check Agent This skill guides the agent in conducting a thorough daily health check of a PostgreSQL database instance. It executes a series of specialized queries to gather critical metrics and identifies potential issues, presenting them in a structured report. ## Purpose The primary goal of this skill is to empower the agent to proactively monitor the health and performance of a PostgreSQL database, alerting users to anomalies or potential problems before they escalate. It acts as an automated DBA, performing routine inspections efficiently. ## Core Capabilities The agent performs checks across several key areas: * **Availability & Health**: Verifying database responsiveness and detecting critical errors like invalid indexes, XID/MultiXactId wraparound risks, and deadlock occurrences. * **Performance & Activity**: Monitoring active sessions, long-running queries, cache efficiency, transaction rollback rates, identifying application hotspots, and tracking temporary file usage. * **Security**: Checking connection encryption status (SSL/GSSAPI) to ensure data transmission security. * **Replication & Archiving**: Ensuring data consistency and recoverability by checking replication lag, slot status, and WAL archiving health. * **Maintenance & Storage**: Analyzing disk usage, identifying bloat in tables and indexes, checking autovacuum activity, and monitoring sequence exhaustion. ## Workflow When activated, this skill executes a predefined sequence of checks. Each check involves calling a specialized script (`run_postgres_check.sh`) which executes specific SQL queries against the target PostgreSQL database. The results are then analyzed by the agent, and a comprehensive Markdown report is generated. ## Warning Strategy Guidelines The default warning thresholds are designed for general-purpose use. You should adjust them based on your specific workload characteristics: ### OLTP Systems (Low Latency Required) - **Long-running queries**: Set threshold to 1 minute or less - **Cache hit rate**: Target >99%, warning at <97% - **Connection usage**: More aggressive thresholds (WARNING at >70%, ERROR at >90%) ### Analytical/Reporting Systems - **Long-running queries**: Higher thresholds acceptable (10-30 minutes) - **Cache hit rate**: Lower hit rates expected due to large scans (warning at <90%) - **Temp file usage**: Higher tolerance for temporary files ### General Recommendations - **bgwriter maxwritten_clean**: Monitor trends over time rather than single values - **Checkpointer stats**: Focus on average write/sync times per checkpoint rather than totals - **XID/MultiXactId wraparound**: Always maintain conservative thresholds regardless of workload All thresholds in `postgres_agent.py` can be customized by modifying the analysis logic. ## Available Skills Each item below represents a callable skill within the `run_postgres_check.sh` script, designed to return structured JSON output. --- ## 1. Core Health & Availability ### Skill: `get_invalid_indexes` - **Description**: Checks for any indexes that are in an invalid state. Invalid indexes can cause DML operations to fail or block. - **Usage**: `./run_postgres_check.sh get_invalid_indexes` - **Expected Output**: ```json { "skill": "get_invalid_indexes", "status": "success", "data": [ {"index_name": "idx_my_invalid_idx", "schema_name": "public"} ] } ``` - **Analysis**: Reports ERROR if any invalid indexes are found. ### Skill: `get_xid_wraparound_risk` - **Description**: Monitors the age of transaction IDs (XID) for each database to detect potential transaction ID wraparound issues. - **Usage**: `./run_postgres_check.sh get_xid_wraparound_risk` - **Expected Output**: ```json { "skill": "get_xid_wraparound_risk", "status": "success", "data": [ {"datname": "postgres", "xid_age": 1234567, "percentage_used": 0.05} ] } ``` - **Analysis**: Reports CRITICAL ERROR if XID age >85%, WARNING if >70%. ### Skill: `get_multixid_wraparound_risk` - **Description**: Monitors the age of MultiXactId (multi-transaction IDs) for each database. Similar to XID wraparound, MultiXactId wraparound can also lead to data loss and requires monitoring. **Note**: `age(datminmxid) = 2147483647` (INT_MAX) indicates the MultiXactId is frozen or invalid, which is normal and not a risk. - **Usage**: `./run_postgres_check.sh get_multixid_wraparound_risk` - **Expected Output**: ```json { "skill": "get_multixid_wraparound_risk", "status": "success", "data": [ { "datname": "postgres", "mxid_age": 1234567, "datminmxid": 12345, "freeze_max_age": 400000000, "status": "OK", "remaining_to_autovacuum": 398765433 } ] } ``` - **Analysis**: - **FROZEN/INVALID**: `datminmxid = 0` or `age = INT_MAX` - MultiXactIds are frozen, no risk ✅ - **OK**: More than 30M MultiXactIds remaining before forced autovacuum ✅ - **WARNING**: Less than 40M remaining - Getting close to threshold 🟠 - **CRITICAL**: Less than 3M remaining - Approaching wraparound ❌ - **FORCE_AUTOVACUUM**: Age >= `autovacuum_multixact_freeze_max_age` - Autovacuum will be forced 🟠 - **Official Doc**: [PostgreSQL Routine Maintenance - Preventing Transaction ID Wraparound Failures](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) ### Skill: `get_blocking_locks` - **Description**: Identifies active blocking lock situations where one session holds a lock that another is waiting for. - **Usage**: `./run_postgres_check.sh get_blocking_locks` - **Expected Output**: ```json { "skill": "get_blocking_locks", "status": "success", "data": [ {"waiting_pid": 123, "waiting_user": "app_user", "blocking_pid": 456, "blocking_user": "db_admin"} ] } ``` - **Analysis**: Reports ERROR if any blocking locks are found. ### Skill: `get_deadlock_detection` - **Description**: Checks for deadlocks that have occurred since the last stats reset. - **Usage**: `./run_postgres_check.sh get_deadlock_detection` - **Expected Output**: ```json { "skill": "get_deadlock_detection", "status": "success", "data": [{"datname": "postgres", "deadlock_count": 0}] } ``` - **Analysis**: Reports ERROR if deadlock_count > 0. ### Skill: `get_critical_settings` - **Description**: Reviews critical PostgreSQL settings (fsync, synchronous_commit, etc.) for security and performance. - **Usage**: `./run_postgres_check.sh get_critical_settings` - **Expected Output**: ```json { "skill": "get_critical_settings", "status": "success", "data": [ {"name": "fsync", "setting": "on", "short_desc": "..."} ] } ``` - **Analysis**: Reports CRITICAL ERROR if fsync=off. ### Skill: `get_connection_security_status` - **Description**: Checks SSL/GSSAPI encryption status for active connections. Security audit to ensure connections use proper encryption. - **Usage**: `./run_postgres_check.sh get_connection_security_status` - **Expected Output**: ```json { "skill": "get_connection_security_status", "status": "success", "data": [ {"datname": "postgres", "usename": "app_user", "ssl_enabled": true, "ssl_version": "TLSv1.3", "connection_type": "SSL"} ] } ``` - **Analysis**: Reports WARNING if unencrypted remote TCP connections found. SSL or GSSAPI encryption recommended for production. - **Official Doc**: [PostgreSQL SSL Support](https://www.postgresql.org/docs/current/ssl-tcp.html), [pg_stat_ssl](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-SSL), [pg_stat_gssapi](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-GSSAPI) --- ## 2. Session & Connection Monitoring ### Skill: `get_long_running_queries` - **Description**: Finds active queries running longer than threshold (default 5 minutes for general use, 1 minute recommended for OLTP systems). - **Usage**: `./run_postgres_check.sh get_long_running_queries [threshold_minutes]` - **Parameters**: `threshold_minutes` - Threshold in minutes (default: 5) - **Expected Output**: ```json { "skill": "get_long_running_queries", "status": "success", "data": [ {"pid": 123, "usename": "app_user", "duration": "00:06:30", "query": "SELECT ..."} ] } ``` - **Analysis**: Reports WARNING if any long-running queries found. For OLTP systems with strict response time requirements, consider setting threshold to 1 minute. ### Skill: `get_idle_in_transaction_sessions` - **Description**: Detects sessions "idle in transaction" longer than threshold (default 1 minute). - **Usage**: `./run_postgres_check.sh get_idle_in_transaction_sessions [threshold_minutes]` - **Analysis**: Reports WARNING if any found. These hold locks and prevent VACUUM. ### Skill: `get_long_running_transactions` - **Description**: Finds non-idle transactions running longer than threshold (default 1 hour). - **Usage**: `./run_postgres_check.sh get_long_running_transactions [threshold_hours]` - **Analysis**: Lists transactions ordered by start time. ### Skill: `get_long_running_prepared_transactions` - **Description**: Finds prepared transactions (2PC) older than threshold. - **Usage**: `./run_postgres_check.sh get_long_running_prepared_transactions [threshold_hours]` - **Analysis**: Reports WARNING if any found. ### Skill: `get_connection_usage` - **Description**: Reports current connection count vs max_connections. - **Usage**: `./run_postgres_check.sh get_connection_usage` - **Analysis**: ERROR if >95%, WARNING if >80%. ### Skill: `get_lock_waiters` - **Description**: Detailed view of all sessions waiting for locks. - **Usage**: `./run_postgres_check.sh get_lock_waiters` - **Analysis**: WARNING if >5 lock waiters detected. ### Skill: `get_wait_events` - **Description**: Shows current wait events for active sessions (what resources they are waiting on). - **Usage**: `./run_postgres_check.sh get_wait_events` - **Analysis**: Lists top 10 wait events by occurrence. --- ## 3. Performance & Activity ### Skill: `get_cache_hit_rate` - **Description**: Calculates block cache hit rate. Low rate indicates memory pressure or bad queries. Threshold should be adjusted based on workload characteristics. - **Usage**: `./run_postgres_check.sh get_cache_hit_rate` - **Analysis**: WARNING if hit rate <95%. For some workloads, 90% may be acceptable. OLTP systems should target >99%, while analytical workloads may have lower hit rates due to large table scans. - **Note**: The 99% threshold may be too strict for certain workloads. Adjust based on your performance baseline. ### Skill: `get_rollback_rate` - **Description**: Calculates transaction rollback percentage. High rate (>5%) may indicate app issues. - **Usage**: `./run_postgres_check.sh get_rollback_rate` - **Analysis**: WARNING if rollback rate >5%. ### Skill: `get_top_sql_by_time` - **Description**: Top 5 queries by total execution time from pg_stat_statements. - **Usage**: `./run_postgres_check.sh get_top_sql_by_time` - **Note**: Requires `pg_stat_statements` extension. - **Analysis**: Lists queries with total time, avg time, and call count. ### Skill: `get_table_hotspots` - **Description**: Tables with highest DML and scan activity. - **Usage**: `./run_postgres_check.sh get_table_hotspots` - **Analysis**: Lists top 5 tables by total DML operations. ### Skill: `get_bgwriter_stats` - **Description**: Background writer statistics and buffer allocation. - **Usage**: `./run_postgres_check.sh get_bgwriter_stats` - **Analysis**: WARNING if maxwritten_clean remains high over time (persistent high values indicate bgwriter tuning issues). Occasional >0 values are normal. Consider monitoring the trend rather than single values. - **Note**: maxwritten_clean indicates the number of times the background writer stopped a cleaning scan because it had written too many buffers. While this can indicate insufficient bgwriter capacity, occasional occurrences are normal and not necessarily a concern. ### Skill: `get_temp_file_usage` - **Description**: Shows databases with temporary file usage. - **Usage**: `./run_postgres_check.sh get_temp_file_usage`