From: Tom Hughes <tom@compton.nu>
Date: Thu, 2 Nov 2023 18:19:39 +0000 (+0000)
Subject: Add support for using sql_exporter to query postgres
X-Git-Url: https://git.openstreetmap.org./chef.git/commitdiff_plain/e909b020604a52f20f78d0980bbfc93252f384e0?hp=62e412af16d86626de5ce704b85fb64c99f4c487

Add support for using sql_exporter to query postgres
---

diff --git a/cookbooks/postgresql/attributes/default.rb b/cookbooks/postgresql/attributes/default.rb
index 19d871ba1..aa3e4b8ac 100644
--- a/cookbooks/postgresql/attributes/default.rb
+++ b/cookbooks/postgresql/attributes/default.rb
@@ -1,6 +1,7 @@
 default[:postgresql][:versions] = []
 default[:postgresql][:clusters] = {}
 default[:postgresql][:monitor_database] = "postgres"
+default[:postgresql][:monitor_queries] = false
 default[:postgresql][:settings][:defaults][:port] = "5432"
 default[:postgresql][:settings][:defaults][:max_connections] = "100"
 default[:postgresql][:settings][:defaults][:ssl] = "true"
diff --git a/cookbooks/postgresql/recipes/default.rb b/cookbooks/postgresql/recipes/default.rb
index 1fe903022..086047258 100644
--- a/cookbooks/postgresql/recipes/default.rb
+++ b/cookbooks/postgresql/recipes/default.rb
@@ -144,6 +144,36 @@ clusters.each do |name, details|
     subscribes :restart, "template[/etc/prometheus/exporters/postgres_queries.yml]"
   end
 
+  if node[:postgresql][:monitor_queries]
+    template "/etc/prometheus/exporters/sql_exporter.yml" do
+      source "sql_exporter.yml.erb"
+      owner "root"
+      group "root"
+      mode "644"
+    end
+
+    prometheus_exporter "sql" do
+      port 20000 + details[:port].to_i
+      service "sql-#{prometheus_suffix}"
+      labels "cluster" => name
+      scrape_interval "1m"
+      scrape_timeout "1m"
+      options "--config.file=/etc/prometheus/exporters/sql_exporter.yml"
+      environment "SQLEXPORTER_TARGET_DSN" => "postgres://prometheus:#{passwords['prometheus']}@/run/postgresql:#{details[:port]}/#{prometheus_database}"
+      restrict_address_families "AF_UNIX"
+      subscribes :restart, "template[/etc/prometheus/exporters/sql_exporter.yml]"
+    end
+  else
+    prometheus_exporter "sql" do
+      action :delete
+      service "sql-#{prometheus_suffix}"
+    end
+
+    file "/etc/prometheus/exporters/sql_exporter.yml" do
+      action :delete
+    end
+  end
+
   munin_suffix = name.tr("/", ":")
 
   munin_plugin "postgres_bgwriter_#{munin_suffix}" do
diff --git a/cookbooks/postgresql/templates/default/sql_exporter.yml.erb b/cookbooks/postgresql/templates/default/sql_exporter.yml.erb
new file mode 100644
index 000000000..94e94776f
--- /dev/null
+++ b/cookbooks/postgresql/templates/default/sql_exporter.yml.erb
@@ -0,0 +1,32 @@
+# Global defaults.
+global:
+  # If scrape_timeout <= 0, no timeout is set unless Prometheus provides one. The default is 10s.
+  scrape_timeout: 10s
+  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from timing out first.
+  scrape_timeout_offset: 500ms
+  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
+  min_interval: 0s
+  # Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,
+  # as will concurrent scrapes.
+  max_connections: 3
+  # Maximum number of idle connections to any one target. Unless you use very long collection intervals, this should
+  # always be the same as max_connections.
+  max_idle_connections: 3
+  # Maximum number of maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse.
+  # If 0, connections are not closed due to a connection's age.
+  max_connection_lifetime: 5m
+
+# The target to monitor and the collectors to execute on it.
+target:
+  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
+  # the schema gets dropped or replaced to match the driver expected DSN format.
+  data_source_name: postgres
+
+  # Collectors (referenced by name) to execute on the target.
+  # Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
+  collectors: [sql_*]
+
+# Collector files specifies a list of globs. One collector definition is read from each matching file.
+# Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
+collector_files:
+  - "sql_*.collector.yml"