CloudNativePG Part 6: Migrating Umami Without Losing a Row

Introduction

bletchley-pg has been live since August 5, chaos-tested in Part 4, and took its first real tenant, lldap, in Part 5. That database was created empty. This one already had six months of analytics in it.

The umami database has lived on the legacy single-instance PostgreSQL StatefulSet in namespace postgres since June, where it landed after moving off a Docker container on docker.luwte.net. It has no HA, no point-in-time recovery, no TLS, and a bespoke per-database pg_dump-to-Garage backup system that exists only because CloudNativePG didn't when it was built. Everything that system does, bletchley-pg already does better and by default.

So the job was narrow: move one 10 MB database between two PostgreSQL instances on the same cluster, prove the data came across intact, and repoint the application. The Umami application itself, a Node.js service on sulu.luwte.net behind Apache as analytics.vluwte.nl, was not touched. Only the database moved, and only the connection string changed.

Total downtime was 5 minutes 54 seconds, of which about one minute was actually moving data. The interesting parts happened before and after that window.

This post is part of the CloudNativePG sub-series.

• Part 4: Breaking a Database on Purpose
• Part 5: lldap's Database Goes First
• Part 6: Migrating Umami Without Losing a Row

🏠 This is part of the Homelab Journey series - building a production Kubernetes cluster from scratch.

• Secret Management Part 5
• CloudNativePG Part 6: Migrating Umami Without Losing a Row (you are here)

This post assumes a working CloudNativePG cluster with the Database and DatabaseRole tenant pattern from Part 5, and External Secrets pulling credentials from OpenBao.

Every manifest, query and command in this post is shown abbreviated or omitted entirely. The complete, runnable set is in the addendum at the bottom of the post.

The Approach: Offline Dump and Restore

Logical replication would have given near-zero downtime. It was rejected: the database is 10 MB, Umami is analytics rather than anything transactional, and the source instance is being decommissioned anyway, so there would be no long-term subscription worth maintaining. A stop-dump-restore-start cutover is trivially verifiable and leaves the source untouched as the rollback.

All the dump and restore work ran from a throwaway pod inside the cluster rather than from sulu. sulu has no PostgreSQL client tooling and is not getting any, an in-cluster pod removes the client-version question entirely, and the transfer stays on ClusterIP instead of going out through a LoadBalancer and back. The pod is stored as a reusable template at apps/databases/_template/pg-migrate-pod.yaml, because the next tenant migration will want exactly the same thing.

Pre-flight

The whole plan rests on facts about the source, and every one of them was checked rather than assumed. The queries are in the addendum; this is what they established.

Two of those deserve a sentence each.

pgcrypto was the single biggest identified risk. bletchley-pg runs with enableSuperuserAccess: false, so there is no postgres password login and no privileged step available mid-restore. If the restore needed superuser to create an extension, the whole plan needed rewriting. It didn't: pgcrypto has been a trusted extension since PostgreSQL 13, and the target confirmed trusted = t for the same version 1.3. A trusted extension is creatable by the database owner. The biggest risk in the plan cost one line of log output.

public being owned by pg_database_owner, the PostgreSQL 15+ default, is what makes DROP SCHEMA public CASCADE work as the umami role during the cutover, with no fallback needed.

Declaring the Tenant

Four objects, applied in dependency order: SecretStore → ExternalSecret → DatabaseRole → Database. All four live in apps/umami/ and all four are in the addendum in full.

A fresh credential was written to OpenBao at secret/umami/db-credential, deliberately beside the legacy credential at secret/umami/db rather than as a new version of it. Both live simultaneously through the migration, which is what keeps the rollback real. The password is hex rather than base64, because it goes into a postgresql:// URL and base64 output contains characters that don't survive that trip cleanly.

Two details carried over from Part 5 that are worth repeating rather than rediscovering:

    template:
      type: kubernetes.io/basic-auth      # required: CNPG rejects Opaque
      metadata:
        labels:
          cnpg.io/reload: "true"          # required: without it CNPG never picks up changes

And the one that looks like a mistake and is not:

  dataFrom:
    - extract:
        key: secret/data/umami/db-credential

