pg_cron's launcher connects to cron.database_name at postmaster start and is
registered BGW_NEVER_RESTART. On a host upgraded onto an existing /nsm/postgres
volume, init-db.sh never runs, so so_telegraf does not exist when PostgreSQL
starts -- the launcher dies and never retries. Salt then creates the database,
the extension, and the schedule, all of which succeed, but no worker is left to
fire the job. partman.run_maintenance_proc() therefore never runs: partitions
stop being premade after create_parent's initial window and every metric lands
in <parent>_default. Retention never fires either.
That state is self-perpetuating. Once the default partition holds rows for a day
with no child, PostgreSQL cannot create that child at all -- attaching it would
violate the default partition's constraint -- so maintenance aborts on the first
parent it reaches. Fixing the scheduler alone does not recover a stalled grid.
Point cron.database_name at the always-present postgres database and register the
job with cron.schedule_in_database targeting so_telegraf, so the launcher no
longer depends on database creation order. group_role drops any registration left
behind in so_telegraf, and both halves are guarded on the live GUC so applying
postgres.telegraf_users before the postgresql.conf change has restarted the
container skips instead of failing.
Maintenance now runs so_admin.telegraf_maintenance(), which drains stranded rows
out of any default partition before calling partman: expired rows are deleted,
the rest are repartitioned. It runs from the state on every highstate as well as
hourly from pg_cron, so a grid whose worker is dead still recovers on its own.
The routines live in a postgres-owned schema so_telegraf has no rights on, since
pg_cron executes them as postgres.
Existing grids are recovered by a marker-guarded repair state that truncates the
non-empty defaults once per host. The backlog is mostly past retention already
and moving tens of GB just to delete most of it is not worth the WAL.
Also raise premake from 3 to 7, reconciled onto existing parents in the retention
subcommand, so an outage has a week of headroom before anything reaches a
default partition, and add a check subcommand reporting partition age, default
occupancy and last job status.
- Deliver postgres super and app passwords via mounted 0600 secret files
(POSTGRES_PASSWORD_FILE, SO_POSTGRES_PASS_FILE) instead of plaintext env
vars visible in docker inspect output
- Mount a managed pg_hba.conf that only allows local trust and hostssl
scram-sha-256 so TCP clients cannot negotiate cleartext sessions
- Restrict postgres.key to 0400 and ensure owner/group 939
- Set umask 0077 on so-postgres-backup output
- Validate host values in so-stats-show against [A-Za-z0-9._-] before SQL
interpolation so a compromised minion cannot inject SQL via a tag value
- Coerce postgres:telegraf:retention_days to int before rendering into SQL
- Escape single quotes when rendering pillar values into postgresql.conf
- Own postgres tooling in /usr/sbin as root:root so a container escape
cannot rewrite admin scripts
- Gate ES migration TLS verification on esVerifyCert (default false,
matching the elastic module's existing pattern)
Every telegraf.* metric table is now a daily time-range partitioned
parent managed by pg_partman. Retention drops old partitions instead
of the row-by-row DELETE that so-telegraf-trim used to run nightly,
and dashboards will benefit from partition pruning at query time.
- Load pg_cron at server start via shared_preload_libraries and point
cron.database_name at so_telegraf so job metadata lives alongside
the metrics
- Telegraf create_templates override makes every new metric table a
PARTITION BY RANGE (time) parent registered with partman.create_parent
in one transaction (1 day interval, 3 premade)
- postgres_telegraf_group_role now also creates pg_partman and pg_cron
extensions and schedules hourly partman.run_maintenance_proc
- New retention reconcile state updates partman.part_config.retention
from postgres.telegraf.retention_days on every apply
- so_telegraf_trim cron is now unconditionally absent; script stays on
disk as a manual fallback
Introduces global.telegraf_output (INFLUXDB|POSTGRES|BOTH, default BOTH)
so Telegraf can write metrics to Postgres alongside or instead of
InfluxDB. Each minion authenticates with its own so_telegraf_<minion>
role and writes to a matching schema inside a shared so_telegraf
database, keeping blast radius per-credential to that minion's data.
- Per-minion credentials auto-generated and persisted in postgres/auth.sls
- postgres/telegraf_users.sls reconciles roles/schemas on every apply
- Firewall opens 5432 only to minion hostgroups when Postgres output is active
- Reactor on salt/auth + orch/telegraf_postgres_sync.sls provision new
minions automatically on key accept
- soup post_to_3.1.0 backfills users for existing minions on upgrade
- so-show-stats prints latest CPU/mem/disk/load per minion for sanity checks
- so-telegraf-trim + nightly cron prune rows older than
postgres.telegraf.retention_days (default 14)
Phase 1 of the PostgreSQL central data platform:
- Salt states: init, enabled, disabled, config, ssl, auth, sostatus
- TLS via SO CA-signed certs with postgresql.conf template
- Two-tier auth: postgres superuser + so_postgres application user
- Firewall restricts port 5432 to manager-only (HA-ready)
- Wired into top.sls, pillar/top.sls, allowed_states, firewall
containers map, docker defaults, CA signing policies, and setup
scripts for all manager-type roles
Mike Reeves