🌐 AI搜索 & 代理 主页
Skip to content

Commit bbd9679

Browse files
bogdan-tsbogdan-ts
committed
feat: Add Postgres version to report data
1 parent 3a12ede commit bbd9679

File tree

2 files changed

+173
-88
lines changed

2 files changed

+173
-88
lines changed

‎reporter/postgres_reports.py‎

Lines changed: 140 additions & 75 deletions
Original file line numberDiff line numberDiff line change
@@ -155,6 +155,62 @@ def query_instant(self, query: str) -> Dict[str, Any]:
155155
print(f"Query error: {e}")
156156
return {}
157157

158+
def _get_postgres_version_info(self, cluster: str, node_name: str) -> Dict[str, str]:
159+
"""
160+
Fetch and parse Postgres version information from pgwatch settings metrics.
161+
162+
Notes:
163+
- This helper is intentionally defensive: it validates the returned setting_name label
164+
(tests may stub query responses broadly by metric name substring).
165+
- Uses a single query with a regex on setting_name to reduce roundtrips.
166+
"""
167+
query = (
168+
f'last_over_time(pgwatch_settings_configured{{'
169+
f'cluster="{cluster}", node_name="{node_name}", '
170+
f'setting_name=~"server_version|server_version_num"}}[3h])'
171+
)
172+
173+
result = self.query_instant(query)
174+
version_str = None
175+
version_num = None
176+
177+
if result.get("status") == "success":
178+
if result.get("data", {}).get("result"):
179+
for item in result["data"]["result"]:
180+
metric = item.get("metric", {}) or {}
181+
setting_name = metric.get("setting_name", "")
182+
setting_value = metric.get("setting_value", "")
183+
if setting_name == "server_version" and setting_value:
184+
version_str = setting_value
185+
elif setting_name == "server_version_num" and setting_value:
186+
version_num = setting_value
187+
else:
188+
print(f"Warning: No version data found (cluster={cluster}, node_name={node_name})")
189+
else:
190+
print(f"Warning: Version query failed (cluster={cluster}, node_name={node_name}): status={result.get('status')}")
191+
192+
server_version = version_str or "Unknown"
193+
version_info: Dict[str, str] = {
194+
"version": server_version,
195+
"server_version_num": version_num or "Unknown",
196+
"server_major_ver": "Unknown",
197+
"server_minor_ver": "Unknown",
198+
}
199+
200+
if server_version != "Unknown":
201+
# Handle both formats:
202+
# - "15.3"
203+
# - "15.3 (Ubuntu 15.3-1.pgdg20.04+1)"
204+
version_parts = server_version.split()[0].split(".")
205+
if len(version_parts) >= 1 and version_parts[0]:
206+
version_info["server_major_ver"] = version_parts[0]
207+
if len(version_parts) >= 2:
208+
version_info["server_minor_ver"] = ".".join(version_parts[1:])
209+
else:
210+
version_info["server_minor_ver"] = "0"
211+
212+
return version_info
213+
158214
def generate_a002_version_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[str, Any]:
159215
"""
160216
Generate A002 Version Information report.
@@ -167,52 +223,7 @@ def generate_a002_version_report(self, cluster: str = "local", node_name: str =
167223
Dictionary containing version information
168224
"""
169225
print(f"Generating A002 Version Information report for cluster='{cluster}', node_name='{node_name}'...")
170-
171-
# Query PostgreSQL version information using last_over_time to get most recent values
172-
# Use 3h lookback to handle cases where metrics collection might be intermittent
173-
version_queries = {
174-
'server_version': f'last_over_time(pgwatch_settings_configured{{cluster="{cluster}", node_name="{node_name}", setting_name="server_version"}}[3h])',
175-
'server_version_num': f'last_over_time(pgwatch_settings_configured{{cluster="{cluster}", node_name="{node_name}", setting_name="server_version_num"}}[3h])',
176-
}
177-
178-
version_data = {}
179-
for metric_name, query in version_queries.items():
180-
result = self.query_instant(query)
181-
if result.get('status') == 'success' and result.get('data', {}).get('result'):
182-
if len(result['data']['result']) > 0:
183-
# Extract setting_value from the metric labels
184-
latest_value = result['data']['result'][0]['metric'].get('setting_value', '')
185-
if latest_value:
186-
version_data[metric_name] = latest_value
187-
else:
188-
print(f"Warning: A002 - No data for {metric_name} (cluster={cluster}, node_name={node_name})")
189-
else:
190-
print(f"Warning: A002 - Query failed for {metric_name}: status={result.get('status')}")
191-
192-
# Format the version data
193-
server_version = version_data.get('server_version', 'Unknown')
194-
version_info = {
195-
"version": server_version,
196-
"server_version_num": version_data.get('server_version_num', 'Unknown'),
197-
}
198-
199-
# Parse major and minor version if we have a valid version string
200-
if server_version and server_version != 'Unknown':
201-
# Handle both formats: "14.5" and "14.5 (Ubuntu 14.5-1.pgdg20.04+1)"
202-
version_parts = server_version.split()[0].split('.')
203-
if len(version_parts) >= 1:
204-
version_info["server_major_ver"] = version_parts[0]
205-
if len(version_parts) >= 2:
206-
version_info["server_minor_ver"] = '.'.join(version_parts[1:])
207-
else:
208-
version_info["server_minor_ver"] = '0'
209-
else:
210-
version_info["server_major_ver"] = 'Unknown'
211-
version_info["server_minor_ver"] = 'Unknown'
212-
else:
213-
version_info["server_major_ver"] = 'Unknown'
214-
version_info["server_minor_ver"] = 'Unknown'
215-
226+
version_info = self._get_postgres_version_info(cluster, node_name)
216227
return self.format_report_data("A002", {"version": version_info}, node_name)
217228

