13 — Automation, Provisioning, and Monitoring

"Scripts that run stuff" was my automation model, and it flattened four genuinely different layers into one blob. The framing that organized it: automation lives at four distinct layers, each with its own tools — in-database logic via PL/pgSQL, host configuration via Ansible/Puppet/Chef/Salt, cluster orchestration via Kubernetes operators, and observability via Prometheus and the Golden Signals [1][2][3]. Once I saw the layers, the sprawling tool list became four focused choices about which layer a given task belongs to.

Layer 1: in-database logic with PL/pgSQL

The first layer of automation lives inside the database. PL/pgSQL is Postgres's procedural language — SQL extended with variables, conditionals, loops, and exception handling, compiled into server-side functions and procedures [1]. The win is moving logic next to the data: a function that updates three related tables and writes an audit row runs as one server round-trip, with full transactional guarantees.

CREATE FUNCTION transfer(src INT, dst INT, amt INTEGER) RETURNS VOID AS $$
BEGIN
  UPDATE accounts SET balance = balance - amt WHERE id = src;
  UPDATE accounts SET balance = balance + amt WHERE id = dst;
  INSERT INTO audit (action, amount) VALUES ('transfer', amt);
END;
$$ LANGUAGE plpgsql;

Functions return a value and can be used in SELECT; procedures (PostgreSQL 11+) can manage transactions (calling COMMIT/ROLLBACK internally) and are invoked with CALL. Triggers attach functions to table events (INSERT/UPDATE/DELETE), running automatically to maintain derived columns, audit trails, or cross-table invariants. The rule I follow: push logic into PL/pgSQL when it's intrinsically data-oriented and must be atomic, and keep it in application code when the logic spans multiple services or needs richer testing.

Layer 2: host configuration management

The second layer automates the server Postgres runs on. Configuration management tools — Ansible, Puppet, Chef, Salt — declare the desired state (Postgres installed, postgresql.conf set, extensions loaded, backups scheduled) and converge the host to it idempotently [2][3].

• Ansible — YAML playbooks run over SSH, agentless, the most common choice for Postgres. The community PostgreSQL modules handle users, databases, privileges, and extensions declaratively.
• Puppet / Chef / Salt — the alternatives, each with its own model (Puppet's DSL, Chef's Ruby, Salt's event-driven). The choice is mostly about what the rest of the infrastructure already uses.

The payoff is reproducibility. A new replica is "apply the playbook to a fresh box," not a multi-hour manual procedure. And configuration drift — someone editing postgresql.conf by hand on one node — is detected and corrected on the next run.

Layer 3: Kubernetes and operators

The third layer runs Postgres on Kubernetes [4]. Because Postgres is stateful (it owns persistent data), it needs more than a plain Deployment — it needs a StatefulSet with persistent volume claims, and ideally an Operator that encodes operational knowledge (backup, failover, upgrade) as Kubernetes controllers.

• StatefulSet — gives each pod a stable identity and persistent volume, so restarts reattach the same data.
• Helm — a package manager for Kubernetes; a Helm chart bundles the YAML for a Postgres deployment into a versioned, configurable unit [5].
• Operators (CloudNativePG, Zalando Postgres Operator, CrunchyData PGO) — automate day-2 operations: cluster creation, replication, backups, failover, and major-version upgrades, all via Kubernetes-native resources.

Running Postgres on Kubernetes is a real tradeoff. The operator handles a lot, but stateful data on an ephemeral-ephemeral orchestration platform adds complexity and operational risk. I reach for it when the rest of the platform is already on Kubernetes and I want Postgres managed the same way; for a single dedicated database server, plain host + configuration management is simpler.

Layer 4: observability

The fourth layer is knowing what the database is doing. Monitoring is what turns "it's slow" into a specific query, a specific lock, a specific saturated resource [6].

The two methodical frameworks are USE (Utilization, Saturation, Errors — for resources like CPU, memory, disk) and RED (Rate, Errors, Duration — for requests like queries) [7][8]. The Four Golden Signals — latency, traffic, errors, saturation — fuse both into a single checklist drawn from Google's SRE practice [9]. A Postgres dashboard built on these covers the right ground: query rate and latency (RED/traffic+latency), error rate (errors), and resource saturation (USE/saturation).

The tools that feed this: Prometheus scrapes metrics (via exporters like postgres_exporter) and evaluates alert rules [10]; pg_stat_statements records per-query execution statistics; pg_stat_activity shows current connections and running queries; PgBadger parses logs into reports; pgcenter, temBoard, and Zabbix provide higher-level Postgres-aware monitoring. The combination I default to is Prometheus + postgres_exporter + pg_stat_statements, surfaced in a Golden-Signals dashboard.

How I use this

The four-layer model is the routing rule. Data-atomic logic that must be transactional goes into PL/pgSQL functions and triggers. Anything about the host — install, config, extensions, scheduled jobs — goes into Ansible so it's reproducible. Running on Kubernetes is reserved for cases where the whole platform is already there, and I lean on an operator (CloudNativePG or PGO) rather than hand-rolling StatefulSets. And observability is set up from day one: pg_stat_statements enabled, postgres_exporter feeding Prometheus, and a Golden-Signals dashboard so "the database is slow" always resolves to a specific query or a saturated resource. Automation isn't one thing; it's four layers, each chosen by what's being automated.

References

[1] PostgreSQL Global Development Group, "PL/pgSQL — SQL Procedural Language," 2024. [Online]. Available: https://www.postgresql.org/docs/current/plpgsql.html

[2] Ansible, "Ansible," 2024. [Online]. Available: https://www.ansible.com/

[3] Puppet, "Puppet PostgreSQL module," 2024. [Online]. Available: https://forge.puppet.com/modules/puppetlabs/postgresql/

[4] CloudNativePG, "Postgres on Kubernetes," 2024. [Online]. Available: https://cloudnative-pg.io/

[5] Helm, "Helm," 2024. [Online]. Available: https://helm.sh/

[6] Prometheus, "Prometheus," 2024. [Online]. Available: https://prometheus.io/

[7] B. Gregg, "The USE Method," 2024. [Online]. Available: https://www.brendangregg.com/usemethod.html

[8] The New Stack, "The RED Method: a new approach to monitoring microservices," 2023. [Online]. Available: https://thenewstack.io/monitoring-microservices-red-method

[9] Google SRE, "Monitoring Distributed Systems: The Four Golden Signals," 2024. [Online]. Available: https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signals

[10] Timescale, "Using pg_stat_statements to optimize queries," 2024. [Online]. Available: https://www.timescale.com/blog/using-pg-stat-statements-to-optimize-queries/