The key is the full path including the KV v2 data/ segment, even though the SecretStore already sets path: secret and version: v2. That is the SecretStore URL-format quirk from Moving the First Database to the Cluster. Copy it; do not "fix" it. OpenBao itself echoes that exact string back on the bao kv put, which is a free confirmation before the manifest is ever written.

The DatabaseRole needs passwordSecret pointing at the synced Secret. CNPG never generates a role password; omit it and the role is created with a NULL password, healthy-looking and unable to log in.

Everything reconciled first time: SecretStore Valid/True in 5 s, ExternalSecret SecretSynced in 7 s, both CRs reporting APPLIED true with an empty MESSAGE, and a successful login as umami. APPLIED is the column to watch on Database and DatabaseRole, because there is no Ready condition in the default output.

The Dry Run, and Testing the Test

With the application still live, a full dump and restore ran once as a rehearsal. The point of a rehearsal is the comparison that follows it, so that came first: one script that renders the whole shape of a database as sorted, prefixed, plain text.

\pset pager off
\pset format unaligned
\pset fieldsep '|'
\pset tuples_only on

SELECT 'COL', table_name, ordinal_position, column_name, data_type, is_nullable
  FROM information_schema.columns WHERE table_schema = 'public'
 ORDER BY table_name, ordinal_position;

SELECT 'ROWS', relname,
       (xpath('/row/c/text()',
              query_to_xml(format('SELECT count(*) AS c FROM public.%I', relname),
                           false, true, '')))[1]::text::bigint
  FROM pg_stat_user_tables ORDER BY relname;

SELECT 'IDX', indexname, indexdef
  FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;

SELECT 'CON', conrelid::regclass::text, conname, pg_get_constraintdef(oid)
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace ORDER BY 2, 3;

SELECT 'SEQ', sequencename, last_value
  FROM pg_sequences WHERE schemaname = 'public' ORDER BY sequencename;

SELECT 'EXT', extname, extversion FROM pg_extension ORDER BY extname;

The four \pset lines are what make it comparable at all: unaligned output with an explicit field separator and no headers or row counts, so nothing in the text depends on column widths. The literal 'COL', 'ROWS', 'IDX' prefixes keep the six result sets identifiable once they are concatenated into one file, and every query has an explicit ORDER BY so two runs cannot differ merely by ordering.

The ROWS query is the one worth a second look. Counting rows through query_to_xml looks like an odd way to write count(*), and it is, but it is the only way to get an exact count per table from a single query that iterates the table list. The obvious alternative, n_live_tup from pg_stat_user_tables, is a planner statistic rather than a count. On this database it reported 0 rows for tables that demonstrably had rows.

A second, shorter script covers content rather than structure: md5(string_agg(...)) over the user and website rows, plus count(*) with min/max timestamps on website_event. It is in the addendum.

Run both sides through the compare script, redirect each to a file, and compare the files:

igor@granite bletchley % kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" \
  psql -h postgres.postgres.svc.cluster.local -U umami -d umami \
  -f /tmp/pg-compare.sql > /tmp/cmp-legacy.txt
igor@granite bletchley % kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
  -f /tmp/pg-compare.sql > /tmp/cmp-cnpg.txt
igor@granite bletchley % diff -u /tmp/cmp-legacy.txt /tmp/cmp-cnpg.txt && echo "STRUCTURE + COUNTS IDENTICAL"
STRUCTURE + COUNTS IDENTICAL
igor@granite bletchley % md5sum /tmp/cmp-legacy.txt /tmp/cmp-cnpg.txt
bfd7595bc6e685c3c0d633db1b31b963  /tmp/cmp-legacy.txt
bfd7595bc6e685c3c0d633db1b31b963  /tmp/cmp-cnpg.txt

Which is the wanted result, and also exactly what a completely broken comparison looks like.

The plan had a built-in guard against that: Umami keeps writing to the legacy database during the dry run, so the EVENT counts were expected to differ, proving the script reads live data from two distinct servers. They didn't differ. At roughly five events a day, no writes happened to land in the window, and the most recent event predated the dump by about nineteen hours.

That check was not satisfied. It was unproven, and a check that cannot fail proves nothing. So instead of ticking the box, the two underlying claims were tested directly.