218229
def generate_a003_settings_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[str, Any]:
@@ -263,7 +274,7 @@ def generate_a003_settings_report(self, cluster: str = "local", node_name: str =
263274
print(f"Query result status: {result.get('status')}")
264275
print(f"Query result data: {result.get('data', {})}")
265276

266-
return self.format_report_data("A003", settings_data, node_name)
277+
return self.format_report_data("A003", settings_data, node_name, postgres_version=self._get_postgres_version_info(cluster, node_name))
267278

268279
def generate_a004_cluster_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[str, Any]:
269280
"""
@@ -316,10 +327,15 @@ def generate_a004_cluster_report(self, cluster: str = "local", node_name: str =
316327
size_bytes = float(result['value'][1])
317328
database_sizes[db_name] = size_bytes
318329

319-
return self.format_report_data("A004", {
320-
"general_info": cluster_data,
321-
"database_sizes": database_sizes
322-
}, node_name)
330+
return self.format_report_data(
331+
"A004",
332+
{
333+
"general_info": cluster_data,
334+
"database_sizes": database_sizes,
335+
},
336+
node_name,
337+
postgres_version=self._get_postgres_version_info(cluster, node_name),
338+
)
323339

324340
def generate_a007_altered_settings_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[
325341
str, Any]:
@@ -364,7 +380,7 @@ def generate_a007_altered_settings_report(self, cluster: str = "local", node_nam
364380
print(f"Warning: A007 - No altered settings data returned for cluster={cluster}, node_name={node_name}")
365381
print(f"Query result status: {result.get('status')}")
366382

367-
return self.format_report_data("A007", altered_settings, node_name)
383+
return self.format_report_data("A007", altered_settings, node_name, postgres_version=self._get_postgres_version_info(cluster, node_name))
368384

369385
def generate_h001_invalid_indexes_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[
370386
str, Any]:
@@ -438,7 +454,12 @@ def generate_h001_invalid_indexes_report(self, cluster: str = "local", node_name
438454
"database_size_pretty": self.format_bytes(db_size_bytes)
439455
}
440456

441-
return self.format_report_data("H001", invalid_indexes_by_db, node_name)
457+
return self.format_report_data(
458+
"H001",
459+
invalid_indexes_by_db,
460+
node_name,
461+
postgres_version=self._get_postgres_version_info(cluster, node_name),
462+
)
442463

443464
def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[str, Any]:
444465
"""
@@ -560,7 +581,12 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
560581
}
561582
}
562583

