Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf8ebe79

Browse files
Added index_definition metric and added uptime time of the db as a backup in case stats weren't reset
1 parent967bd50 commitf8ebe79

File tree

3 files changed

+108
-8
lines changed

3 files changed

+108
-8
lines changed

‎config/pgwatch-postgres/metrics.yml‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,8 +14,21 @@ metrics:
1414
gauges:
1515
-'*'
1616

17+
index_definitions:
18+
description:"Index definitions from all databases"
19+
sqls:
20+
11:|-
21+
select /* pgwatch_generated */
22+
indexname,
23+
pg_get_indexdef(indexrelid) as index_definition
24+
from pg_stat_all_indexes
25+
order by schemaname, tablename, indexname;
26+
gauges:
27+
-'*'
28+
1729
presets:
1830
full:
1931
description:"Full metrics for PostgreSQL storage"
2032
metrics:
21-
pgss_queryid_queries:300
33+
pgss_queryid_queries:300
34+
index_definitions:3600

‎reporter/postgres_reports.py‎

Lines changed: 92 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -14,18 +14,25 @@
1414
importargparse
1515
importsys
1616
importos
17+
importpsycopg2
18+
importpsycopg2.extras
1719

1820

1921
classPostgresReportGenerator:
20-
def__init__(self,prometheus_url:str="http://localhost:9090"):
22+
def__init__(self,prometheus_url:str="http://sink-prometheus:9090",
23+
postgres_sink_url:str="postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements"):
2124
"""
2225
Initialize the PostgreSQL report generator.
2326
2427
Args:
25-
prometheus_url: URL of the Prometheus instance
28+
prometheus_url: URL of the Prometheus instance (default: http://sink-prometheus:9090)
29+
postgres_sink_url: Connection string for the Postgres sink database
30+
(default: postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements)
2631
"""
2732
self.prometheus_url=prometheus_url
2833
self.base_url=f"{prometheus_url}/api/v1"
34+
self.postgres_sink_url=postgres_sink_url
35+
self.pg_conn=None
2936

3037
deftest_connection(self)->bool:
3138
"""Test connection to Prometheus."""
@@ -36,6 +43,59 @@ def test_connection(self) -> bool:
3643
print(f"Connection failed:{e}")
3744
returnFalse
3845