Are these actually two different servers? Four independent facts say yes: different server addresses, postmaster start times four days apart, and cluster_name empty on one side and bletchley-pg on the other. (pg_control_system().system_identifier would be the textbook proof, but its EXECUTE is superuser-only by default, and this cluster deliberately has no superuser login. A security decision from three posts ago shaping the answer to an unrelated question.)

Would the comparison notice a difference if one existed? A sentinel table was created on the CNPG copy only:

CREATE TABLE public._liveness_probe (id int PRIMARY KEY, note text);
INSERT INTO public._liveness_probe VALUES (1, 'dry-run sensitivity check');

Then the same two commands as before, against a third pair of files:

igor@granite bletchley % diff -u /tmp/cmp-legacy-probe.txt /tmp/cmp-cnpg-probe.txt && echo "!!! HARNESS FAILED TO DETECT THE PROBE"
--- /tmp/cmp-legacy-probe.txt
+++ /tmp/cmp-cnpg-probe.txt
@@ -1,6 +1,8 @@
+COL|_liveness_probe|1|id|integer|NO
+COL|_liveness_probe|2|note|text|YES
@@ -140,6 +142,7 @@
+ROWS|_liveness_probe|1
@@ -154,6 +157,7 @@
+IDX|_liveness_probe_pkey|CREATE UNIQUE INDEX _liveness_probe_pkey ON public._liveness_probe USING btree (id)
@@ -244,6 +248,7 @@
+CON|_liveness_probe|_liveness_probe_pkey|PRIMARY KEY (id)
igor@granite bletchley % md5sum /tmp/cmp-legacy-probe.txt /tmp/cmp-cnpg-probe.txt
bfd7595bc6e685c3c0d633db1b31b963  /tmp/cmp-legacy-probe.txt
a2e740e9fc84cc6c6381c22ba2e32fd7  /tmp/cmp-cnpg-probe.txt

The harness caught one two-column table in all four relevant sections: both columns under COL, the single row under ROWS, the implicit primary-key index under IDX, the constraint under CON. The && echo guard did not fire, because diff exited non-zero as it must when the files differ. And the legacy hash did not move from bfd7595b…, which proves the probe landed only on the copy and never touched the source.

Then the probe was dropped and both sides re-compared:

bfd7595bc6e685c3c0d633db1b31b963  /tmp/cmp-legacy-after.txt
bfd7595bc6e685c3c0d633db1b31b963  /tmp/cmp-cnpg-after.txt

Three hashes tell the whole story: bfd7595b… before, a2e740e9… with the probe, bfd7595b… again after cleanup. That one number does three jobs at once: the cleanup was complete, the script is deterministic across runs, and the earlier "identical" was a genuine comparison rather than a silent failure.

A verification step you have never seen fail is not a verification step.

The Cutover

August 29. Window open 13:41:52 CEST, closed 13:47:46 CEST. 5 minutes 54 seconds.

Umami was stopped on sulu, and the legacy database confirmed to have zero remaining connections, a clean stop with no pooled connections to wait out. The CNPG target, which still held the dry-run data, was emptied:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public AUTHORIZATION umami;

NOTICE:  drop cascades to 15 other objects
DETAIL:  drop cascades to extension pgcrypto
drop cascades to table _prisma_migrations
...
drop cascades to table website_event

That NOTICE is a receipt worth reading rather than scrolling past: 15 objects, pgcrypto plus exactly the 14 tables from the pre-flight baseline. Nothing more, nothing less. A following \dt returned Did not find any relations., which is the target confirmed empty before the final restore rather than hoped-for afterwards.

Then the final pg_dump -Fc from legacy, pg_restore --no-owner --no-privileges -j2 into CNPG, and a grep -iE 'error|permission denied' over the restore log that returned nothing at all.

The comparison scripts ran once more, and produced bfd7595bc6e685c3c0d633db1b31b963 on both sides, for the fifth and sixth time, and a hash that had been demonstrated to change when the data changes. Content checksums matched too, including EVENT|742 with identical min and max timestamps. The max is the stronger signal: a truncated dump would show a plausible-looking count with an earlier maximum.