563-
return self.format_report_data("H002", unused_indexes_by_db, node_name)
584+
return self.format_report_data(
585+
"H002",
586+
unused_indexes_by_db,
587+
node_name,
588+
postgres_version=self._get_postgres_version_info(cluster, node_name),
589+
)
564590

565591
def generate_h004_redundant_indexes_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[
566592
str, Any]:
@@ -664,7 +690,12 @@ def generate_h004_redundant_indexes_report(self, cluster: str = "local", node_na
664690
"database_size_pretty": self.format_bytes(db_size_bytes)
665691
}
666692

667-
return self.format_report_data("H004", redundant_indexes_by_db, node_name)
693+
return self.format_report_data(
694+
"H004",
695+
redundant_indexes_by_db,
696+
node_name,
697+
postgres_version=self._get_postgres_version_info(cluster, node_name),
698+
)
668699

669700
def generate_d004_pgstat_settings_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[
670701
str, Any]:
@@ -733,11 +764,16 @@ def generate_d004_pgstat_settings_report(self, cluster: str = "local", node_name
733764
# Check if pg_stat_statements is available and working by querying its metrics
734765
pgss_status = self._check_pg_stat_statements_status(cluster, node_name)
735766

736-
return self.format_report_data("D004", {
737-
"settings": pgstat_data,
738-
"pg_stat_statements_status": pgss_status,
739-
"pg_stat_kcache_status": kcache_status
740-
}, node_name)
767+
return self.format_report_data(
768+
"D004",
769+
{
770+
"settings": pgstat_data,
771+
"pg_stat_statements_status": pgss_status,
772+
"pg_stat_kcache_status": kcache_status,
773+
},
774+
node_name,
775+
postgres_version=self._get_postgres_version_info(cluster, node_name),
776+
)
741777

742778
def _check_pg_stat_kcache_status(self, cluster: str, node_name: str) -> Dict[str, Any]:
743779
"""
@@ -906,7 +942,7 @@ def generate_f001_autovacuum_settings_report(self, cluster: str = "local", node_
906942
"pretty_value": self.format_setting_value(setting_name, setting_value, unit)
907943
}
908944

909-
return self.format_report_data("F001", autovacuum_data, node_name)
945+
return self.format_report_data("F001", autovacuum_data, node_name, postgres_version=self._get_postgres_version_info(cluster, node_name))
910946

911947
def generate_f005_btree_bloat_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[str, Any]:
912948
"""
@@ -995,7 +1031,12 @@ def generate_f005_btree_bloat_report(self, cluster: str = "local", node_name: st
9951031
"database_size_pretty": self.format_bytes(db_size_bytes)
9961032
}
9971033

998-
return self.format_report_data("F005", bloated_indexes_by_db, node_name)
1034+
return self.format_report_data(
1035+
"F005",
1036+
bloated_indexes_by_db,
1037+
node_name,
1038+
postgres_version=self._get_postgres_version_info(cluster, node_name),
1039+
)
9991040

10001041
def generate_g001_memory_settings_report(self, cluster: str = "local", node_name: str = "node-01") -> Dict[
10011042
str, Any]:
@@ -1073,10 +1114,15 @@ def generate_g001_memory_settings_report(self, cluster: str = "local", node_name
10731114
# Calculate some memory usage estimates and recommendations
10741115
memory_analysis = self._analyze_memory_settings(memory_data)
10751116

1076-
return self.format_report_data("G001", {
1077-
"settings": memory_data,
1078-
"analysis": memory_analysis
1079-
}, node_name)
1117+
return self.format_report_data(
1118+
"G001",
1119+
{
1120+
"settings": memory_data,
1121+
"analysis": memory_analysis,
1122+
},
1123+
node_name,
1124+
postgres_version=self._get_postgres_version_info(cluster, node_name),
1125+
)
10801126

10811127
def _analyze_memory_settings(self, memory_data: Dict[str, Any]) -> Dict[str, Any]:
10821128
"""
@@ -1261,7 +1307,12 @@ def generate_f004_heap_bloat_report(self, cluster: str = "local", node_name: str
12611307
"database_size_pretty": self.format_bytes(db_size_bytes)
12621308
}
12631309

1264-
return self.format_report_data("F004", bloated_tables_by_db, node_name)
1310+
return self.format_report_data(
1311+
"F004",
1312+
bloated_tables_by_db,
1313+
node_name,
1314+
postgres_version=self._get_postgres_version_info(cluster, node_name),
1315+
)
12651316

12661317
def generate_k001_query_calls_report(self, cluster: str = "local", node_name: str = "node-01",
12671318
time_range_minutes: int = 60) -> Dict[str, Any]:
@@ -1318,7 +1369,12 @@ def generate_k001_query_calls_report(self, cluster: str = "local", node_name: st
13181369
}
13191370
}
13201371

1321-
return self.format_report_data("K001", queries_by_db, node_name)
1372+
return self.format_report_data(
1373+
"K001",
1374+
queries_by_db,
1375+
node_name,
1376+
postgres_version=self._get_postgres_version_info(cluster, node_name),
1377+
)
13221378

13231379
def generate_k003_top_queries_report(self, cluster: str = "local", node_name: str = "node-01",
13241380
time_range_minutes: int = 60, limit: int = 50) -> Dict[str, Any]:
@@ -1377,7 +1433,12 @@ def generate_k003_top_queries_report(self, cluster: str = "local", node_name: st
13771433
}
13781434
}
13791435

1380-
return self.format_report_data("K003", queries_by_db, node_name)
1436+
return self.format_report_data(
1437+
"K003",
1438+
queries_by_db,
1439+
node_name,
1440+
postgres_version=self._get_postgres_version_info(cluster, node_name),
1441+
)
13811442

13821443
def _get_pgss_metrics_data(self, cluster: str, node_name: str, start_time: datetime, end_time: datetime) -> List[
13831444
Dict[str, Any]]:
@@ -1636,7 +1697,8 @@ def format_bytes(self, bytes_value: float) -> str:
16361697
return f"{value:.2f} {units[unit_index]}"
16371698

16381699
def format_report_data(self, check_id: str, data: Dict[str, Any], host: str = "target-database",
1639-
all_hosts: Dict[str, List[str]] = None) -> Dict[str, Any]:
1700+
all_hosts: Dict[str, List[str]] = None,
1701+
postgres_version: Dict[str, str] = None) -> Dict[str, Any]:
16401702
"""
16411703
Format data to match template structure.
16421704
@@ -1645,6 +1707,7 @@ def format_report_data(self, check_id: str, data: Dict[str, Any], host: str = "t
16451707
data: The data to format (can be a dict with node keys if combining multiple nodes)
16461708
host: Primary host identifier (used if all_hosts not provided)
16471709
all_hosts: Optional dict with 'primary' and 'standbys' keys for multi-node reports
1710+
postgres_version: Optional Postgres version info to include at report level
16481711
16491712
Returns:
16501713
Dictionary formatted for templates
@@ -1663,14 +1726,16 @@ def format_report_data(self, check_id: str, data: Dict[str, Any], host: str = "t
16631726
# Handle both single-node and multi-node data structures
16641727
if isinstance(data, dict) and any(isinstance(v, dict) and 'data' in v for v in data.values()):
16651728
# Multi-node structure: data is already in {node_name: {"data": ...}} format
1729+
# postgres_version should already be embedded per-node; warn if passed here
1730+
if postgres_version:
1731+
print(f"Warning: postgres_version parameter ignored for multi-node data in {check_id}")
16661732
results = data
16671733
else:
16681734
# Single-node structure: wrap data in host key
1669-
results = {
1670-
host: {
1671-
"data": data
1672-
}
1673-
}
1735+
node_result = {"data": data}
1736+
if postgres_version:
1737+
node_result["postgres_version"] = postgres_version
1738+
results = {host: node_result}
16741739

16751740
template_data = {
16761741
"checkId": check_id,

0 commit comments

Comments
 (0)