From 926b50ddad5d1701e0b22690588e632585d60ad8 Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Sun, 24 Sep 2023 10:03:06 +0100
Subject: [PATCH] Replace custom query with process_idle collector

---
 cookbooks/postgresql/recipes/default.rb       |  9 +--
 .../default/postgres_queries.yml.erb          | 58 -------------------
 2 files changed, 3 insertions(+), 64 deletions(-)
 delete mode 100644 cookbooks/postgresql/templates/default/postgres_queries.yml.erb

diff --git a/cookbooks/postgresql/recipes/default.rb b/cookbooks/postgresql/recipes/default.rb
index 3bf0ad154..7b5269b07 100644
--- a/cookbooks/postgresql/recipes/default.rb
+++ b/cookbooks/postgresql/recipes/default.rb
@@ -108,11 +108,8 @@ ohai_plugin "postgresql" do
   template "ohai.rb.erb"
 end
 
-template "/etc/prometheus/exporters/postgres_queries.yml" do
-  source "postgres_queries.yml.erb"
-  owner "root"
-  group "root"
-  mode "644"
+file "/etc/prometheus/exporters/postgres_queries.yml" do
+  action :delete
 end
 
 package "pgtop"
@@ -131,7 +128,7 @@ clusters.each do |name, details|
     scrape_interval "1m"
     scrape_timeout "1m"
     user "postgres"
-    options "--no-collector.process_idle --extend.query-path=/etc/prometheus/exporters/postgres_queries.yml"
+    options "--collector.process_idle"
     environment "DATA_SOURCE_NAME" => "postgres:///#{prometheus_database}?host=/run/postgresql&port=#{details[:port]}"
     restrict_address_families "AF_UNIX"
     remove_ipc false
diff --git a/cookbooks/postgresql/templates/default/postgres_queries.yml.erb b/cookbooks/postgresql/templates/default/postgres_queries.yml.erb
deleted file mode 100644
index a92382edc..000000000
--- a/cookbooks/postgresql/templates/default/postgres_queries.yml.erb
+++ /dev/null
@@ -1,58 +0,0 @@
-pg_process_idle:
-  query: |
-    WITH
-      metrics AS (
-        SELECT
-          state,
-          application_name,
-          SUM(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))::bigint)::float AS process_idle_seconds_sum,
-          COUNT(*) AS process_idle_seconds_count
-        FROM pg_stat_activity
-        WHERE state ~ '^idle'
-        GROUP BY state, application_name
-      ),
-      buckets AS (
-        SELECT
-          state,
-          application_name,
-          le,
-          SUM(
-            CASE WHEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) <= le
-              THEN 1
-              ELSE 0
-            END
-          )::bigint AS bucket
-        FROM
-          pg_stat_activity,
-          UNNEST(ARRAY[1, 2, 5, 15, 30, 60, 90, 120, 300]) AS le
-        GROUP BY state, application_name, le
-        ORDER BY state, application_name, le
-      )
-    SELECT
-      state,
-      application_name,
-      process_idle_seconds_sum as seconds_sum,
-      process_idle_seconds_count as seconds_count,
-      ARRAY_AGG(le) AS seconds,
-      ARRAY_AGG(bucket) AS seconds_bucket
-    FROM metrics JOIN buckets USING (state, application_name)
-    GROUP BY 1, 2, 3, 4
-  master: true
-  metrics:
-    - state:
-        usage: "LABEL"
-        description: "State"
-    - application_name:
-        usage: "LABEL"
-        description: "Application Name"
-    - seconds:
-        usage: "HISTOGRAM"
-        description: "Idle time of server processes"
-
-pg_wal:
-  query: "SELECT count(*) AS segment_count FROM pg_ls_waldir() WHERE name ~ '^[0-9A-Z]{24}$'"
-  master: true
-  metrics:
-    - segment_count:
-        usage: "GAUGE"
-        description: "Number of WAL segments"
-- 
2.39.5