Day 39 — Data service pattern

Updated

July 30, 2026

Day 39 — Data service pattern

Stage IV · ~4h
Goal: Run one data service—PostgreSQL by default—declaratively on NixOS: module options, state directories, app role/auth via secrets paths, and a written backup story (even if automation is minimal).

Important

DB passwords go through sops-nix paths, not Nix strings. Examples use placeholders and path references only.

Why this day exists

Application servers without state are easy. Real systems need durable data:

Concern NixOS angle
Package + unit services.postgresql module
State location services.postgresql.dataDir / ensure on persistent disk
Auth peer/scram; password files from secrets
Network exposure localhost first (Day 37)
Backups Not automatic just because Nix is declarative

Nix rebuilds the software and config; it does not magically version your rows.

Theory 1 — One DB only (pattern depth)

Syllabus: PostgreSQL or another single DB. Depth > zoo.

We use PostgreSQL. If you already run another engine at work, the pattern transfers: module → state dir → credentials → firewall → backups.

Theory 2 — Minimal PostgreSQL module

# modules/services/postgres.nix
{ config, pkgs, ... }:
{
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_16; # pick a version available on 26.05 pin
    ensureDatabases = [ "labapp" ];
    ensureUsers = [
      {
        name = "labapp";
        ensureDBOwnership = true;
      }
    ];
    # authentication / settings via extra plugins or ensure* options
    settings = {
      # listen_addresses = "localhost"; # confirm option name on your version
    };
  };

  # Do NOT open 5432 on the host firewall for world access in the default lab.
}

Verify option names against search.nixos.org for 26.05.

Theory 3 — State directories and disk

PostgreSQL data lives outside the Nix store (mutable).

Question Answer you must know
Where is dataDir? Document path
Is it on the disk that survives reinstall? Stage VI Disko/impermanence later
What happens on rollback of system generation? Software rolls back; data may not

Impermanence hosts (later) need explicit persistence for DB dirs—note the footgun now.

Theory 4 — Credentials

Patterns:

  1. Peer auth for local system users (good for admin)
  2. SCRAM password for app users—password from sops at runtime
  3. Socket-only access for local apps

Sketch with sops (illustrative—align with module capabilities):

sops.secrets."postgres/labapp_password" = {
  owner = "postgres";
  mode = "0400";
};

# Prefer module-supported passwordFile style options when available.
# If you script role passwords, run a oneshot that reads the secret path
# and does not print it.

Never:

# BAD
services.postgresql.ensureUsers = [{ name = "labapp"; password = "secret"; }];

(If an option even allows raw password—avoid it.)

Theory 5 — App connection story

App config should receive:

  • Host: 127.0.0.1 or socket
  • DB name: labapp
  • User: labapp
  • Password: from env file populated at activation from sops

Your Day 38 proxy does not replace DB auth. Backend talks to DB on localhost; users talk to proxy on 443.

Theory 6 — Backup thought (required cognitive work)

Even a one-node lab needs a plan:

Element Example
What pg_dump of labapp
When nightly timer (system or user)
Where /var/backups/postgres/ on another disk or remote
Encryption restic/age to remote (secrets via sops)
Restore test once per quarter / before capstone

Today: write the plan + optional manual pg_dump once. Full backup automation can be minimal.

sudo -u postgres pg_dump labapp > /tmp/labapp-dump.sql
# store off-box in real life; scrub /tmp later

Theory 7 — Upgrades

Postgres major upgrades are not “change package attr and forget.” They need dump/restore or pg_upgrade procedures. Pin a major version intentionally; schedule upgrades.

Worked example — Service + docs

modules/services/postgres.nix
docs/data-service.md
  - version
  - dataDir
  - DB/user names
  - auth method
  - backup sketch
  - restore sketch

Lab 1 — Enable PostgreSQL

Add module; rebuild; verify unit:

sudo systemctl status postgresql
sudo -u postgres psql -c '\l'

Lab 2 — Database and role

Confirm labapp DB and user via ensure* options. Connect:

sudo -u postgres psql -d labapp -c 'SELECT 1;'

Lab 3 — Wire a secret password path

Create sops secret placeholder for app password. Configure the safest supported method for your module version. Prove app user can authenticate without password living in the flake as plaintext.

Lab 4 — Network posture

ss -tulpn | grep 5432 || true

Confirm listening on localhost (or document intentional LAN bind). Ensure firewall does not expose 5432 publicly.

Update docs/ports.md.

Lab 5 — Manual dump

sudo -u postgres pg_dump labapp | head
# full dump to a safe path you control; do not commit dumps with sensitive data

Write restore commands in docs/data-service.md (even if restore is “psql < dump”).

Lab 6 — Failure narrative

Answer:

  1. System generation rollback after a bad app deploy—does data revert?
  2. Disk failure—what is lost without backups?
  3. Secret rotation steps for DB password

Theory 8 — ensureDatabases vs migrations

ensureDatabases / ensureUsers create empty roles/DBs. Application schema is not Nix’s job by default.

Layer Owner
Package + unit + listen policy NixOS module
Roles/DBs existence ensure* options / oneshots
Schema / migrations App tooling (flyway, django, …)
Data Backups

Do not expect rebuild to replay SQL migrations unless you deliberately automate that (usually outside pure module defaults).

Theory 9 — Socket vs TCP local connections

Postgres often exposes a Unix socket under /run/postgresql. Local admin via sudo -u postgres psql uses peer auth. Apps may use:

host=127.0.0.1 port=5432
# or host=/run/postgresql

Pick one, document in docs/data-service.md, match app secret env.

Worked example — Timer sketch for dumps (optional)

systemd.services.labapp-pg-dump = {
  serviceConfig = {
    Type = "oneshot";
    User = "postgres";
    ExecStart = "${pkgs.bash}/bin/bash -c '${pkgs.postgresql}/bin/pg_dump labapp | ${pkgs.gzip}/bin/gzip -c > /var/backups/labapp.sql.gz'";
  };
};
# + timer + StateDirectory/tmpfiles for /var/backups

Wire carefully (permissions, disk space). Even a commented sketch in the flake beats “we’ll back up later.”

Lab 7 — Schema independence proof

sudo -u postgres psql -d labapp -c "CREATE TABLE day39_demo(id int);"
sudo -u postgres psql -d labapp -c '\dt'
# rebuild switch without dropping data
sudo nixos-rebuild switch --flake .#lab
sudo -u postgres psql -d labapp -c '\dt'

Table should still exist—state ≠ generation.

Lab 8 — Document restore dry-run

Write exact commands in docs (do not need to destroy data):

# restore sketch
# sudo -u postgres psql -d labapp < labapp-dump.sql

Common gotchas

Symptom / mistake What to do
Data in store myth Data is on mutable disk
Open 5432 to world Bind localhost; close firewall
Password in nix sops path / peer auth
Major version bump casual Plan dump/restore
No backup thought Lab 5–6 required
Proxying Postgres over HTTP Wrong layer; don’t

Checkpoint

  • PostgreSQL enabled declaratively
  • App DB + user exist
  • Auth without plaintext secret in git
  • Localhost exposure default
  • Manual dump performed once
  • Backup/restore story written
  • ports.md updated

Commit

git add .
git commit -m "day39: declarative postgres data service pattern"

Write three personal gotchas before continuing.

Tomorrow

Day 40 — nixos-containers / OCI: isolation options on NixOS—declarative nspawn-style containers and when OCI helps without “Docker first” culture.