postgres-daily-check

Name: postgres-daily-check
Author: digoal

38stars12forks

How to install postgres-daily-check?

digoal/postgres_skill/postgres-daily-check

$npx -y skills add digoal/postgres_skill --skill postgres-daily-check

Installs into the current project.

Summary

This skill is designed to perform comprehensive daily health checks on a PostgreSQL database. It leverages a suite of specialized SQL queries and analysis logic to provide insights into database availability, performance, activity, maintenance, storage, replication, and archiving

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Daily Check Agent
2
3	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.
4
5	## Purpose
6
7	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.
8
9	## Core Capabilities
10
11	The agent performs checks across several key areas:
12
13	* Availability & Health: Verifying database responsiveness and detecting critical errors like invalid indexes, XID/MultiXactId wraparound risks, and deadlock occurrences.
14	* Performance & Activity: Monitoring active sessions, long-running queries, cache efficiency, transaction rollback rates, identifying application hotspots, and tracking temporary file usage.
15	* Security: Checking connection encryption status (SSL/GSSAPI) to ensure data transmission security.
16	* Replication & Archiving: Ensuring data consistency and recoverability by checking replication lag, slot status, and WAL archiving health.
17	* Maintenance & Storage: Analyzing disk usage, identifying bloat in tables and indexes, checking autovacuum activity, and monitoring sequence exhaustion.
18
19	## Workflow
20
21	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.
22
23	## Warning Strategy Guidelines
24
25	The default warning thresholds are designed for general-purpose use. You should adjust them based on your specific workload characteristics:
26
27	### OLTP Systems (Low Latency Required)
28	- Long-running queries: Set threshold to 1 minute or less
29	- Cache hit rate: Target >99%, warning at <97%
30	- Connection usage: More aggressive thresholds (WARNING at >70%, ERROR at >90%)
31
32	### Analytical/Reporting Systems
33	- Long-running queries: Higher thresholds acceptable (10-30 minutes)
34	- Cache hit rate: Lower hit rates expected due to large scans (warning at <90%)
35	- Temp file usage: Higher tolerance for temporary files
36
37	### General Recommendations
38	- bgwriter maxwritten_clean: Monitor trends over time rather than single values
39	- Checkpointer stats: Focus on average write/sync times per checkpoint rather than totals
40	- XID/MultiXactId wraparound: Always maintain conservative thresholds regardless of workload
41
42	All thresholds in `postgres_agent.py` can be customized by modifying the analysis logic.
43
44	## Available Skills
45
46	Each item below represents a callable skill within the `run_postgres_check.sh` script, designed to return structured JSON output.
47
48	---
49
50	## 1. Core Health & Availability
51
52	### Skill: `get_invalid_indexes`
53
54	- Description: Checks for any indexes that are in an invalid state. Invalid indexes can cause DML operations to fail or block.
55	- Usage: `./run_postgres_check.sh get_invalid_indexes`
56	- Expected Output:
57	```json
58	{
59	"skill": "get_invalid_indexes",
60	"status": "success",
61	"data": [
62	{"index_name": "idx_my_invalid_idx", "schema_name": "public"}
63	]
64	}
65	```
66	- Analysis: Reports ERROR if any invalid indexes are found.
67
68	### Skill: `get_xid_wraparound_risk`
69
70	- Description: Monitors the age of transaction IDs (XID) for each database to detect potential transaction ID wraparound issues.
71	- Usage: `./run_postgres_check.sh get_xid_wraparound_risk`
72	- Expected Output:
73	```json
74	{
75	"skill": "get_xid_wraparound_risk",
76	"status": "success",
77	"data": [
78	{"datname": "postgres", "xid_age": 1234567, "percentage_used": 0.05}
79	]
80	}
81	```
82	- Analysis: Reports CRITICAL ERROR if XID age >85%, WARNING if >70%.
83
84	### Skill: `get_multixid_wraparound_risk`
85
86	- 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.
87	- *Usage

People who liked this also viewed

6 picks

Type

postgres-daily-check

38stars12forks

View on GitHub

How to install postgres-daily-check?

digoal/postgres_skill/postgres-daily-check

$npx -y skills add digoal/postgres_skill --skill postgres-daily-check

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Daily Check Agent
2
3	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.
4
5	## Purpose
6
7	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.
8
9	## Core Capabilities
10
11	The agent performs checks across several key areas:
12
13	* Availability & Health: Verifying database responsiveness and detecting critical errors like invalid indexes, XID/MultiXactId wraparound risks, and deadlock occurrences.
14	* Performance & Activity: Monitoring active sessions, long-running queries, cache efficiency, transaction rollback rates, identifying application hotspots, and tracking temporary file usage.
15	* Security: Checking connection encryption status (SSL/GSSAPI) to ensure data transmission security.
16	* Replication & Archiving: Ensuring data consistency and recoverability by checking replication lag, slot status, and WAL archiving health.
17	* Maintenance & Storage: Analyzing disk usage, identifying bloat in tables and indexes, checking autovacuum activity, and monitoring sequence exhaustion.
18
19	## Workflow
20
21	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.
22
23	## Warning Strategy Guidelines
24
25	The default warning thresholds are designed for general-purpose use. You should adjust them based on your specific workload characteristics:
26
27	### OLTP Systems (Low Latency Required)
28	- Long-running queries: Set threshold to 1 minute or less
29	- Cache hit rate: Target >99%, warning at <97%
30	- Connection usage: More aggressive thresholds (WARNING at >70%, ERROR at >90%)
31
32	### Analytical/Reporting Systems
33	- Long-running queries: Higher thresholds acceptable (10-30 minutes)
34	- Cache hit rate: Lower hit rates expected due to large scans (warning at <90%)
35	- Temp file usage: Higher tolerance for temporary files
36
37	### General Recommendations
38	- bgwriter maxwritten_clean: Monitor trends over time rather than single values
39	- Checkpointer stats: Focus on average write/sync times per checkpoint rather than totals
40	- XID/MultiXactId wraparound: Always maintain conservative thresholds regardless of workload
41
42	All thresholds in `postgres_agent.py` can be customized by modifying the analysis logic.
43
44	## Available Skills
45
46	Each item below represents a callable skill within the `run_postgres_check.sh` script, designed to return structured JSON output.
47
48	---
49
50	## 1. Core Health & Availability
51
52	### Skill: `get_invalid_indexes`
53
54	- Description: Checks for any indexes that are in an invalid state. Invalid indexes can cause DML operations to fail or block.
55	- Usage: `./run_postgres_check.sh get_invalid_indexes`
56	- Expected Output:
57	```json
58	{
59	"skill": "get_invalid_indexes",
60	"status": "success",
61	"data": [
62	{"index_name": "idx_my_invalid_idx", "schema_name": "public"}
63	]
64	}
65	```
66	- Analysis: Reports ERROR if any invalid indexes are found.
67
68	### Skill: `get_xid_wraparound_risk`
69
70	- Description: Monitors the age of transaction IDs (XID) for each database to detect potential transaction ID wraparound issues.
71	- Usage: `./run_postgres_check.sh get_xid_wraparound_risk`
72	- Expected Output:
73	```json
74	{
75	"skill": "get_xid_wraparound_risk",
76	"status": "success",
77	"data": [
78	{"datname": "postgres", "xid_age": 1234567, "percentage_used": 0.05}
79	]
80	}
81	```
82	- Analysis: Reports CRITICAL ERROR if XID age >85%, WARNING if >70%.
83
84	### Skill: `get_multixid_wraparound_risk`
85
86	- 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.
87	- *Usage

People who liked this also viewed

6 picks

Type