feat(tente): monitor database sizes

5 files changed, 99 insertions, 0 deletions

diff --git a/nixos/hosts/tente/default.nix b/nixos/hosts/tente/default.nix
index 64eba57..72cd466 100644
--- a/nixos/hosts/tente/default.nix
+++ b/nixos/hosts/tente/default.nix
@@ -80,6 +80,7 @@ in
   monitoring.alloyUiPort = 3001;
   monitoring.lokiPort = 3030;
   monitoring.prometheusNodeExporterPort = 9002;
+  monitoring.prometheusSqlExporterPort = 9003;
   headscale.port = 8080;
   matrix.port = 8008;
 
diff --git a/nixos/hosts/tente/monitoring.nix b/nixos/hosts/tente/monitoring.nix
index 41c59f3..b9c806e 100644
--- a/nixos/hosts/tente/monitoring.nix
+++ b/nixos/hosts/tente/monitoring.nix
@@ -17,8 +17,15 @@ in
     prometheusNodeExporterPort = lib.mkOption {
       type = lib.types.int;
     };
+    prometheusSqlExporterPort = lib.mkOption {
+      type = lib.types.int;
+    };
   };
 
+  imports = [
+    <top/shared/prometheus-sql-exporter/service.nix>
+  ];
+
   config = {
     services.grafana = {
       enable = true;
@@ -57,6 +64,12 @@ in
             targets = [ "localhost:${toString cfg.prometheusNodeExporterPort}" ];
           }];
         }
+        {
+          job_name = "sql";
+          static_configs = [{
+            targets = [ "localhost:${toString cfg.prometheusSqlExporterPort}" ];
+          }];
+        }
       ];
 
       exporters.node = {
@@ -66,6 +79,34 @@ in
       };
     };
 
+    services.prometheus-sql-exporter = {
+      enable = true;
+      port = cfg.prometheusSqlExporterPort;
+      config = {
+        target = {
+          # This URL should be postgresql:///postgres?host=/run/postgresql
+          # but sql_exporter uses xo/dburl which isn't spec-compliant: https://github.com/xo/dburl/issues/46
+          data_source_name = "postgresql:/run/postgresql:/postgres";
+          collectors = ["db-sizes"];
+        };
+        collectors = [
+          {
+            collector_name = "db-sizes";
+            metrics = [
+              {
+                metric_name = "pg_db_size_bytes";
+                help = "disk space used by the database";
+                type = "gauge";
+                key_labels = ["database_name"];
+                values = ["size"];
+                query = "SELECT datname AS database_name, pg_database_size(datname) as size from pg_database";
+              }
+            ];
+          }
+        ];
+      };
+    };
+
     services.loki = {
       enable = true;
       configuration = {
diff --git a/nixos/shared/postgresql.nix b/nixos/shared/postgresql.nix
index 6d9bb40..274895b 100644
--- a/nixos/shared/postgresql.nix
+++ b/nixos/shared/postgresql.nix
@@ -7,6 +7,7 @@
       authentication = pkgs.lib.mkOverride 10 ''
         #type database  DBuser  auth-method
         local sameuser  all     peer
+        local all       prometheus-sql-exporter peer
       '';
     };
   };
diff --git a/nixos/shared/prometheus-sql-exporter/default.nix b/nixos/shared/prometheus-sql-exporter/default.nix
new file mode 100644
index 0000000..81f1660
--- /dev/null
+++ b/nixos/shared/prometheus-sql-exporter/default.nix
@@ -0,0 +1,26 @@
+{
+  lib,
+  buildGoModule,
+  fetchFromGitHub,
+}:
+
+buildGoModule rec {
+  pname = "sql_exporter";
+  version = "0.17.1";
+
+  src = fetchFromGitHub {
+    owner = "burningalchemist";
+    repo = pname;
+    rev = version;
+    sha256 = "sha256-AEPFXPplHtny1P3gMvB1gbMj10bpu9PXc6ywliF+dCc=";
+  };
+
+  vendorHash = "sha256-KFWDqbdbXvgEtz1nlasWrvIckpzasUdzbb+AKfXmYf8=";
+
+  meta = with lib; {
+    description = "Database-agnostic SQL exporter for Prometheus";
+    mainProgram = "sql_exporter";
+    homepage = "https://github.com/burningalchemist/sql_exporter";
+    license = licenses.mit;
+  };
+}
diff --git a/nixos/shared/prometheus-sql-exporter/service.nix b/nixos/shared/prometheus-sql-exporter/service.nix
new file mode 100644
index 0000000..a887f91
--- /dev/null
+++ b/nixos/shared/prometheus-sql-exporter/service.nix
@@ -0,0 +1,30 @@
+# TODO: submit burningalchemist's sql_exporter to nixpkgs
+{ config, lib, pkgs, ... }:
+
+let
+  sqlExporter = pkgs.callPackage ./default.nix {};
+  cfg = config.services.prometheus-sql-exporter;
+  configFile =  builtins.toFile "config.yaml" (builtins.toJSON cfg.config);
+in
+{
+  options.services.prometheus-sql-exporter = {
+    enable = lib.mkEnableOption "sql-exporter";
+    port = lib.mkOption {
+      type = lib.types.int;
+    };
+    config = lib.mkOption {
+      type = (pkgs.formats.json {}).type;
+    };
+  };
+
+  config = lib.mkIf cfg.enable {
+    systemd.services.prometheus-sql-exporter = {
+      serviceConfig = {
+        ExecStart = "${sqlExporter}/bin/sql_exporter -config.file ${configFile} -web.listen-address :${toString cfg.port}";
+        DynamicUser = "true";
+        User = "prometheus-sql-exporter";
+      };
+      wantedBy = ["multi-user.target"];
+    };
+  };
+}