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 https://nixos.org/nixos/manual/index.html#module-services-postgres-upgrading
{ config, pkgs, ... }:
{
containers.temp-pg.config.services.postgresql = {
enable = true;
package = pkgs.postgresql_11;
## set a custom new dataDir
# dataDir = "/some/data/dir";
};
environment.systemPackages =
let newpg = config.containers.temp-pg.config.services.postgresql;
in [
(pkgs.writeScriptBin "upgrade-pg-cluster" ''
set -x
export OLDDATA="${config.services.postgresql.dataDir}"
export NEWDATA="${newpg.dataDir}"
export OLDBIN="${config.services.postgresql.package}/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.
./hardware-configuration.nix
];
to
imports =
[ # Include the results of the hardware scan.
./hardware-configuration.nix
# provide upgrade-pg-cluster
./postgres-upgrade.nix
];
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
gisdb
*failure*
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
containers.temp-pg.config.services.postgresql = {
enable = true;
package = pkgs.postgresql_11;
## set a custom new dataDir
# dataDir = "/some/data/dir";
};
to
containers.temp-pg.config.services.postgresql = rec {
enable = true;
package = pkgs.postgresql_11;
extraPlugins = with package.pkgs; [
postgis
];
## 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 containers.temp-pg.config.services.postgresql to pkgs.postgresql_11.withPackages (ps: [ps.postgis]) doesn’t work, as the result of withPackages lacks some attributes that are expected in config.services.postgresql.