postgresql: not possible to upgrade from postgres 16 to 17 (new default on NixOS 25.11)

[skiinganchor]

Nixpkgs version

• Stable (25.11)

Describe the bug

I had on NixOS 25.05 a working postgres service (that used version 16). After the upgrade to NixOS 25.11 it by default spins up PostgreSQL 17. As expected it does not trigger the upgrade and migrations. I followed the documentation for that and did:

$ sudo systemctl stop postgresql
$ ps aux | grep postgres
> root       15357  0.0  0.0   6860  2820 pts/2    S+   16:25   0:00 grep postgres
sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_16.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/16 -D /var/lib/postgresql/17

This leads to the following output:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

*failure*
Consult the last few lines of "/var/lib/postgresql/17/pg_upgrade_output.d/20260102T162434.628/log/pg_upgrade_server.log" for
the probable cause of the failure.

connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?


could not connect to source postmaster started with the command:
"/nix/store/a067iwfgwaabg6wkxl9a80mqdabaffsi-postgresql-16.11/bin/pg_ctl" -w -l "/var/lib/postgresql/17/pg_upgrade_output.d/20260102T162434.628/log/pg_upgrade_server.log" -D "/var/lib/postgresql/16" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

Then I checked the error logs:

$ sudo cat /var/lib/postgresql/17/pg_upgrade_output.d/20260102T162434.628/log/pg_upgrade_server.log
-----------------------------------------------------------------
  pg_upgrade run on Fri Jan  2 16:24:34 2026
-----------------------------------------------------------------

command: "/nix/store/a067iwfgwaabg6wkxl9a80mqdabaffsi-postgresql-16.11/bin/pg_ctl" -w -l "/var/lib/postgresql/17/pg_upgrade_output.d/20260102T162434.628/log/pg_upgrade_server.log" -D "/var/lib/postgresql/16" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start >> "/var/lib/postgresql/17/pg_upgrade_output.d/20260102T162434.628/log/pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: could not access the server configuration file "/var/lib/postgresql/16/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I can see that /var/lib/postgresql/16/postgresql.conf is a symbolic link to a file that does not exist anymore (I believe was not removed properly by NixOS GC):

$ sudo ls -lha /var/lib/postgresql/16/
total 120K
drwxr-x--- 20 postgres postgres 4.0K Dec 17 06:39 .
drwxr-x---  4 postgres postgres 4.0K Dec 17 06:39 ..
drwx------  6 postgres postgres 4.0K Feb 21  2025 base
-rw-------  1 postgres postgres  501 Feb 21  2025 .bash_history
drwxr-xr-x  3 postgres postgres 4.0K Feb 16  2025 .cache
drwx------  2 postgres postgres 4.0K Dec 17 06:05 global
-rw-------  1 postgres postgres   20 Feb 21  2025 .lesshst
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_commit_ts
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_dynshmem
-rw-------  1 postgres postgres 5.6K Feb  4  2025 pg_hba.conf
-rw-------  1 postgres postgres 2.6K Feb  4  2025 pg_ident.conf
drwx------  4 postgres postgres 4.0K Dec 17 06:39 pg_logical
drwx------  4 postgres postgres 4.0K Feb  4  2025 pg_multixact
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_notify
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_replslot
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_serial
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_snapshots
drwx------  2 postgres postgres 4.0K Dec 17 06:39 pg_stat
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_stat_tmp
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_subtrans
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_tblspc
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_twophase
-rw-------  1 postgres postgres    3 Feb  4  2025 PG_VERSION
drwx------  3 postgres postgres 4.0K Feb 21  2025 pg_wal
drwx------  2 postgres postgres 4.0K Feb  4  2025 pg_xact
-rw-------  1 postgres postgres   88 Feb  4  2025 postgresql.auto.conf
lrwxrwxrwx  1 postgres postgres   75 Dec 17 06:05 postgresql.conf -> /nix/store/kccc8i4svic5rbidngdqwpzrrzsnz0ii-postgresql.conf/postgresql.conf
-rw-r-----  1 postgres postgres   86 Dec 17 06:05 postmaster.opts
-rw-------  1 postgres postgres  268 Feb 21  2025 .psql_history
$ readlink -f /var/lib/postgresql/16/postgresql.conf
> none 

I believe something is really wrong either with the service and/or the documentation.

Steps to reproduce

1. Have a running NixOS 25.05 before upgrade with:

services.postgresql.enable = true; # it will have postgresql 16

1. Create some database RANDOM_NAME.
2. Upgrade NixOS to 25.11.
3. You will have postgres 17:

sudo -u postgres psql -V
> psql (PostgreSQL) 17.7

1. Stop the new postgresql and run the upgrade:

$ sudo systemctl stop postgresql
$ ps aux | grep postgres
> root       15357  0.0  0.0   6860  2820 pts/2    S+   16:25   0:00 grep postgres
sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_16.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/16 -D /var/lib/postgresql/17

It should give the error mentioned in the description.

Expected behaviour

Successful execution of pg_upgrade.

Relevant log output

See on Describe the bug.

Additional context

No response

System metadata

• system: "x86_64-linux"
• host os: Linux 6.12.63, NixOS, 25.11 (Xantusia), 25.11.20251229.89dbf01
• multi-user?: yes
• sandbox: yes
• version: nix-env (Nix) 2.31.2
• channels(root): "nixos-24.11"
• nixpkgs: /nix/store/rv4z0vd7v0pkadinxd1c1xmd4b850ldr-source