Umami was started with a new DATABASE_URL pointing at pg.vluwte.nl:5432, and came up in ✓ Ready in 1289ms with no database errors, which is itself a positive result, since a wrong host or password surfaces immediately as a Prisma connection error on start-up.

The dashboard loaded, admin logged in, and the history was there. The login is the single most meaningful check in the whole list: it proves the password hash in the user table survived a --no-owner --no-privileges dump and restore across two different PostgreSQL builds, 17.11 on Alpine to 17.10 on Debian. A row count can never tell you that.

Proving the old path is dead

It is easy to check that the new thing works and never check that the old thing stopped. The dry run's drift check couldn't answer that; a deliberate write could. Own-visit exclusion was disabled and the site loaded from a different browser, because the Umami tracker is a common ad-block target and "nothing appeared" would otherwise be ambiguous between a broken migration and a blocked request.

=== CNPG ===
743 | 2026-08-29 12:33:00.008+00
=== LEGACY ===
742 | 2026-08-28 14:44:31.623+00

CNPG moved 742 → 743. Legacy stayed frozen at 742, with a maximum timestamp still identical to the pre-flight baseline. Both halves, four lines of output. "742, unchanged" is the sentence that actually retires the StatefulSet.

The TLS Thread: What sslmode=require Means to Prisma

Pre-flight turned up something the plan hadn't gone looking for: the legacy StatefulSet is a stock postgres:17-alpine with ssl = off. Umami's analytics traffic had been crossing VLAN 140 in clear text since June.

CNPG always runs ssl = on, so the fix looked free. verify-full was off the table. CNPG's server certificate carries sixteen SANs, all in-cluster service names, and pg.vluwte.nl is not among them; and its internal CA has a 90-day lifetime, so pinning a copy on an off-cluster host with no ESO and no trust-manager would silently expire around November. The plan settled on sslmode=require: encrypt, don't validate. That is what the PostgreSQL documentation says require means.

The application disagreed:

Error [PrismaClientKnownRequestError]:
Invalid `prisma.user.findUnique()` invocation:
Error opening a TLS connection: self-signed certificate in certificate chain
  code: 'P1011',
  clientVersion: '6.19.2'

sslmode is not portable across PostgreSQL clients. libpq's require means encrypt and do not verify. Prisma's Rust connector implements require as encrypt and verify the certificate chain, so it correctly refused CNPG's self-signed chain. The service starts; every query fails. "The PostgreSQL docs say X" is a statement about libpq, not about PostgreSQL clients in general.

sslaccept=accept_invalid_certs, Prisma's documented escape hatch for exactly this case, produced no change in behaviour. Cause not established, and recorded as an open item rather than a conclusion.

What shipped is the string with no sslmode at all:

DATABASE_URL=postgresql://umami:<password>@pg.vluwte.nl:5432/umami?schema=public

And then the question that should always follow a security assumption: is it actually encrypted?

 usename | client_addr | ssl | version |         cipher
---------+-------------+-----+---------+------------------------
 umami   | 10.244.6.25 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384

Prisma's default sslmode=prefer negotiates TLS without validating the chain, which is precisely the behaviour the plan wanted from require, arrived at by leaving the parameter out rather than putting it in. Six months of clear-text analytics traffic ended at 13:47 on August 29.

That is a real improvement, not a lateral move, and I am not going to pretend it was the intended route. What is missing is enforcement, and it is now blocked on something concrete rather than a preference: Prisma will not accept this certificate, so the fix is to give it one it will. That means a publicly-trusted Let's Encrypt certificate, which is work for later. It also means a hostname change first, because pg.vluwte.nl was always an interim name, and there is no sense certifying a name that is on its way out.

Backup Coverage: the Whole Point

A tenant that is merely present in the cluster is not a tenant that is protected. The closing gate was the next morning's scheduled backup:

bletchley-pg-daily-20260828051500   2d1h   bletchley-pg   plugin   completed
bletchley-pg-daily-20260829051500   25h    bletchley-pg   plugin   completed
bletchley-pg-daily-20260830051500   75m    bletchley-pg   plugin   completed

bletchley-pg-daily-20260830051500 is the first scheduled base backup taken with umami inside the cluster, and the unbroken run of completed before it shows that adding a tenant disturbed nothing.