46+
defconnect_postgres_sink(self)->bool:
47+
"""Connect to Postgres sink database."""
48+
ifnotself.postgres_sink_url:
49+
returnFalse
50+
51+
try:
52+
self.pg_conn=psycopg2.connect(self.postgres_sink_url)
53+
returnTrue
54+
exceptExceptionase:
55+
print(f"Postgres sink connection failed:{e}")
56+
returnFalse
57+
58+
defclose_postgres_sink(self):
59+
"""Close Postgres sink connection."""
60+
ifself.pg_conn:
61+
self.pg_conn.close()
62+
self.pg_conn=None
63+
64+
defget_index_definitions_from_sink(self)->Dict[str,str]:
65+
"""
66+
Get index definitions from the Postgres sink database.
67+
68+
Returns:
69+
Dictionary mapping index names to their definitions
70+
"""
71+
ifnotself.pg_conn:
72+
ifnotself.connect_postgres_sink():
73+
return {}
74+
75+
index_definitions= {}
76+
77+
try:
78+
withself.pg_conn.cursor(cursor_factory=psycopg2.extras.DictCursor)ascursor:
79+
# Query the index_definitions table for the most recent data
80+
query="""
81+
SELECT DISTINCT ON (data->>'indexname')
82+
data->>'indexname' as indexname,
83+
data->>'index_definition' as index_definition
84+
FROM public.index_definitions
85+
WHERE data ? 'indexname' AND data ? 'index_definition'
86+
ORDER BY data->>'indexname', time DESC
87+
"""
88+
cursor.execute(query)
89+
90+
forrowincursor.fetchall():
91+
ifrow['indexname']:
92+
index_definitions[row['indexname']]=row['index_definition']
93+
94+
exceptExceptionase:
95+
print(f"Error fetching index definitions from Postgres sink:{e}")
96+
97+
returnindex_definitions
98+
3999
defquery_instant(self,query:str)->Dict[str,Any]:
40100
"""
41101
Execute an instant PromQL query.
@@ -315,6 +375,21 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
315375
# Get all databases
316376
databases=self.get_all_databases(cluster,node_name)
317377

378+
# Query postmaster uptime to get startup time
379+
postmaster_uptime_query=f'last_over_time(pgwatch_db_stats_postmaster_uptime_s{{cluster="{cluster}", node_name="{node_name}"}}[10h])'
380+
postmaster_uptime_result=self.query_instant(postmaster_uptime_query)
381+
382+
postmaster_startup_time=None
383+
postmaster_startup_epoch=None
384+
ifpostmaster_uptime_result.get('status')=='success'andpostmaster_uptime_result.get('data', {}).get('result'):
385+
uptime_seconds=float(postmaster_uptime_result['data']['result'][0]['value'][1])ifpostmaster_uptime_result['data']['result']elseNone
386+
ifuptime_seconds:
387+
postmaster_startup_epoch=datetime.now().timestamp()-uptime_seconds
388+
postmaster_startup_time=datetime.fromtimestamp(postmaster_startup_epoch).isoformat()
389+
390+
# Get index definitions from Postgres sink database
391+
index_definitions=self.get_index_definitions_from_sink()
392+
318393
unused_indexes_by_db= {}
319394
fordb_nameindatabases:
320395
# Query stats_reset timestamp for this database
@@ -353,10 +428,14 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
353428
{}).get(
354429
'result')else0
355430

431+
# Get index definition from collected metrics
432+
index_definition=index_definitions.get(index_name,'Definition not available')
433+
356434
index_data= {
357435
"schema_name":schema_name,
358436
"table_name":table_name,
359437
"index_name":index_name,
438+
"index_definition":index_definition,
360439
"reason":reason,
361440
"idx_scan":idx_scan,
362441
"index_size_bytes":index_size_bytes,
@@ -381,7 +460,9 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
381460
"stats_reset": {
382461
"stats_reset_epoch":stats_reset_epoch,
383462
"stats_reset_time":stats_reset_time,
384-
"days_since_reset":days_since_reset
463+
"days_since_reset":days_since_reset,
464+
"postmaster_startup_epoch":postmaster_startup_epoch,
465+
"postmaster_startup_time":postmaster_startup_time
385466
}
386467
}
387468

@@ -1764,8 +1845,10 @@ def make_request(api_url, endpoint, request_data):
17641845

17651846
defmain():
17661847
parser=argparse.ArgumentParser(description='Generate PostgreSQL reports using PromQL')
1767-
parser.add_argument('--prometheus-url',default='http://localhost:9090',
1768-
help='Prometheus URL (default: http://localhost:9090)')
1848+
parser.add_argument('--prometheus-url',default='http://sink-prometheus:9090',
1849+
help='Prometheus URL (default: http://sink-prometheus:9090 for Docker, use http://localhost:59090 for external access)')
1850+
parser.add_argument('--postgres-sink-url',default='postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements',
1851+
help='Postgres sink connection string (default: postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements for Docker, use postgresql://pgwatch:pgwatchadmin@localhost:55433/measurements for external access)')
17691852
parser.add_argument('--cluster',default='local',
17701853
help='Cluster name (default: local)')
17711854
parser.add_argument('--node-name',default='node-01',
@@ -1785,7 +1868,7 @@ def main():
17851868

17861869
args=parser.parse_args()
17871870

1788-
generator=PostgresReportGenerator(args.prometheus_url)
1871+
generator=PostgresReportGenerator(args.prometheus_url,args.postgres_sink_url)
17891872

17901873
# Test connection
17911874
ifnotgenerator.test_connection():
@@ -1852,6 +1935,9 @@ def main():
18521935
print(f"Error generating reports:{e}")
18531936
raisee
18541937
sys.exit(1)
1938+
finally:
1939+
# Clean up postgres connection
1940+
generator.close_postgres_sink()
18551941

18561942

18571943
if__name__=="__main__":

‎reporter/requirements.txt‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +1,2 @@
1-
requests>=2.31.0
1+
requests>=2.31.0
2+
psycopg2-binary>=2.9.9

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp