Repair stalled Telegraf partitions from soup instead of every highstate

Truncating default partitions is destructive and premaking them is pg_cron's job, so neither belongs in a state that runs on every checkin. Replace the two telegraf_users states with so-telegraf-partition-repair, a standalone tool that reports partition health and clears the backlog, and call it once from soup. The script depends only on pg_partman, so it also runs against a grid that has not yet picked up the new postgres state. It no-ops when nothing is stranded, refuses to discard rows non-interactively without --yes, and reports when the pg_cron job has never fired, which is the underlying cause rather than a symptom the truncate addresses. Hourly self-healing stays with so_admin.telegraf_maintenance() via pg_cron, so a grid that never soups still recovers, just gradually and without discarding in-retention metrics.
Fix Telegraf metrics falling into pg_partman default partitions
2026-08-07 08:24:45 +02:00 · 2026-08-06 12:28:40 -04:00 · 2026-08-05 17:39:39 -04:00
6 changed files with 360 additions and 10 deletions
@@ -1013,9 +1013,24 @@ up_to_3.3.0() {
  INSTALLEDVERSION=3.3.0
 }

+telegraf_partition_repair() {
+  # Grids upgraded onto an existing /nsm/postgres never ran init-db.sh, so
+  # so_telegraf did not exist when PostgreSQL started and pg_cron's launcher
+  # died without retrying. partman maintenance never ran and every metric piled
+  # up in <parent>_default, which then blocks partition creation outright.
+  # Fresh installs are unaffected. The script no-ops when nothing is stranded.
+  [[ -x /usr/sbin/so-telegraf-partition-repair ]] || return 0
+  docker ps --format '{{.Names}}' | grep -qx so-postgres || return 0
+  echo "Checking Telegraf metric partitions."
+  /usr/sbin/so-telegraf-partition-repair --yes \
+    || echo "  warning: so-telegraf-partition-repair failed; run it manually" >&2
+}
+
 post_to_3.3.0() {
  # Recollate again since some internal DBs were excluded during 3.2.0 soup
  recollate_postgres
+
+  telegraf_partition_repair
 }
 ### 3.3.0 End ###

@@ -16,4 +16,4 @@ postgres:
    logging_collector: 'off'
    log_min_messages: 'warning'
    shared_preload_libraries: pg_cron
-    cron.database_name: so_telegraf
+    cron.database_name: postgres
@@ -83,7 +83,7 @@ postgres:
      advanced: True
      helpLink: postgres
    cron.database_name:
-      description: Database pg_cron schedules jobs in. Must be so_telegraf so partman maintenance runs in the right database context.
+      description: Database pg_cron keeps its job metadata in. Must already exist when PostgreSQL starts, because pg_cron's launcher connects to it at startup and never retries if it is missing. The maintenance job itself targets so_telegraf.
      global: True
      advanced: True
      helpLink: postgres