Combined with continuous Barman Cloud WAL archiving, umami now has point-in-time recovery. Nobody configured that. It was inherited by moving. PostgreSQL Backups on Kubernetes was an entire post about building per-database dumps, rotation, a Go exporter and two alert rules by hand, and that system is now the thing being decommissioned.

What's Working Now

• ✅ Umami runs on CloudNativePG. Cut over August 29, 5 min 54 s downtime, data verified identical
• ✅ Dashboard loads, admin logs in, six months of history intact
• ✅ The connection is encrypted: TLS 1.3, TLS_AES_256_GCM_SHA384
• ✅ Live traffic verified on the new path: 742 → 743 on CNPG while legacy stayed frozen at 742
• ✅ Backups inherited from bletchley-pg-daily plus WAL archiving, with no per-app wiring
• ✅ Reusable migration runner pod at apps/databases/_template/pg-migrate-pod.yaml
• ✅ Comparison harness proven sensitive and deterministic
• ✅ Rollback still available: the legacy database is untouched and secret/umami/db is still at version 1
• ⚠️ TLS encrypted but not enforced, running on Prisma's prefer default; the fix is a publicly-trusted certificate, later
• ⚠️ CNPG's internal CA expires October 31, 2026, and nothing is alerting on it

What's Next

Part 7 is cleanup, and only cleanup: retire the legacy StatefulSet and everything that exists only to serve it, meaning the backup CronJobs, the Go exporter, its two alert rules, the Traefik postgres TCP entrypoint, the IngressRouteTCP and the Longhorn PVC.

There is one ordering rule in there and it is not optional: suspend the legacy backup CronJobs before dropping the database. That backup system has a known, never-fixed bug where a run with zero databases writes its state files, uploads nothing, exits successfully, and publishes a healthy last-success timestamp for a backup containing no data. Dropping the last database reproduces it exactly.

Only once all of that is gone does the rename make sense: pg.vluwte.nl becomes postgres.vluwte.nl, which is a repoint between two IPs, and the new name gets a publicly-trusted Let's Encrypt certificate so sslmode=require can finally mean what it should. Those two go together and they come last. Neither is tidying up, so neither belongs in a decommissioning post.

← Previous: Secret Management Part 5

Questions or suggestions? Leave a comment below or reach out at igor@vluwte.nl.

Addendum: Every Manifest and Command

Everything below runs top to bottom. Passwords are placeholders. $LEGACY_PW and $UMAMI_PG_PW are shell variables held on the workstation, never written to a manifest.

A1: Migration runner pod

apps/databases/_template/pg-migrate-pod.yaml. Note runAsUser: 70: in postgres:17-alpine the postgres user is UID 70, not the 999 used by the Debian-based images.

apiVersion: v1
kind: Pod
metadata:
  name: pg-migrate
  namespace: databases
spec:
  restartPolicy: Never
  securityContext:
    runAsUser: 70
    runAsGroup: 70
    fsGroup: 70
  containers:
    - name: pg
      image: postgres:17-alpine
      command: ["sleep", "infinity"]
      volumeMounts:
        - name: work
          mountPath: /work
  volumes:
    - name: work
      emptyDir: {}

kubectl apply -f apps/databases/_template/pg-migrate-pod.yaml
kubectl -n databases wait --for=condition=Ready pod/pg-migrate --timeout=120s
kubectl -n databases exec pg-migrate -- id
kubectl -n databases exec pg-migrate -- pg_dump --version

kubectl -n databases exec pg-migrate -- \
  pg_isready -h postgres.postgres.svc.cluster.local -p 5432 -U umami -d umami
kubectl -n databases exec pg-migrate -- \
  pg_isready -h bletchley-pg-rw.databases.svc.cluster.local -p 5432 -U umami -d umami

A2: Pre-flight

kubectl -n openbao port-forward svc/openbao 8200:8200 &
export BAO_ADDR=http://127.0.0.1:8200
bao login -method=userpass username=a-igor

bao kv list secret/umami/
bao kv get -format=json secret/umami/db | jq '.data.data | keys'
bao kv metadata get secret/umami/db