Notify maintainers

---

Note for maintainers: Please tag this issue in your pull request description. (i.e. Resolves #ISSUE.)

I assert that this issue is relevant for Nixpkgs

• I assert that this is a bug and not a support request.
• I assert that this is not a duplicate of an existing issue.
• I assert that I have read the NixOS Code of Conduct and agree to abide by it.

Is this issue important to you?

Add a 👍 reaction to issues you find important.

[bstanderline]

It looks like GC has deleted the configuration file that PSQL16 was using. You may be able to manually set your system to use PSQL16 by specifiying services.postgresql.package. Once you've got both 16 and 17, you can use pg_upgrade to upgrade your cluster.

How did you upgrade from 25.05 to 25.11?

[skiinganchor]

@bstanderline that is correct, I did you upgrade from 25.05 to 25.11.

When I set the postgres back to 16, everything is working as before. I just cannot upgrade when I go back to 17. Should I have is somehow both working as the same time? This is not exactly what is saying in the docs.

[bstanderline]

Now you've got 16 working again, your postgresql.conf file should now point to a valid file. You can swap back to 17 and, without running garbage collection, run pg_upgrade

My question was: What was your process of upgrading your system from 25.05 -> 25.11?

[skiinganchor]

@bstanderline my upgrade process was standard:

1. read release notes to see renamed options
2. point inputs to 25.11
3. flake update
4. nixos rebuild switch

as for postgres, I was not point to a specific version package, but since the upgrade did not work now I am pointing at postgres_16.

did you do the upgrade for nixos and postgres as well? did it work?

[bstanderline]

@skiinganchor the reason I'm asking is that the PSQL version, if you don't specify a different package, is set through system.stateVersion so should remain static unless you're not setting system.stateVersion in your config

nixpkgs/nixos/modules/services/databases/postgresql.nix

Line 660 in 9af67fc

if versionAtLeast config.system.stateVersion "25.11" then

I haven't had any severe issues with upgrading PSQL. The upgrade process can be a bit fiddly if the new version has already initialised some databases and users.

Have a look through this section of documentation to see if it helps (the NixOS manual is directly linked to the package documentation in nixpkgs so is more up to date):
https://nixos.org/manual/nixos/stable/#module-services-postgres-upgrading

[staticdev]

@bstanderline I did the same as above not to mention on my upgrade steps, I changed stateVersion together with my flake inputs to 25.11.

[bstanderline]

@staticdev I'm not sure if your issue is the same as op's but it might be worth consulting this section on the stateVersion setting:
https://nixos.org/manual/nixos/stable/options#opt-system.stateVersion
namely:

Do not change this value unless you have manually inspected all the changes it would make to your configuration, and migrated your data accordingly.

Which means I think this behavior is expected - the user needs to migrate their data if they're going to change system.stateVersion and this shouldn't be handled by Nix.

[skiinganchor]

@bstanderline you are correct, and I did not expect Nix to handle the migration of data. I created this issue because I followed the migration documentation instructions and it does not work, so I had to pin my postgres version to an older version (16) since I was not able to make it work on the new default version of NixOS 25.11. I believe something else is missing there.

[bstanderline]

@skiinganchor I've just run a migration from 16 to 17 which was successful. My steps were:

1. Ensure the 17 database cluster was empty. This involved an initdb as some data had already been written to the new database cluster
2. Search the nix store for the psql 16 executables with ls -l /nix/store | grep postgresql
3. Run pg_upgrade as the postgres user, specifying the old binaries located in step 2, old database and new database.

The only difference between the steps in the documentation and mine were that I didn't run nix build to retrieve the binaries, I used the old ones that hadn't yet been cleaned up by GC.

I think I agree with your point in the initial issue description that the problem is nix garbage collection. It looks like its deleting the old configuration file before you had chance to run the migration so the config file was unavailable. I think this is the desired configuration for the GC because your system configuration no longer used psql 16 so it deleted the config file (along with everything else that wasn't needed).

Do you have anything that is running the garbage collection on nixos-rebuild or on a timer or something? GC shouldn't run unless requested so switching from psql 16 to 17 now and performing the upgrade should keep the binaries and configuration files available in the nix store to be referenced during the migration and the migration should be successful

[skiinganchor]

If someone land in this issue, my steps to solve were:

1. Backup

2. Switch to the newer version using services.postgresql.package

3. Run:

   sudo systemctl stop postgresql
   sudo -u postgres -i
   cd /var/lib/postgresql
   pg_upgrade \
    -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_16.out)/bin" \
    -B /run/current-system/sw/bin \
    -d /var/lib/postgresql/16 \
    -D /var/lib/postgresql/17 \
    -o "-c unix_socket_directories=/run/postgresql" \
    -O "-c unix_socket_directories=/run/postgresql"
   exit
   sudo systemctl start postgresql

   You should see upgrade complete.

4. Check all data is there

5. Run: sudo -u postgres /run/current-system/sw/bin/vacuumdb --all --analyze-in-stages

6. Cleanup old data:

   sudo -u postgres -i
   cd /var/lib/postgresql
   ./delete_old_cluster.sh