PostgreSQL

Overview

The server runs one PostgreSQL instance for platform services: the identity provider and the host applications. Product applications in Kubernetes get their own databases there and never share this one.

Module: modules/features/postgresql.nix

Property Value
Version 17, pinned with services.postgresql.package
Data /var/lib/postgresql/17, persisted under /persist
Consumers Keycloak, Miniflux, Invidious
flowchart LR
    keycloak[Keycloak] --> pg[(PostgreSQL 17)]
    miniflux[Miniflux] -->|"TCP 127.0.0.1:5432"| pg
    invidious[Invidious] --> pg
    pg --> data["/persist/var/lib/postgresql"]
    data -->|"btrfs snapshot"| restic[Restic → B2]

Adding a Consumer

The module owns only the server. Each consumer creates its own role and database through its upstream option, usually createLocally or createDatabaseLocally, which fills ensureUsers and ensureDatabases.

The consumer module:

An assertion in modules/features/impermanence.nix fails evaluation when a host enables PostgreSQL without postgresql.nix, because its data directory would then be wiped on every boot.

Backups

/persist/var/lib/postgresql is in the Restic path list and is read from a read-only btrfs snapshot, so the backup is crash-consistent: restoring it is equivalent to recovering from a power cut. Reference Backblaze B2 Backups.

Major Version Upgrades

The version is pinned because each major version keeps its data in a separate directory (/var/lib/postgresql/<major>) and needs pg_upgrade. Changing the package without migrating starts an empty cluster next to the old one.

The procedure, run on the server:

  1. Add the upgrade-pg-cluster script from the NixOS manual to the module, targeting the new package, and deploy
  2. Stop the consumers, then run upgrade-pg-cluster as root
  3. Change services.postgresql.package to the new version and deploy
  4. Run the vacuumdb command printed by the script
  5. Remove the script and the old data directory once the consumers work

Reference the NixOS manual, https://nixos.org/manual/nixos/stable/#module-services-postgres-upgrading.