LEGACY_PW=$(bao kv get -field=POSTGRES_PASSWORD secret/umami/db)

Size, row counts and accounts on the source:

SELECT pg_size_pretty(pg_database_size('umami')) AS db_size;

SELECT relname,
       (xpath('/row/c/text()',
              query_to_xml(format('SELECT count(*) AS c FROM public.%I', relname),
                           false, true, '')))[1]::text::bigint AS exact_rows
  FROM pg_stat_user_tables ORDER BY relname;

SELECT user_id, username, role, created_at FROM "user";
SELECT website_id, name, domain, created_at FROM website;

Use count(*) via query_to_xml, not n_live_tup. n_live_tup is a planner statistic and was stale here, reporting 0 rows for tables that demonstrably had rows.

Roles, ownership and extensions on the source:

SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolcanlogin;
SELECT nspname, pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname = 'public';
SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';
SELECT extname, extversion, pg_get_userbyid(extowner) AS owner FROM pg_extension;

Extension availability on the target, where trusted = t is the column that matters under enableSuperuserAccess: false:

SELECT name, default_version, installed_version, superuser, trusted
  FROM pg_available_extensions WHERE name = 'pgcrypto';

TLS state of the source, and the certificate the target actually presents:

SHOW ssl;

SELECT a.pid, a.usename, a.client_addr, s.ssl, s.version, s.cipher
  FROM pg_stat_activity a LEFT JOIN pg_stat_ssl s USING (pid)
 WHERE a.datname = 'umami';

kubectl -n databases get secret bletchley-pg-server \
  -o jsonpath='{.data.tls\.crt}' | base64 -d | \
  openssl x509 -noout -subject -issuer -dates -ext subjectAltName

kubectl -n databases get secret bletchley-pg-ca \
  -o jsonpath='{.data.ca\.crt}' | base64 -d | \
  openssl x509 -noout -subject -dates

ssh sulu.luwte.net 'getent hosts pg.vluwte.nl; nc -zv 10.0.140.101 5432'

A3: Credential in OpenBao

