For semi-automated PostgreSQL upgrade: Can the wrapped PostgreSQL be accessed from nix expressions?

The NixOS manual has a handy nix expression and recipe (permalink) for upgrading PostgreSQL data.

I’ve put the nix expression into /etc/nixos/postgres-upgrade.nix and I’ve put

{ config, pkgs, ... }:


  # ...


around it, so that I can import it in /etc/nixos/configuration.nix. I’ve also changed the new PostgreSQL version to 11, so my /etc/nixos/postgres-upgrade.nix is now

# See
{ config, pkgs, ... }:

{ = {
    enable = true;
    package = pkgs.postgresql_11;
    ## set a custom new dataDir
    # dataDir = "/some/data/dir";
  environment.systemPackages =
    let newpg =;
    in [
      (pkgs.writeScriptBin "upgrade-pg-cluster" ''
        set -x
        export OLDDATA="${}"
        export NEWDATA="${newpg.dataDir}"
        export OLDBIN="${}/bin"
        export NEWBIN="${newpg.package}/bin"

        install -d -m 0700 -o postgres -g postgres "$NEWDATA"
        cd "$NEWDATA"
        sudo -u postgres $NEWBIN/initdb -D "$NEWDATA"

        systemctl stop postgresql    # old one

        sudo -u postgres $NEWBIN/pg_upgrade \
          --old-datadir "$OLDDATA" --new-datadir "$NEWDATA" \
          --old-bindir $OLDBIN --new-bindir $NEWBIN \

In /etc/nixos/configuration.nix, I’ve changed

  imports =
    [ # Include the results of the hardware scan.


  imports =
    [ # Include the results of the hardware scan.

      # provide upgrade-pg-cluster

Then I ran nixos-rebuild boot and rebooted into the resulting system.

Then I tried

sudo upgrade-pg-cluster

but that failed on the sudo -u postgres $NEWBIN/pg_upgrade ... step:

+ export OLDDATA=/var/lib/postgresql/9.6
+ OLDDATA=/var/lib/postgresql/9.6
+ export NEWDATA=/var/lib/postgresql/11.1
+ NEWDATA=/var/lib/postgresql/11.1
+ export OLDBIN=/nix/store/rx5cv6mycx7nl9djyqrzziaip736smcv-postgresql-9.6.17/bin
+ OLDBIN=/nix/store/rx5cv6mycx7nl9djyqrzziaip736smcv-postgresql-9.6.17/bin
+ export NEWBIN=/nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin
+ NEWBIN=/nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin
+ install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/11.1
+ cd /var/lib/postgresql/11.1
+ sudo -u postgres /nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin/initdb -D /var/lib/postgresql/11.1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "de_CH.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "german".

Data page checksums are disabled.

initdb: directory "/var/lib/postgresql/11.1" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/postgresql/11.1" or run initdb
with an argument other than "/var/lib/postgresql/11.1".
+ systemctl stop postgresql
+ sudo -u postgres /nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin/pg_upgrade --old-datadir /var/lib/postgresql/9.6 --new-datadir /var/lib/postgresql/11.1 --old-bindir /nix/store/rx5cv6mycx7nl9djyqrzziaip736smcv-postgresql-9.6.17/bin --new-bindir /nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin
Performing Consistency Checks
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Creating dump of global objects                             ok
Creating dump of database schemas

Consult the last few lines of "pg_upgrade_dump_30947.log" for
the probable cause of the failure.
Failure, exiting

/var/lib/postgresql/11.1/pg_upgrade_dump_30947.log contains

command: "/nix/store/54yswr8q7a9cny5yjq2rr7955rw92k5i-postgresql-11.7/bin/pg_dump" --host /var/lib/postgresql/11.1 --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_30947.custom" 'dbname=gisdb' >> "pg_upgrade_dump_30947.log" 2>&1
pg_dump: [archiver (db)] query failed: ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory
pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions, NULL as attmissingval FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '32495'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attnum

The error could not access file "$libdir/postgis-2.5": No such file or directory isn’t very surprising, as gisdb uses PostGIS, but no plugins were specified for the new PostgreSQL version. But even changing = {
    enable = true;
    package = pkgs.postgresql_11;
    ## set a custom new dataDir
    # dataDir = "/some/data/dir";

to = rec {
    enable = true;
    package = pkgs.postgresql_11;
    extraPlugins = with package.pkgs; [
    ## set a custom new dataDir
    # dataDir = "/some/data/dir";

followed by sudo nixos-rebuild switch and then repeating sudo upgrade-pg-cluster led to the same result. I guess this is because $NEWBIN is set to the unwrapped PostgreSQL package, instead of the one wrapped such that extraPlugins are available to it.

Is there a way to access that wrapped PostgreSQL (postgresql-and-plugins) from nix expressions?

Simply setting to pkgs.postgresql_11.withPackages (ps: [ps.postgis]) doesn’t work, as the result of withPackages lacks some attributes that are expected in

You could integrate some upgrade system in:

Write the info needed for a upgrade in a file, and check for changes
echo >
diff file ||
< do upgrade >
mv file

That’s a nice idea to make this more automatic (Thanks!), but if I’m not mistaken, we’d first have to solve the problem at hand by answering my question

You can put any postgresql-configuration inside the container-configuration.
The container makes sure the new postgresql installation is available.

1 Like