Using the upgrade script from the wiki, I’m getting these errors during the update:
(For reference, the database serves Akkoma, Matrix-synapse, Immich, Miniflux and Nextcloud)
pg_restore: connecting to new database "immich"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4571; 0 0 pg_largeobject pg_largeobject (no owner)
pg_restore: error: could not execute query: ERROR: permission denied for table pg_largeobject
Command was:
-- For binary upgrade, preserve pg_largeobject and index relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('2613'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('989054'::pg_catalog.oid);
TRUNCATE pg_catalog.pg_largeobject;
-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '726', relminmxid = '1'
WHERE oid = 2613;
UPDATE pg_catalog.pg_class
SET relfrozenxid = '0', relminmxid = '0'
WHERE oid = 2683;
Here is my complete postgresql.nix:
{
config,
lib,
pkgs,
...
}:
{
services.postgresql = {
enable = true;
package = pkgs.postgresql_15;
};
# From the NixOS manual
environment.systemPackages = [
(
let
# XXX specify the postgresql package you'd like to upgrade to.
# Do not forget to list the extensions you need.
newPostgres = pkgs.postgresql_17.withPackages (pp: [
pp.vectorchord
pp.pgvector
]);
cfg = config.services.postgresql;
in
pkgs.writeScriptBin "upgrade-pg-cluster" ''
set -eux
# XXX it's perhaps advisable to stop all services that depend on postgresql
systemctl stop postgresql
export NEWDATA="/var/lib/postgresql/${newPostgres.psqlSchema}"
export NEWBIN="${newPostgres}/bin"
export OLDDATA="${cfg.dataDir}"
export OLDBIN="${cfg.finalPackage}/bin"
install -d -m 0700 -o postgres -g postgres "$NEWDATA"
cd "$NEWDATA"
sudo -u postgres "$NEWBIN/initdb" -D "$NEWDATA" ${lib.escapeShellArgs cfg.initdbArgs}
sudo -u postgres "$NEWBIN/pg_upgrade" \
--old-datadir "$OLDDATA" --new-datadir "$NEWDATA" \
--old-bindir "$OLDBIN" --new-bindir "$NEWBIN" \
--new-options "-c shared_preload_libraries='vchord.so'" \
"$@"
''
)
];
}
The only change I had to make to get this far was adding the --new-options....vchord.so to ensure that the new postgres instance would see the vectorchord extension. Otherwise it failed considerably earlier.
Here’s the dump of the upgrade-pg-cluster script and the errors logged:
[root@homeserver:~]# upgrade-pg-cluster --jobs 4 --link
++ systemctl stop postgresql
++ export NEWDATA=/var/lib/postgresql/17
++ NEWDATA=/var/lib/postgresql/17
++ export NEWBIN=/nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin
++ NEWBIN=/nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin
++ export OLDDATA=/var/lib/postgresql/15
++ OLDDATA=/var/lib/postgresql/15
++ export OLDBIN=/nix/store/fzvs1p7pkqjwvmc2i9kdq44ynjxwaa5r-postgresql-and-plugins-15.14/bin
++ OLDBIN=/nix/store/fzvs1p7pkqjwvmc2i9kdq44ynjxwaa5r-postgresql-and-plugins-15.14/bin
++ install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/17
++ cd /var/lib/postgresql/17
++ sudo -u postgres /nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin/initdb -D /var/lib/postgresql/17
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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin/pg_ctl -D /var/lib/postgresql/17 -l logfile start
++ sudo -u postgres /nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin/pg_upgrade --old-datadir /var/lib/postgresql/15 --new-datadir /var/lib/postgresql/17 --old-bindir /nix/store/fzvs1p7pkqjwvmc2i9kdq44ynjxwaa5r-postgresql-and-plugins-15.14/bin --new-bindir /nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin --new-options '-c shared_preload_libraries='\''vchord.so'\''' --jobs 4 --link
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 contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for not-null constraint inconsistencies ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
matrix-synapse
*failure*
Consult the last few lines of "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_5383909.log" for
the probable cause of the failure.
Failure, exiting
*failure*
Consult the last few lines of "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_3371482.log" for
the probable cause of the failure.
Failure, exiting
*failure*
Consult the last few lines of "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_16384.log" for
the probable cause of the failure.
Failure, exiting
*failure*
Consult the last few lines of "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_13780.log" for
the probable cause of the failure.
Failure, exiting
child process exited abnormally: status 256
Failure, exiting
[root@homeserver:~]# cat /var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_5383909.log
command: "/nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin/pg_dump" --host /var/lib/postgresql/17 --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/dump/pg_upgrade_dump_5383909.custom" 'dbname=immich' >> "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_5383909.log" 2>&1
command: "/nix/store/gc2as95wdv7923bkcg4w4k6kl2c7vmx7-postgresql-and-plugins-17.6/bin/pg_restore" --host /var/lib/postgresql/17 --port 50432 --username postgres --create --exit-on-error --verbose --transaction-size=250 --dbname template1 "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/dump/pg_upgrade_dump_5383909.custom" >> "/var/lib/postgresql/17/pg_upgrade_output.d/20251107T165144.178/log/pg_upgrade_dump_5383909.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "immich"
pg_restore: connecting to new database "immich"
pg_restore: creating DATABASE PROPERTIES "immich"
pg_restore: connecting to new database "immich"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4571; 0 0 pg_largeobject pg_largeobject (no owner)
pg_restore: error: could not execute query: ERROR: permission denied for table pg_largeobject
Command was:
-- For binary upgrade, preserve pg_largeobject and index relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('2613'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('989054'::pg_catalog.oid);
TRUNCATE pg_catalog.pg_largeobject;
-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '726', relminmxid = '1'
WHERE oid = 2613;
UPDATE pg_catalog.pg_class
SET relfrozenxid = '0', relminmxid = '0'
WHERE oid = 2683;
Any ideas or tips? Thanks!
But, in any case, I have Postgres 17 functioning correctly now.