@@ -0,0 +1,153 @@
+#!/bin/bash
+
+# Copyright Security Onion Solutions LLC and/or licensed to Security Onion Solutions LLC under one
+# or more contributor license agreements. Licensed under the Elastic License 2.0 as shown at
+# https://securityonion.net/license; you may not use this file except in compliance with the
+# Elastic License 2.0.
+
+# Repair Telegraf metric partitions on a grid where pg_partman maintenance
+# stalled and metrics piled up in <parent>_default.
+#
+# Once a default partition holds rows for a day with no child partition,
+# Postgres cannot create that child at all -- attaching it would violate the
+# default's constraint -- so maintenance stays broken until the default is
+# emptied. This discards the stranded rows rather than repartitioning them:
+# most are already past retention, and moving tens of GB just to drop them is
+# not worth the WAL.
+#
+# Self-contained on purpose: it depends only on pg_partman, so it runs against
+# a grid that has not yet picked up the current postgres state.
+#
+# Usage: so-telegraf-partition-repair [--dry-run] [--yes]
+#   --dry-run  Report only; change nothing.
+#   --yes      Skip the confirmation prompt (for soup and other automation).
+
+set -e
+
+DRY_RUN=false
+ASSUME_YES=false
+
+usage() {
+  sed -n '8,24p' "$0" | sed 's/^# \?//'
+}
+
+while [[ $# -gt 0 ]]; do
+  case "$1" in
+    --dry-run) DRY_RUN=true ;;
+    --yes|-y)  ASSUME_YES=true ;;
+    -h|--help) usage; exit 0 ;;
+    *) echo "Unknown option: $1" >&2; usage >&2; exit 1 ;;
+  esac
+  shift
+done
+
+fail() { echo "ERROR: $*" >&2; exit 1; }
+
+psql_tg() { docker exec -i so-postgres psql -U postgres -d so_telegraf "$@"; }
+
+# Row counts have to come from dynamic SQL; query_to_xml keeps that in a plain
+# query so this needs no helper functions installed in the database.
+REPORT="
+WITH parents AS (
+    SELECT pc.parent_table,
+           split_part(pc.parent_table, '.', 1) AS sch,
+           split_part(pc.parent_table, '.', 2) AS tbl
+    FROM partman.part_config pc
+    WHERE pc.parent_table LIKE 'telegraf.%'
+), children AS (
+    SELECT p.parent_table,
+           max(to_date(substring(c.relname FROM '_p(\d{8})\$'), 'YYYYMMDD')) AS newest_child
+    FROM parents p
+    JOIN pg_class pt ON pt.oid = p.parent_table::regclass
+    JOIN pg_inherits i ON i.inhparent = pt.oid
+    JOIN pg_class c ON c.oid = i.inhrelid
+    WHERE pg_get_expr(c.relpartbound, c.oid) <> 'DEFAULT'
+    GROUP BY p.parent_table
+), defaults AS (
+    SELECT p.parent_table,
+           format('%I.%I', p.sch, p.tbl || '_default') AS default_table,
+           to_regclass(format('%I.%I', p.sch, p.tbl || '_default')) AS default_oid
+    FROM parents p
+)
+SELECT d.parent_table,
+       c.newest_child,
+       (c.newest_child - current_date) AS days_ahead,
+       CASE WHEN d.default_oid IS NULL THEN NULL ELSE
+            (xpath('/row/cnt/text()',
+                   query_to_xml(format('SELECT count(*) AS cnt FROM %s', d.default_table),
+                                false, true, '')))[1]::text::bigint
+       END AS default_rows,
+       CASE WHEN d.default_oid IS NULL THEN NULL
+            ELSE pg_size_pretty(pg_total_relation_size(d.default_oid)) END AS default_size
+FROM defaults d
+LEFT JOIN children c ON c.parent_table = d.parent_table
+ORDER BY 1
+"
+
+docker ps --format '{{.Names}}' | grep -qx so-postgres \
+  || fail "so-postgres is not running."
+docker exec so-postgres psql -U postgres -tAc \
+  "SELECT 1 FROM pg_database WHERE datname='so_telegraf'" | grep -q 1 \
+  || fail "The so_telegraf database does not exist; Telegraf is not writing to Postgres."
+psql_tg -tAc "SELECT 1 FROM pg_extension WHERE extname='pg_partman'" | grep -q 1 \
+  || fail "pg_partman is not installed in so_telegraf."
+
+echo "Telegraf partition status:"
+psql_tg -c "$REPORT"
+
+stranded=$(psql_tg -tAc "SELECT coalesce(sum(default_rows), 0) FROM ( $REPORT ) t")
+echo "Rows stranded in default partitions: $stranded"
+
+# Report the scheduler too. A grid that needed this script almost always has a
+# pg_cron job that has never fired, and repairing partitions without fixing that
+# just delays the next occurrence.
+cron_db=$(docker exec so-postgres psql -U postgres -tAc \
+  "SELECT current_setting('cron.database_name', true)" | tr -d '[:space:]')
+if [[ -n "$cron_db" ]]; then
+  runs=$(docker exec so-postgres psql -U postgres -d "$cron_db" -tAc \
+    "SELECT count(*) FROM cron.job_run_details d JOIN cron.job j USING (jobid)
+      WHERE j.jobname = 'telegraf-partman-maintenance'" 2>/dev/null | tr -d '[:space:]' || true)
+  if [[ "$runs" == "0" ]]; then
+    echo
+    echo "WARNING: the telegraf-partman-maintenance job has never run. Apply the"
+    echo "         postgres state so pg_cron is pointed at a database that exists"
+    echo "         at server start, or this will recur."
+  fi
+fi
+
+if [[ "$stranded" == "0" ]]; then
+  echo "Nothing stranded. Running maintenance to premake the current window."
+  $DRY_RUN && { echo "(dry run: skipping maintenance)"; exit 0; }
+  psql_tg -v ON_ERROR_STOP=1 -c "CALL partman.run_maintenance_proc()"
+  exit 0
+fi
+
+if $DRY_RUN; then
+  echo "(dry run: would TRUNCATE the default partitions listed above)"
+  exit 0
+fi
+
+if ! $ASSUME_YES; then
+  echo
+  echo "This will permanently discard the $stranded stranded row(s) above."
+  [[ -t 0 ]] || fail "Not a terminal; re-run with --yes to confirm."
+  read -r -p "Continue? [y/N] " answer
+  [[ "$answer" =~ ^[Yy]$ ]] || { echo "Aborted."; exit 1; }
+fi
+
+psql_tg -v ON_ERROR_STOP=1 <<'EOSQL'
+SELECT format('TRUNCATE TABLE %I.%I', n.nspname, c.relname)
+FROM partman.part_config pc
+JOIN pg_class p ON p.oid = pc.parent_table::regclass
+JOIN pg_inherits i ON i.inhparent = p.oid
+JOIN pg_class c ON c.oid = i.inhrelid
+JOIN pg_namespace n ON n.oid = c.relnamespace
+WHERE pc.parent_table LIKE 'telegraf.%'
+  AND pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT'
+\gexec
+CALL partman.run_maintenance_proc();
+EOSQL
+
+echo
+echo "Telegraf partition status after repair:"
+psql_tg -c "$REPORT"
@@ -5,11 +5,20 @@ set -e
 # Usage: so-telegraf-postgres <subcommand>
 #   create_db    Ensure the so_telegraf database exists.
 #   group_role   Provision the so_telegraf group role, telegraf/partman schemas,
-#                pg_partman, pg_cron, and the hourly partman maintenance job.
+#                pg_partman, the so_admin maintenance routines, and the hourly
+#                pg_cron maintenance job.
 #   user         Create or update a per-minion login role granted to so_telegraf.
 #                Env: ROLE_USER, ROLE_PASS.
-#   retention    Reconcile partman retention on telegraf parents.
+#   retention    Reconcile partman retention and premake on telegraf parents.
 #                Env: RETENTION_DAYS.
+#   maintenance  Drain default partitions and run partman maintenance.
+#   check        Report partition health. Non-zero if any parent is unhealthy.
+#
+# Once a default partition holds rows for a day with no child, Postgres cannot
+# create that child at all -- attaching it would violate the default's
+# constraint -- so maintenance drains defaults before calling partman.
+# To recover a grid that is already in that state, use
+# so-telegraf-partition-repair, which discards the backlog instead of moving it.

 cmd="${1:?subcommand required}"

@@ -36,7 +45,6 @@ CREATE SCHEMA IF NOT EXISTS telegraf AUTHORIZATION so_telegraf;
 GRANT USAGE, CREATE ON SCHEMA telegraf TO so_telegraf;
 CREATE SCHEMA IF NOT EXISTS partman;
 CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
-CREATE EXTENSION IF NOT EXISTS pg_cron;
 -- Telegraf (running as so_telegraf) calls partman.create_parent()
 -- on first write of each metric, which needs USAGE on the partman
 -- schema, EXECUTE on its functions/procedures, and write access to
@@ -51,12 +59,141 @@ ALTER DEFAULT PRIVILEGES IN SCHEMA partman
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO so_telegraf;
 ALTER DEFAULT PRIVILEGES IN SCHEMA partman
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO so_telegraf;
-- Hourly partman maintenance. cron.schedule is idempotent by jobname.
-SELECT cron.schedule(
+
+-- pg_cron runs these as postgres, so they must not sit in a schema any
+-- Telegraf role can create objects in.
+CREATE SCHEMA IF NOT EXISTS so_admin AUTHORIZATION postgres;
+REVOKE ALL ON SCHEMA so_admin FROM PUBLIC;
+
+CREATE OR REPLACE PROCEDURE so_admin.telegraf_maintenance()
+LANGUAGE plpgsql
+AS $proc$
+DECLARE
+    r          record;
+    v_default  text;
+    v_rows     bigint;
+BEGIN
+    -- No per-parent EXCEPTION handler: partition_data_proc commits internally,
+    -- and COMMIT is illegal while a subtransaction is active. A failing parent
+    -- aborts the run and the next pass retries.
+    FOR r IN
+        SELECT parent_table, retention
+        FROM partman.part_config
+        WHERE parent_table LIKE 'telegraf.%'
+        ORDER BY parent_table
+    LOOP
+        v_default := format('%I.%I',
+                            split_part(r.parent_table, '.', 1),
+                            split_part(r.parent_table, '.', 2) || '_default');
+
+        CONTINUE WHEN to_regclass(v_default) IS NULL;
+
+        EXECUTE format('SELECT count(*) FROM %s', v_default) INTO v_rows;
+        CONTINUE WHEN v_rows = 0;
+
+        RAISE WARNING 'so_admin.telegraf_maintenance: % rows stranded in %, draining',
+                      v_rows, v_default;
+
+        -- Cheaper to delete expired rows than to repartition and then drop them.
+        IF r.retention IS NOT NULL THEN
+            EXECUTE format('DELETE FROM %s WHERE "time" < now() - %L::interval',
+                           v_default, r.retention);
+            COMMIT;
+        END IF;
+
+        -- Bounded so a large backlog drains across several runs.
+        CALL partman.partition_data_proc(
+            p_parent_table := r.parent_table,
+            p_loop_count   := 200,
+            p_source_table := v_default
+        );
+        COMMIT;
+    END LOOP;
+
+    CALL partman.run_maintenance_proc();
+END;
+$proc$;
+
+CREATE OR REPLACE FUNCTION so_admin.telegraf_partition_status()
+RETURNS TABLE (
+    parent_table  text,
+    oldest_child  date,
+    newest_child  date,
+    days_ahead    int,
+    retention     text,
+    default_rows  bigint,
+    default_size  text
+)
+LANGUAGE plpgsql
+AS $func$
+DECLARE
+    r          record;
+    v_default  regclass;
+BEGIN
+    FOR r IN
+        SELECT pc.parent_table AS pt, pc.retention AS ret
+        FROM partman.part_config pc
+        WHERE pc.parent_table LIKE 'telegraf.%'
+        ORDER BY pc.parent_table
+    LOOP
+        parent_table := r.pt;
+        retention    := r.ret;
+
+        SELECT min(d), max(d) INTO oldest_child, newest_child
+        FROM (
+            SELECT to_date(substring(c.relname FROM '_p(\d{8})$'), 'YYYYMMDD') AS d
+            FROM pg_inherits i
+            JOIN pg_class c ON c.oid = i.inhrelid
+            WHERE i.inhparent = r.pt::regclass
+              AND pg_get_expr(c.relpartbound, c.oid) <> 'DEFAULT'
+        ) s;
+
+        days_ahead := newest_child - current_date;
+
+        v_default := to_regclass(format('%I.%I',
+                                        split_part(r.pt, '.', 1),
+                                        split_part(r.pt, '.', 2) || '_default'));
+        IF v_default IS NULL THEN
+            default_rows := NULL;
+            default_size := NULL;
+        ELSE
+            EXECUTE format('SELECT count(*) FROM %s', v_default::text) INTO default_rows;
+            default_size := pg_size_pretty(pg_total_relation_size(v_default));
+        END IF;
+
+        RETURN NEXT;
+    END LOOP;
+END;
+$func$;
+
+-- Drop the registration older releases left in so_telegraf.
+SELECT CASE
+         WHEN current_setting('cron.database_name', true) IS DISTINCT FROM current_database()
+              AND EXISTS (SELECT 1 FROM pg_catalog.pg_extension WHERE extname = 'pg_cron')
+         THEN 'true' ELSE 'false'
+       END AS drop_stale_cron \gset
+\if :drop_stale_cron
+DROP EXTENSION pg_cron CASCADE;
+\endif
+EOSQL
+
+    # Guarded on the live GUC so applying this before the postgresql.conf change
+    # has restarted the container skips rather than failing.
+    docker exec -i so-postgres psql -v ON_ERROR_STOP=1 -U postgres -d postgres <<'EOSQL'
+SELECT CASE WHEN current_setting('cron.database_name', true) = current_database()
+            THEN 'true' ELSE 'false' END AS cron_here \gset
+\if :cron_here
+CREATE EXTENSION IF NOT EXISTS pg_cron;
+-- cron.schedule_in_database is idempotent by jobname.
+SELECT cron.schedule_in_database(
  'telegraf-partman-maintenance',
  '17 * * * *',
-  'CALL partman.run_maintenance_proc()'
+  'CALL so_admin.telegraf_maintenance()',
+  'so_telegraf'
 );
+\else
+\echo 'pg_cron metadata database is not `postgres` yet; skipping job registration.'
+\endif
 EOSQL
    ;;

@@ -90,6 +227,8 @@ EOSQL
    : "${RETENTION_DAYS:?RETENTION_DAYS is required}"
    # \gset + \if guards against a missing pg_partman without using a DO
    # block (psql :var substitution doesn't reach into dollar-quoted code).
+    # premake is reconciled here because telegraf.conf only applies it to
+    # parents created from now on.
    docker exec -i so-postgres psql \
      -v ON_ERROR_STOP=1 \
      -v retention_days="$RETENTION_DAYS" \
@@ -99,12 +238,55 @@ SELECT CASE WHEN EXISTS (SELECT 1 FROM pg_catalog.pg_extension WHERE extname = '
 \if :has_partman
 UPDATE partman.part_config
 SET retention = :'retention_days' || ' days',
-    retention_keep_table = false
+    retention_keep_table = false,
+    premake = 7
 WHERE parent_table LIKE 'telegraf.%';
 \endif
 EOSQL
    ;;

+  maintenance)
+    docker exec -i so-postgres psql -v ON_ERROR_STOP=1 -U postgres -d so_telegraf <<'EOSQL'
+SELECT CASE WHEN to_regproc('so_admin.telegraf_maintenance') IS NOT NULL
+            THEN 'true' ELSE 'false' END AS has_proc \gset
+\if :has_proc
+CALL so_admin.telegraf_maintenance();
+\else
+\echo 'so_admin.telegraf_maintenance() is missing; run so-telegraf-postgres group_role first.'
+\endif
+EOSQL
+    ;;
+
+  check)
+    docker exec -i so-postgres psql -U postgres -d so_telegraf <<'EOSQL'
+\pset border 2
+SELECT * FROM so_admin.telegraf_partition_status();
+EOSQL
+    docker exec -i so-postgres psql -U postgres -d postgres <<'EOSQL'
+\pset border 2
+SELECT CASE WHEN to_regclass('cron.job_run_details') IS NOT NULL
+            THEN 'true' ELSE 'false' END AS has_cron \gset
+\if :has_cron
+SELECT d.status, d.return_message, d.start_time
+FROM cron.job_run_details d
+JOIN cron.job j ON j.jobid = d.jobid
+WHERE j.jobname = 'telegraf-partman-maintenance'
+ORDER BY d.start_time DESC
+LIMIT 5;
+\else
+\echo 'pg_cron is not installed in this database.'
+\endif
+EOSQL
+    unhealthy=$(docker exec so-postgres psql -U postgres -d so_telegraf -tAc \
+      "SELECT count(*) FROM so_admin.telegraf_partition_status()
+       WHERE coalesce(default_rows, 0) > 0 OR coalesce(days_ahead, -1) < 1")
+    if [ "${unhealthy:-1}" != "0" ]; then
+      echo "so-telegraf-postgres check: $unhealthy telegraf parent(s) unhealthy" >&2
+      exit 1
+    fi
+    echo "so-telegraf-postgres check: all telegraf parents healthy"
+    ;;
+
  *)
    echo "Unknown subcommand: $cmd" >&2
    exit 1
@@ -122,7 +122,7 @@
  create_templates = [
    '''CREATE TABLE IF NOT EXISTS {{ .table }} ({{ .columns }}) PARTITION BY RANGE ("time")''',
    '''ALTER TABLE {{ .table }} ALTER COLUMN "time" SET NOT NULL''',
-    '''SELECT partman.create_parent(p_parent_table := {{ printf "%s.%s" .table.Schema .table.Name | quoteLiteral }}, p_control := 'time', p_type := 'range', p_interval := '1 day', p_premake := 3) WHERE NOT EXISTS (SELECT 1 FROM partman.part_config WHERE parent_table = {{ printf "%s.%s" .table.Schema .table.Name | quoteLiteral }})'''
+    '''SELECT partman.create_parent(p_parent_table := {{ printf "%s.%s" .table.Schema .table.Name | quoteLiteral }}, p_control := 'time', p_type := 'range', p_interval := '1 day', p_premake := 7) WHERE NOT EXISTS (SELECT 1 FROM partman.part_config WHERE parent_table = {{ printf "%s.%s" .table.Schema .table.Name | quoteLiteral }})'''
  ]
  tag_table_create_templates = [
    '''CREATE TABLE IF NOT EXISTS {{ .table }} ({{ .columns }}, PRIMARY KEY (tag_id))'''