--- title: "13 — Automation, Provisioning, and Monitoring" uid: automation-orchestration tags: ["kubernetes", "roadmap:postgresql-dba", "monitoring", "prometheus", "automation", "ansible", "postgresql", "plpgsql", "operators"] excerpt: "Automation lives at four layers, each with its own tools: in-database logic (PL/pgSQL), host configuration (Ansible/Puppet), cluster orchestration (Kubernetes operators), observability (Prometheus, Golden Signals)." date: 2026-08-13T03:27:50+0000 source: https://www.aveshina.my.id/en/blog/automation-orchestration --- "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]. ```figure USE — resource-scoped Utilization · Saturation · Errors CPU %, memory, disk queue length, network errors for each resource, is it busy/queued/erroring? RED — request-scoped Rate · Errors · Duration queries/sec, failed queries, query latency distribution for each request type, how fast/failing/slow? Four Golden Signals (unified checklist) Latency · Traffic · Errors · Saturation the minimal dashboard for any service, Postgres included ``` 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](https://www.postgresql.org/docs/current/plpgsql.html) [2] Ansible, "Ansible," 2024. [Online]. Available: [https://www.ansible.com/](https://www.ansible.com/) [3] Puppet, "Puppet PostgreSQL module," 2024. [Online]. Available: [https://forge.puppet.com/modules/puppetlabs/postgresql/](https://forge.puppet.com/modules/puppetlabs/postgresql/) [4] CloudNativePG, "Postgres on Kubernetes," 2024. [Online]. Available: [https://cloudnative-pg.io/](https://cloudnative-pg.io/) [5] Helm, "Helm," 2024. [Online]. Available: [https://helm.sh/](https://helm.sh/) [6] Prometheus, "Prometheus," 2024. [Online]. Available: [https://prometheus.io/](https://prometheus.io/) [7] B. Gregg, "The USE Method," 2024. [Online]. Available: [https://www.brendangregg.com/usemethod.html](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](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](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/](https://www.timescale.com/blog/using-pg-stat-statements-to-optimize-queries/) ```quiz Q: What is the key difference between a PL/pgSQL function and a procedure (PostgreSQL 11+)? - Functions are faster; procedures are safer - A function returns a value and is used in SELECT; a procedure can manage transactions (COMMIT/ROLLBACK) and is called with CALL correct: 1 explain: Functions integrate into SQL queries and must return a value. Procedures don't return a value but can control transactions internally, which functions cannot. Q: Why use a configuration management tool like Ansible instead of configuring Postgres hosts by hand? - Ansible runs queries faster - It declares the desired state idempotently, making setup reproducible and detecting configuration drift on subsequent runs correct: 1 explain: Configuration management turns "set up a database server" into a versioned, repeatable playbook. New hosts converge to the declared state, and manual edits get corrected automatically. Q: Why does PostgreSQL on Kubernetes need a StatefulSet (and ideally an Operator) rather than a plain Deployment? - Because Deployments cannot run containers - Because Postgres is stateful — it needs stable identity and persistent volumes, and an Operator automates backups, replication, failover, and upgrades correct: 1 explain: Deployments are for stateless workloads. Postgres needs stable storage and identity (StatefulSet) plus day-2 automation (Operator) to handle the operational complexity of a stateful database. Q: Which monitoring framework is resource-scoped (Utilization, Saturation, Errors for CPU, memory, disk)? - RED - USE correct: 1 explain: USE is for resources. RED (Rate, Errors, Duration) is request-scoped. The Four Golden Signals combine both into a single service-health checklist. Q: The minimal Postgres observability dashboard covers Latency, Traffic, Errors, and Saturation (the Golden Signals). Which tool records per-query execution statistics to feed the Latency and Traffic signals? - pg_stat_statements - pg_verifybackup correct: 0 explain: pg_stat_statements accumulates per-query call counts, total time, and rows. It's the source of query-rate and query-latency data, fed via postgres_exporter into Prometheus. ```