Upgrading to a new Postgresql major version on NixOS

November 20, 2025 NixOS PostgreSQL

I’ve seen a few different guides on how to upgrade to a new major PostgreSQL version but couldn’t make it work as I’m also using extensions for some of my databases. Luckily this was relatively easy due to NixOS declarative nature!

My method is just an modified version of Kevin Cox’s guide. A few other sources of inspiration was the NixOS wiki entry on upgrading (which uses flakes which I still don’t understand) and reading the pg_upgrade manual.

Preflight check

First up, run a check before doing anything more, this is done with the old PostgreSQL version running:

sudo -su postgres

cd /var/lib/postgresql/

old=15
new=17

pg_old=$(nix-build --no-out-link -E "with import <nixpkgs> {}; \
         postgresql_${old:?}.withPackages (p: [ p.pgvector p.vectorchord ])")
pg_new=$(nix-build --no-out-link -E "with import <nixpkgs> {}; \
         postgresql_${new:?}.withPackages (p: [ p.pgvector p.vectorchord ])")

# Update shared_preload_libraries to your needs!
$pg_new/bin/pg_upgrade \
        --socketdir=/var/run/postgresql \
        --old-bindir=$pg_old/bin \
        --new-bindir=$pg_new/bin \
        --old-datadir=/var/lib/postgresql/${old:?} \
        --new-datadir=/var/lib/postgresql/${new:?} \
        --old-options "-c shared_preload_libraries='vchord.so'" \
        --new-options "-c shared_preload_libraries='vchord.so'" \
        --check

$pg_old and $pg_new needs to be representative for your current and your future PostgreSQL installations. In my case I’m also including pgvector and vectorchord in my installation¹, but if you try to upgrade to a new version without the necessary extensions this check will fail.

If the check went ok then we can prepare for upgrading.

Preparing for the upgrade

In this example I’m using postgresql_15 and want to upgrade to the latest packaged version (which currently is 17). Below is a very short slice of my configuration:

{ pkgs, ... }:
{
  services.postgresql.enable = true;
  services.postgresql.package = pkgs.postgresql;
  # services.postgresql.package = pkgs.postgresql_15;
  # ...
}

Change services.postgresql.package to the version you want to upgrade to, and then build (don’t switch yet!)

nixos-rebuild build

Now, stop the database, prepare the socket directory under /var/run:

systemctl stop postgresql.service
mkdir /var/run/postgresql
chown postgres:postgres /var/run/postgresql

Upgrade the database

Run initdb and migrate your database:

sudo -su postgres

cd /var/lib/postgresql/

old=15
new=17

pg_old=$(nix-build --no-out-link -E "with import <nixpkgs> {}; \
         postgresql_${old:?}.withPackages (p: [ p.pgvector p.vectorchord ])")
pg_new=$(nix-build --no-out-link -E "with import <nixpkgs> {}; \
         postgresql_${new:?}.withPackages (p: [ p.pgvector p.vectorchord ])")

$pg_new/bin/initdb -D /var/lib/postgresql/17 || exit 1

# Remember to update the shared_preload_libraries if needed!
$pg_new/bin/pg_upgrade \
        --socketdir=/var/run/postgresql \
        --old-bindir=$pg_old/bin \
        --new-bindir=$pg_new/bin \
        --old-datadir=/var/lib/postgresql/${old:?} \
        --new-datadir=/var/lib/postgresql/${new:?} \
        --old-options "-c shared_preload_libraries='vchord.so'" \
        --new-options "-c shared_preload_libraries='vchord.so'" \

If the migration was successful, switch over to your new system configuration:

nixos-rebuild switch

Check if PostgreSQL if running:

systemctl status postgresql.service

Update extensions

After upgrading, I noticed that I had two new files in /var/lib/postgresql, one called update_extensions.sql and one called delete_old_cluster.sh.

I was interested in update_extensions.sql, which contained:

\c immich
ALTER EXTENSION "earthdistance" UPDATE;

That makes sense, as my Immich databases makes use of several extensions!

To apply:

sudo -u postgres psql -f /var/lib/postgresql/update_extensions.sql

Conclusion

Now I’m just waiting for NixOS 25.11 which should include PostgreSQL 18, so I can start experimenting with it’s new OIDC support!

Immich requires them to function, no other ai here, I promise! ↩︎

monotux.tech