NixOS PostgreSQL Major Version Upgrade

Posted on 2025-08-24

PostgreSQL major version upgrades is something that NixOS currently doesn’t handle for you. This is my process, documented mostly so that I can remember it next time I want to upgrade.

Configuration

I use the standard NixOS module for Postgres. My main config looks like this:

{ lib, pkgs, ... }: {
	services.postgresql = {
		enable = true;
		package = pkgs.postgresql_17;
		initdbArgs = [
			"--encoding=UTF8"
			"--no-locale"
		];
		ensureUsers = [{
			name = "kevincox";
		} {
			name = "root";
		}];
	};

	systemd.services.postgresql = {
		postStart = lib.mkAfter ''
			$PSQL -tAc "ALTER ROLE kevincox with SUPERUSER"
			$PSQL -tAc "ALTER ROLE root with SUPERUSER"
		'';
		serviceConfig = {
			TimeoutStartSec = "30min";
		};
	};
}

Notable features:

I pin the version of Postgres, to ensure that I upgrade when I want to.
I give myself and root superuser.

Preparation

Update your NixOS config but don’t deploy it.
Build the config to make sure everything is ready to go.

Upgrading

Log in as the postgres user. (sudo -su postgres)
Define the old version old=16
Get the old and new versions:
```
cd /var/lib/postgresql/
pg_old=$(nix-build --no-out-link -A postgresql_${old:?} '<nixpkgs>')
pg_new=$(nix-build --no-out-link -A postgresql_$((old+1)) '<nixpkgs>')
```
Note
If you are using extensions you will need them in $pg_old and $pg_new. If you are using the NixOS module you can make a package with nix-build "<nixpkgs/nixos>" -A config.services.postgresql.finalPackage. If you are using colmena you can do colmena eval -E "{nodes, ...}: nodes.mynode.config.services.postgresql.finalPackage".
Initialize the new data directory. Be sure to use the arguments from your config!
```
$pg_new/bin/initdb --encoding=UTF8 --no-locale $((old+1))
```
Copy the configuration (this is really only needed if you have extensions that need to be loaded, the main value we care about is shared_preload_libraries. But copying the whole file is easiest and the NixOS module will overwrite it anyways when starting the new version.).
```
cp -v {${old:?},$((old+1))}/postgresql.conf
```

Run the check

$pg_new/bin/pg_upgrade \
	--old-bindir=$pg_old/bin
	--new-bindir=$pg_new/bin \
	--old-datadir=/var/lib/postgresql/${old:?} \
	--new-datadir=/var/lib/postgresql/$((old+1)) \
	-j16 \
	--clone \
	--check

Stop the old Postgres. systemctl stop postgresql

Warning
This is the point where we copy the data. If the old Postgres starts running again it will write new data to the old directory that will not be migrated. This will result in data loss. If that happens remove the new state directory and start again.
Run the migration. This is the same command as the check without --check.
Start the new PostgreSQL server. (This is typically done by activating the new configuration such as sudo nixos-rebuild switch.)

At this point the new version is running. Note that rolling back will cause data loss as new changes are being made only to the new data directory.

You may wish to rename the old directory so that if you do accidentally roll back it is more likely that things fail rather than losing data. But if you have insecure apps that let the first user be admin that may be a bad idea. Maybe you could do something like put a file there or a directory owned by root? But I haven’t tested any of these things.

Cleanup

As suggested by pg_migrate run vacuumdb --all --analyze-in-stages.
At some point in the future once you are confident that the new DB is running fine (a few weeks doesn’t hurt) you can remove the old data.
```
cd /var/lib/postgresql/
./delete_old_cluster.sh
rm -v delete_old_cluster.sh
```