Moving a server's own database

Every server keeps a small database of its own, separate from your customers'. Two things live in it: where that machine's mail has to go, and which DNS records it serves. That database runs on PostgreSQL and needs attention once every few years — exactly as described below.

This is not about your customers' databases. Those run on MariaDB or MySQL and move a different way; see Where a website is served.

Why this is an operation and not an update

Ubuntu ships exactly one PostgreSQL major release for the whole life of the release. PostgreSQL itself supports a major for five years. Work that through and there is a day on which your server runs a database release nobody is patching, while Ubuntu offers no other.

A major release also does not travel with an ordinary update, and that is deliberate: PostgreSQL writes its data in a per-major layout. An update installs the new major beside the old one and leaves your data where it is. Moving it is a decision, and it is yours.

What you see before you do anything

Ask what is there first. Looking changes nothing:

corectl node store status
corectl node store upgrade 19 --dry-run

The first line says which major this server runs now. The second counts every table and shows what would happen:

NODE STORE UPGRADE (dry run) — PostgreSQL 18 -> 19
  corecp_dns.domains                        3
  corecp_dns.records                      120
  corecp_mail.mailboxes                    41

Those counts are what you will compare against afterwards. They are also compared automatically — more on that below — but it helps to have seen them.

The move itself

Put the server in maintenance mode first, so the alarms are damped and a snapshot is taken beforehand:

corectl node maintenance-mode on --reason "PostgreSQL 18 -> 19"
corectl node store upgrade 19
corectl node maintenance-mode off

Between those two lines this happens, in this order: the new major is installed beside the old one, the mail and DNS services are stopped, the data is moved with PostgreSQL's own tooling, CoreCP's settings are written for the new major, every table is counted again, and the services start.

Allow a quarter of an hour. A server's own database is small — a few tens of megabytes on the staging fleet — but that machine's mail and DNS stand still while it moves.

The count is the approval

This is the part that separates it from "it seemed to work". The server record moves only when every table holds the same number of rows on both majors. Not "the cluster starts", not "the tool returned zero": a move that silently loses a table is indistinguishable from a good one until somebody's mail stops arriving, and that is exactly what nobody notices in between.

If the counts do not match, the command puts the server back on the old major, starts it, and names the table and both numbers.

When something goes wrong

The old database stays — stopped, complete, on disk. That is the way back and it costs a copy of a few tens of megabytes.

Clearing the old one away

Not straight away. Leave it for a few weeks; it is your way back and it costs almost nothing. After that:

pg_dropcluster <old-major> main

If you already know you do not want to keep it, --drop-old can travel with the move itself.

From the terminal

Everything above is terminal work; there is no screen for it. That is deliberate: it is an operation with a maintenance window around it, not a button you press by accident. What the panel does show is the result — the server's own page names the major its own database runs.

The full runbook, including what you want ready beforehand, is in docs/operations/postgresql-hoofdversie.md (Dutch).