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

Commitd7d15e2

Browse files
Added a postgres_ai containter to hold the config
1 parentd7633c2 commitd7d15e2

File tree

2 files changed

+142
-17
lines changed

2 files changed

+142
-17
lines changed

‎Dockerfile.postgres_ai‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
FROM alpine:latest
2+
3+
# Create a directory to hold the configs
4+
RUN mkdir -p /config
5+
6+
# Copy all config contents except sources.yml files
7+
COPY config/ /config/
8+
9+
# Remove the sources.yml files that should be excluded
10+
RUN rm -f /config/pgwatch-postgres/sources.yml /config/pgwatch-prometheus/sources.yml
11+
12+
# Create a volume for the configs
13+
VOLUME /config
14+
15+
# Keep the container running
16+
CMD ["tail", "-f", "/dev/null"]

‎docker-compose.yml‎

Lines changed: 126 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,12 @@ services:
44
image:alpine:3.22.0
55
container_name:sources-generator
66
working_dir:/app
7+
depends_on:
8+
-postgres-ai
79
volumes:
8-
-./instances.yml:/app/instances.yaml
9-
-./config:/app/config
10+
-./instances.yml:/app/instances.yaml:rw
11+
-postgres_ai_configs:/app/config:rw
12+
-grafana_data:/var/lib/grafana:rw
1013
command:>
1114
sh -c "
1215
mkdir -p /app/config/pgwatch-postgres /app/config/pgwatch-prometheus &&
@@ -15,7 +18,20 @@ services:
1518
echo '# PGWatch Sources Configuration - Prometheus Instance' > /app/config/pgwatch-prometheus/sources.yml &&
1619
echo '' >> /app/config/pgwatch-prometheus/sources.yml &&
1720
sed 's/~sink_type~/prometheus/g' /app/instances.yaml >> /app/config/pgwatch-prometheus/sources.yml &&
18-
echo 'Generated sources.yml files for both postgres and prometheus'
21+
echo 'Generated sources.yml files for both postgres and prometheus' &&
22+
ls -la /app/config/pgwatch-postgres/ &&
23+
ls -la /app/config/pgwatch-prometheus/ &&
24+
echo 'Copying files to shared volume location...' &&
25+
mkdir -p /app/config/pgwatch &&
26+
cp /app/config/pgwatch-postgres/sources.yml /app/config/pgwatch/ &&
27+
cp /app/config/pgwatch-postgres/metrics.yml /app/config/pgwatch/ &&
28+
echo 'Files copied to shared volume:' &&
29+
ls -la /app/config/pgwatch/ &&
30+
echo 'Copying Grafana dashboards...' &&
31+
cp /app/config/grafana/dashboards/*.json /var/lib/grafana/dashboards/ &&
32+
echo 'Grafana dashboards copied to shared volume:' &&
33+
ls -la /var/lib/grafana/dashboards/ &&
34+
tail -f /dev/null
1935
"
2036
2137
# Target Database - The PostgreSQL database being monitored
@@ -26,12 +42,65 @@ services:
2642
POSTGRES_DB:target_database
2743
POSTGRES_USER:postgres
2844
POSTGRES_PASSWORD:postgres
29-
command:["postgres", "-c", "shared_preload_libraries=pg_stat_statements", "-c", "pg_stat_statements.track=all"]
3045
ports:
3146
-"55432:5432"
47+
depends_on:
48+
-postgres-ai
3249
volumes:
3350
-target_db_data:/var/lib/postgresql/data
34-
-./config/target-db/init.sql:/docker-entrypoint-initdb.d/init.sql
51+
command:>
52+
sh -c "
53+
echo '-- Initialize target database for monitoring' > /docker-entrypoint-initdb.d/init.sql &&
54+
echo '-- Enable pg_stat_statements extension for query monitoring' >> /docker-entrypoint-initdb.d/init.sql &&
55+
echo 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;' >> /docker-entrypoint-initdb.d/init.sql &&
56+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
57+
echo '-- Create a sample table for demonstration' >> /docker-entrypoint-initdb.d/init.sql &&
58+
echo 'CREATE TABLE IF NOT EXISTS sample_data (' >> /docker-entrypoint-initdb.d/init.sql &&
59+
echo ' id SERIAL PRIMARY KEY,' >> /docker-entrypoint-initdb.d/init.sql &&
60+
echo ' name VARCHAR(100),' >> /docker-entrypoint-initdb.d/init.sql &&
61+
echo ' created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP' >> /docker-entrypoint-initdb.d/init.sql &&
62+
echo ');' >> /docker-entrypoint-initdb.d/init.sql &&
63+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
64+
echo '-- Insert some sample data' >> /docker-entrypoint-initdb.d/init.sql &&
65+
echo 'INSERT INTO sample_data (name) VALUES' >> /docker-entrypoint-initdb.d/init.sql &&
66+
echo ' (''Sample Record 1''),' >> /docker-entrypoint-initdb.d/init.sql &&
67+
echo ' (''Sample Record 2''),' >> /docker-entrypoint-initdb.d/init.sql &&
68+
echo ' (''Sample Record 3'');' >> /docker-entrypoint-initdb.d/init.sql &&
69+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
70+
echo '-- Create a user for PGWatch monitoring' >> /docker-entrypoint-initdb.d/init.sql &&
71+
echo 'CREATE USER monitor WITH PASSWORD ''monitor_pass'';' >> /docker-entrypoint-initdb.d/init.sql &&
72+
echo 'GRANT CONNECT ON DATABASE target_database TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
73+
echo 'GRANT USAGE ON SCHEMA public TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
74+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
75+
echo '-- Create a public view for pg_statistic access' >> /docker-entrypoint-initdb.d/init.sql &&
76+
echo 'CREATE OR REPLACE VIEW public.pg_statistic AS' >> /docker-entrypoint-initdb.d/init.sql &&
77+
echo 'SELECT' >> /docker-entrypoint-initdb.d/init.sql &&
78+
echo ' n.nspname as schemaname,' >> /docker-entrypoint-initdb.d/init.sql &&
79+
echo ' c.relname as tablename,' >> /docker-entrypoint-initdb.d/init.sql &&
80+
echo ' a.attname,' >> /docker-entrypoint-initdb.d/init.sql &&
81+
echo ' s.stanullfrac as null_frac,' >> /docker-entrypoint-initdb.d/init.sql &&
82+
echo ' s.stawidth as avg_width,' >> /docker-entrypoint-initdb.d/init.sql &&
83+
echo ' false as inherited' >> /docker-entrypoint-initdb.d/init.sql &&
84+
echo 'FROM pg_statistic s' >> /docker-entrypoint-initdb.d/init.sql &&
85+
echo 'JOIN pg_class c ON c.oid = s.starelid' >> /docker-entrypoint-initdb.d/init.sql &&
86+
echo 'JOIN pg_namespace n ON n.oid = c.relnamespace' >> /docker-entrypoint-initdb.d/init.sql &&
87+
echo 'JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum' >> /docker-entrypoint-initdb.d/init.sql &&
88+
echo 'WHERE a.attnum > 0 AND NOT a.attisdropped;' >> /docker-entrypoint-initdb.d/init.sql &&
89+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
90+
echo '-- Grant specific access instead of all tables' >> /docker-entrypoint-initdb.d/init.sql &&
91+
echo 'GRANT SELECT ON public.pg_statistic TO pg_monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
92+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
93+
echo '-- Grant access to monitoring views' >> /docker-entrypoint-initdb.d/init.sql &&
94+
echo 'GRANT SELECT ON pg_stat_statements TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
95+
echo 'GRANT SELECT ON pg_stat_database TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
96+
echo 'GRANT SELECT ON pg_stat_user_tables TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
97+
echo '-- Grant pg_monitor role to monitor user for enhanced monitoring capabilities' >> /docker-entrypoint-initdb.d/init.sql &&
98+
echo 'GRANT pg_monitor TO monitor;' >> /docker-entrypoint-initdb.d/init.sql &&
99+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
100+
echo '-- Set search path for the monitor user' >> /docker-entrypoint-initdb.d/init.sql &&
101+
echo 'ALTER USER monitor SET search_path = \"$$user\", public, pg_catalog;' >> /docker-entrypoint-initdb.d/init.sql &&
102+
exec postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all
103+
"
35104
36105
# Postgres Sink - Storage for metrics in PostgreSQL format
37106
sink-postgres:
@@ -41,23 +110,52 @@ services:
41110
POSTGRES_DB:postgres
42111
POSTGRES_USER:postgres
43112
POSTGRES_PASSWORD:postgres
113+
depends_on:
114+
-postgres-ai
44115
ports:
45116
-"55433:5432"
46117
volumes:
47118
-sink_postgres_data:/var/lib/postgresql/data
48-
-./config/sink-postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
119+
command:>
120+
sh -c "
121+
echo '-- Initialize PostgreSQL sink database for storing pgwatch measurements' > /docker-entrypoint-initdb.d/init.sql &&
122+
echo '-- This database will store all the monitoring metrics collected by PGWatch' >> /docker-entrypoint-initdb.d/init.sql &&
123+
echo '-- Based on https://pgwat.ch/latest/howto/metrics_db_bootstrap.html' >> /docker-entrypoint-initdb.d/init.sql &&
124+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
125+
echo '-- Create the pgwatch role for measurements database' >> /docker-entrypoint-initdb.d/init.sql &&
126+
echo 'CREATE ROLE pgwatch WITH LOGIN PASSWORD ''pgwatchadmin'';' >> /docker-entrypoint-initdb.d/init.sql &&
127+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
128+
echo '-- Create the measurements database owned by pgwatch' >> /docker-entrypoint-initdb.d/init.sql &&
129+
echo 'CREATE DATABASE measurements OWNER pgwatch;' >> /docker-entrypoint-initdb.d/init.sql &&
130+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
131+
echo '-- Switch to the measurements database context' >> /docker-entrypoint-initdb.d/init.sql &&
132+
echo '\\c measurements;' >> /docker-entrypoint-initdb.d/init.sql &&
133+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
134+
echo '-- Create extensions that might be useful for metrics storage' >> /docker-entrypoint-initdb.d/init.sql &&
135+
echo 'CREATE EXTENSION IF NOT EXISTS btree_gist;' >> /docker-entrypoint-initdb.d/init.sql &&
136+
echo 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;' >> /docker-entrypoint-initdb.d/init.sql &&
137+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
138+
echo '-- Grant necessary permissions to pgwatch user' >> /docker-entrypoint-initdb.d/init.sql &&
139+
echo 'GRANT ALL PRIVILEGES ON DATABASE measurements TO pgwatch;' >> /docker-entrypoint-initdb.d/init.sql &&
140+
echo 'GRANT ALL PRIVILEGES ON SCHEMA public TO pgwatch;' >> /docker-entrypoint-initdb.d/init.sql &&
141+
echo '' >> /docker-entrypoint-initdb.d/init.sql &&
142+
echo '-- pgwatch will automatically create the admin and subpartitions schemas' >> /docker-entrypoint-initdb.d/init.sql &&
143+
echo '-- and all necessary tables when it starts up' >> /docker-entrypoint-initdb.d/init.sql &&
144+
"
49145
50146
# Prometheus Sink - Storage for metrics in Prometheus format
51147
sink-prometheus:
52148
image:prom/prometheus:v3.4.2
53149
container_name:sink-prometheus
150+
depends_on:
151+
-postgres-ai
54152
ports:
55153
-"59090:9090"
56154
volumes:
57-
-./config/prometheus/prometheus.yml:/etc/prometheus/prometheus.yml
155+
-postgres_ai_configs:/config:ro
58156
-prometheus_data:/prometheus
59157
command:
60-
-'--config.file=/etc/prometheus/prometheus.yml'
158+
-'--config.file=/config/prometheus/prometheus.yml'
61159
-'--storage.tsdb.path=/prometheus'
62160
-'--web.console.libraries=/etc/prometheus/console_libraries'
63161
-'--web.console.templates=/etc/prometheus/consoles'
@@ -68,30 +166,30 @@ services:
68166
pgwatch-postgres:
69167
image:cybertecpostgresql/pgwatch:3
70168
container_name:pgwatch-postgres
71-
command:["--sources=/etc/pgwatch/sources.yml", "--metrics=/etc/pgwatch/metrics.yml", "--sink=postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements", "--web-addr=:8080"]
169+
command:["--sources=/config/pgwatch-postgres/sources.yml", "--metrics=/config/pgwatch-postgres/metrics.yml", "--sink=postgresql://pgwatch:pgwatchadmin@sink-postgres:5432/measurements", "--web-addr=:8080"]
72170
ports:
73171
-"58080:8080"
74172
depends_on:
173+
-postgres-ai
75174
-sources-generator
76175
-sink-postgres
77176
volumes:
78-
-./config/pgwatch-postgres/sources.yml:/etc/pgwatch/sources.yml
79-
-./config/pgwatch-postgres/metrics.yml:/etc/pgwatch/metrics.yml
177+
-postgres_ai_configs:/config:rw
80178

81179
# PGWatch Instance 2 - Monitoring service (Prometheus sink)
82180
pgwatch-prometheus:
83181
image:cybertecpostgresql/pgwatch:3
84182
container_name:pgwatch-prometheus
85-
command:["--sources=/etc/pgwatch/sources.yml", "--metrics=/etc/pgwatch/metrics.yml", "--sink=prometheus://0.0.0.0:9091/pgwatch", "--web-addr=:8089"]
183+
command:["--sources=/config/pgwatch-prometheus/sources.yml", "--metrics=/config/pgwatch-prometheus/metrics.yml", "--sink=prometheus://0.0.0.0:9091/pgwatch", "--web-addr=:8089"]
86184
ports:
87185
-"58089:8089"
88186
-"59091:9091"
89187
depends_on:
188+
-postgres-ai
90189
-sources-generator
91190
-sink-prometheus
92191
volumes:
93-
-./config/pgwatch-prometheus/sources.yml:/etc/pgwatch/sources.yml
94-
-./config/pgwatch-prometheus/metrics.yml:/etc/pgwatch/metrics.yml
192+
-postgres_ai_configs:/config:rw
95193

96194
# Grafana with datasources - Visualization layer
97195
grafana:
@@ -101,16 +199,19 @@ services:
101199
GF_SECURITY_ADMIN_USER:demo
102200
GF_SECURITY_ADMIN_PASSWORD:demo
103201
GF_INSTALL_PLUGINS:yesoreyeram-infinity-datasource
202+
GF_PATHS_PROVISIONING:/config/grafana/provisioning
203+
GF_PATHS_CONFIG:/config/grafana/provisioning/grafana.ini
204+
GF_PATHS_DATA:/var/lib/grafana
104205
ports:
105206
-"3000:3000"
106207
volumes:
107208
-grafana_data:/var/lib/grafana
108-
-./config/grafana/provisioning:/etc/grafana/provisioning
109-
-./config/grafana/dashboards:/var/lib/grafana/dashboards
110-
-./config/grafana/provisioning/grafana.ini:/etc/grafana/grafana.ini
209+
-postgres_ai_configs:/config:rw
111210
depends_on:
211+
-postgres-ai
112212
-sink-postgres
113213
-sink-prometheus
214+
114215
flask-backend:
115216
build:
116217
context:./flask-backend
@@ -164,9 +265,17 @@ services:
164265
sleep 86400
165266
done
166267
"
268+
postgres-ai:
269+
build:
270+
context:.
271+
dockerfile:Dockerfile.postgres_ai
272+
container_name:postgres-ai
273+
volumes:
274+
-postgres_ai_configs:/config:/config
167275

168276
volumes:
169277
target_db_data:
170278
sink_postgres_data:
171279
prometheus_data:
172280
grafana_data:
281+
postgres_ai_configs:

0 commit comments

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