No policy or role changes were needed: the existing umami policy already grants read on secret/data/umami/*, and the umami Kubernetes auth role binds ESO's ServiceAccount, which is the same regardless of which namespace the SecretStore lives in.

UMAMI_PG_PW=$(openssl rand -hex 24)      # hex, NOT base64: goes into a postgresql:// URL

bao kv put secret/umami/db-credential \
  username=umami \
  password="$UMAMI_PG_PW"

bao kv get -format=json secret/umami/db-credential | jq '.data.data | keys'
bao kv metadata get secret/umami/db          # confirm still version 1, unmodified

A4: The four tenant manifests

apps/umami/secretstore-umami.yaml

apiVersion: external-secrets.io/v1
kind: SecretStore
metadata:
  name: umami
  namespace: databases
spec:
  provider:
    vault:
      server: http://openbao.openbao.svc:8200
      path: secret
      version: v2
      auth:
        kubernetes:
          mountPath: kubernetes
          role: umami

apps/umami/externalsecret-umami-db-owner.yaml

apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: umami-db-owner
  namespace: databases
spec:
  refreshInterval: 1h
  secretStoreRef:
    kind: SecretStore
    name: umami
  dataFrom:
    - extract:
        key: secret/data/umami/db-credential
  target:
    name: umami-db-owner
    creationPolicy: Owner
    template:
      type: kubernetes.io/basic-auth      # required: CNPG rejects Opaque
      metadata:
        labels:
          cnpg.io/reload: "true"          # required: without it CNPG never picks up changes
      data:
        username: "{{ .username }}"
        password: "{{ .password }}"

apps/umami/databaserole-umami.yaml

apiVersion: postgresql.cnpg.io/v1
kind: DatabaseRole
metadata:
  name: umami
  namespace: databases
spec:
  cluster:
    name: bletchley-pg
  name: umami
  ensure: present
  login: true
  superuser: false
  createdb: false
  createrole: false
  passwordSecret:
    name: umami-db-owner       # CNPG never generates a password; omit and the role gets NULL

apps/umami/database-umami.yaml

apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: umami
  namespace: databases
spec:
  cluster:
    name: bletchley-pg
  name: umami
  owner: umami
  encoding: UTF8
  databaseReclaimPolicy: retain      # deleting the CR must never drop the data

Apply and verify:

kubectl apply -f apps/umami/secretstore-umami.yaml
kubectl -n databases get secretstore umami

kubectl apply -f apps/umami/externalsecret-umami-db-owner.yaml
kubectl -n databases get externalsecret umami-db-owner
kubectl -n databases get secret umami-db-owner -o jsonpath='{.type}{"\n"}'

kubectl apply -f apps/umami/databaserole-umami.yaml
kubectl apply -f apps/umami/database-umami.yaml
kubectl -n databases get databaseroles.postgresql.cnpg.io,databases.postgresql.cnpg.io

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
  -c "SELECT current_user, current_database(), version();"

A5: The comparison scripts

kubectl -n databases exec -i pg-migrate -- sh -c 'cat > /tmp/pg-compare.sql' <<'SQL'
\pset pager off
\pset format unaligned
\pset fieldsep '|'
\pset tuples_only on

SELECT 'COL', table_name, ordinal_position, column_name, data_type, is_nullable
  FROM information_schema.columns WHERE table_schema = 'public'
 ORDER BY table_name, ordinal_position;

SELECT 'ROWS', relname,
       (xpath('/row/c/text()',
              query_to_xml(format('SELECT count(*) AS c FROM public.%I', relname),
                           false, true, '')))[1]::text::bigint
  FROM pg_stat_user_tables ORDER BY relname;

SELECT 'IDX', indexname, indexdef
  FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;

SELECT 'CON', conrelid::regclass::text, conname, pg_get_constraintdef(oid)
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace ORDER BY 2, 3;

SELECT 'SEQ', sequencename, last_value
  FROM pg_sequences WHERE schemaname = 'public' ORDER BY sequencename;

SELECT 'EXT', extname, extversion FROM pg_extension ORDER BY extname;
SQL

kubectl -n databases exec -i pg-migrate -- sh -c 'cat > /tmp/pg-content.sql' <<'SQL'
\pset pager off
\pset format unaligned
\pset fieldsep '|'
\pset tuples_only on

SELECT 'USER', count(*),
       md5(string_agg(user_id::text || ':' || username || ':' || role, ',' ORDER BY user_id))
  FROM "user";

SELECT 'SITE', count(*),
       md5(string_agg(website_id::text || ':' || domain, ',' ORDER BY website_id))
  FROM website;

SELECT 'EVENT', count(*), min(created_at), max(created_at) FROM website_event;
SELECT 'SESSION', count(*) FROM session;
SQL

Both files live in the pod's /tmp emptyDir. Deleting the pod loses them.

Running the comparison. This exact pair of commands is reused at every checkpoint:

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" \
  psql -h postgres.postgres.svc.cluster.local -U umami -d umami \
  -f /tmp/pg-compare.sql > /tmp/cmp-legacy.txt

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
  -f /tmp/pg-compare.sql > /tmp/cmp-cnpg.txt

diff -u /tmp/cmp-legacy.txt /tmp/cmp-cnpg.txt && echo "STRUCTURE + COUNTS IDENTICAL"
md5sum /tmp/cmp-legacy.txt /tmp/cmp-cnpg.txt

A6: Dry run

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" sh -c \
  'pg_dump -Fc -h postgres.postgres.svc.cluster.local -U umami -d umami -f /tmp/umami-dryrun.dump'

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" sh -c \
  'pg_restore -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
     --no-owner --no-privileges -j2 --verbose /tmp/umami-dryrun.dump' \
  2>&1 | tee /tmp/umami-dryrun-restore.log

grep -iE 'error|warning|must be owner|permission denied' /tmp/umami-dryrun-restore.log

Then run the A5 comparison, and the content script against both sides.

A7: Proving the harness works

Two different servers:

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" \
  psql -h postgres.postgres.svc.cluster.local -U umami -d umami -At -c \
  "SELECT inet_server_addr() || ' | ' || inet_server_port()
       || ' | ' || coalesce(current_setting('cluster_name', true), '(none)')
       || ' | ' || pg_postmaster_start_time();"

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami -At -c \
  "SELECT inet_server_addr() || ' | ' || inet_server_port()
       || ' | ' || coalesce(current_setting('cluster_name', true), '(none)')
       || ' | ' || pg_postmaster_start_time();"

Sentinel probe on the CNPG copy only, then re-compare, then clean up and re-compare again:

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami <<'SQL'
CREATE TABLE public._liveness_probe (id int PRIMARY KEY, note text);
INSERT INTO public._liveness_probe VALUES (1, 'dry-run sensitivity check');
SQL

# ... A5 comparison here: expect a diff and two different md5s ...

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
  -c 'DROP TABLE public._liveness_probe;'

# ... A5 comparison again: expect the original hash on both sides ...

A8: Cutover

# 1. Stop the writer, on sulu
sudo systemctl stop umami && systemctl is-active umami

# 2. Confirm zero connections remain on the legacy database
kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" \
  psql -h postgres.postgres.svc.cluster.local -U umami -d umami -c \
  "SELECT count(*), array_agg(DISTINCT client_addr) FROM pg_stat_activity
    WHERE datname='umami' AND pid <> pg_backend_pid();"

# 3. Empty the CNPG target and confirm it
kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami <<'SQL'
DROP SCHEMA public CASCADE;
CREATE SCHEMA public AUTHORIZATION umami;
SQL

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami -c '\dt'

# 4. Final dump and restore
TS=$(date +%Y%m%d-%H%M)

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" sh -c \
  "pg_dump -Fc -h postgres.postgres.svc.cluster.local -U umami -d umami -f /tmp/umami-final-$TS.dump"

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" sh -c \
  "pg_restore -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami \
     --no-owner --no-privileges -j2 --verbose /tmp/umami-final-$TS.dump" \
  2>&1 | tee /tmp/umami-final-restore-$TS.log

grep -iE 'error|permission denied' /tmp/umami-final-restore-$TS.log

# keep a copy off-cluster
kubectl -n databases cp pg-migrate:/tmp/umami-final-$TS.dump ./umami-final-$TS.dump

# 5. Comparison: must be identical, both scripts (see A5)

# 6. Repoint and start Umami, on sulu
#    /etc/systemd/system/umami.service (or its EnvironmentFile):
#    DATABASE_URL=postgresql://umami:<password>@pg.vluwte.nl:5432/umami?schema=public
#    Comment out the old line rather than deleting it; it is the rollback.
systemctl start umami && sleep 5 && systemctl status umami --no-pager
journalctl -u umami -n 50 --no-pager

A9: Post-cutover verification

Encryption:

kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami -c \
  "SELECT a.usename, a.client_addr, s.ssl, s.version, s.cipher
     FROM pg_stat_activity a LEFT JOIN pg_stat_ssl s USING (pid)
    WHERE a.datname = 'umami' AND a.backend_type = 'client backend';"

Liveness. Generate a real pageview first, from a browser without an ad-blocker and with Umami's own visit exclusion disabled:

echo "=== CNPG ===" && kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$UMAMI_PG_PW" \
  psql -h bletchley-pg-rw.databases.svc.cluster.local -U umami -d umami -At -c \
  "SELECT count(*) || ' | ' || max(created_at) FROM website_event;"

echo "=== LEGACY ===" && kubectl -n databases exec -i pg-migrate -- env PGPASSWORD="$LEGACY_PW" \
  psql -h postgres.postgres.svc.cluster.local -U umami -d umami -At -c \
  "SELECT count(*) || ' | ' || max(created_at) FROM website_event;"

Backup coverage, the morning after:

kubectl -n databases get backups.postgresql.cnpg.io \
  --sort-by=.metadata.creationTimestamp | tail -5

A10: Rollback

Until the legacy database is dropped in Part 7:

# on sulu
sudo systemctl stop umami
# restore the commented-out DATABASE_URL line
sudo systemctl start umami

The legacy database is untouched, and secret/umami/db is still at version 1, so the old credential still resolves. The cost is every pageview since the cutover, plus a return to an unencrypted connection, since the legacy server has ssl = off.

← Previous: Secret Management Part 5

Questions or suggestions? Leave a comment below or reach out at igor@vluwte.nl.