Error running pg_upgrade command [PGroonga]

[xd003]

I am facing the following error when upgrading from PGroonga 16.7 (groonga/pgroonga:latest-debian-16) to PGroonga debian 17 using the One shot mode. I would like to add that PGroonga is just an extension of postgres.
Command - docker run --name pgauto -it --mount type=bind,source=/opt/containers/postgres,target=/var/lib/postgresql/data -e POSTGRES_USER=USER -e POSTGRES_PASSWORD=PASS -e POSTGRES_DB=demo -e PGAUTO_ONESHOT=yes pgautoupgrade/pgautoupgrade:17-bookworm

Output

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data
************************************
Creating upgrade lock file at /var/lib/postgresql/data/upgrade_in_progress.lock
*******************************************************************************************
Performing PG upgrade on version 16 database files.  Upgrading to version 17
*******************************************************************************************
----------------------------------------------------------------------
Checking for left over artifacts from a failed previous autoupgrade...
----------------------------------------------------------------------
-------------------------------------------------------------------------------
No artifacts found from a failed previous autoupgrade.  Continuing the process.
-------------------------------------------------------------------------------
---------------------------------------
Creating OLD temporary directory /var/lib/postgresql/data/old
---------------------------------------
--------------------------------------------
Creating OLD temporary directory is complete
--------------------------------------------
-------------------------------------------------------
Moving existing data files into OLD temporary directory
-------------------------------------------------------
renamed '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
renamed '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
mv: cannot move '/var/lib/postgresql/data/old' to a subdirectory of itself, '/var/lib/postgresql/data/old/old'
renamed '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
renamed '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
renamed '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
renamed '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
renamed '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
renamed '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
renamed '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
renamed '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
renamed '/var/lib/postgresql/data/pgroonga.log' -> '/var/lib/postgresql/data/old/pgroonga.log'
renamed '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
renamed '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
renamed '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
renamed '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
renamed '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
renamed '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
renamed '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
renamed '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
renamed '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
renamed '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
renamed '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
renamed '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
renamed '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
renamed '/var/lib/postgresql/data/postmaster.pid' -> '/var/lib/postgresql/data/old/postmaster.pid'
renamed '/var/lib/postgresql/data/upgrade_in_progress.lock' -> '/var/lib/postgresql/data/old/upgrade_in_progress.lock'
-------------------------------------------------------------------
Moving existing data files into OLD temporary directory is complete
-------------------------------------------------------------------
---------------------------------------
Creating NEW temporary directory /var/lib/postgresql/data/new
---------------------------------------
--------------------------------------------
Creating NEW temporary directory is complete
--------------------------------------------
-----------------------------------------------------
Changing permissions of temporary directories to 0700
-----------------------------------------------------
---------------------------------------------------------
Changing permissions of temporary directories is complete
---------------------------------------------------------
-------------------------------------------------
Remove postmaster.pid file from PG data directory
-------------------------------------------------
------------------------------------
Determining our own initdb arguments
------------------------------------
2025-02-21 12:11:46.993 UTC [36] LOG:  database system was interrupted; last known up at 2025-02-21 12:10:01 UTC
2025-02-21 12:11:47.004 UTC [36] LOG:  database system was not properly shut down; automatic recovery in progress
2025-02-21 12:11:47.008 UTC [36] LOG:  redo starts at 0/56FB490
2025-02-21 12:11:47.042 UTC [36] LOG:  invalid record length at 0/5798BC0: expected at least 24, got 0
2025-02-21 12:11:47.042 UTC [36] LOG:  redo done at 0/5798B88 system usage: CPU: user: 0.00 s, system: 0.02 s, elapsed: 0.03 s
2025-02-21 12:11:47.046 UTC [36] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-02-21 12:11:47.110 UTC [36] LOG:  checkpoint complete: wrote 3475 buffers (21.2%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.041 s, sync=0.018 s, total=0.065 s; sync files=27, longest=0.017 s, average=0.001 s; distance=629 kB, estimate=629 kB; lsn=0/5798BC0, redo lsn=0/5798BC0
2025-02-21 12:11:47.122 UTC [36] LOG:  checkpoint starting: shutdown immediate
2025-02-21 12:11:47.129 UTC [36] LOG:  checkpoint complete: wrote 8 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.002 s, total=0.009 s; sync files=4, longest=0.002 s, average=0.001 s; distance=0 kB, estimate=566 kB; lsn=0/5798C38, redo lsn=0/5798C38
---------------------------------------------------------------
The initdb arguments we determined are: --encoding=UTF8
---------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Old database using collation settings: '--encoding=UTF8'.  Initialising new database with those settings too
--------------------------------------------------------------------------------------------------------------------
Initialising PostgreSQL 17 data directory
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.utf8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data/new ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
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:

    /usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/data/new/ -l logfile start

------------------------------------
New database initialisation complete
------------------------------------
---------------------------------------
Running pg_upgrade command, from /var/lib/postgresql/data
---------------------------------------
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
Creating dump of global objects                               ok
Creating dump of database schemas
  demo
*failure*

Consult the last few lines of "/var/lib/postgresql/data/new/pg_upgrade_output.d/20250221T121147.931/log/pg_upgrade_dump_16384.log" for
the probable cause of the failure.
Failure, exiting

Above mentioned log file

command: "/usr/lib/postgresql/17/bin/pg_dump" --host /var/run/postgresql --port 50432 --username USER --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="/var/lib/postgresql/data/new/pg_upgrade_output.d/20250221T121147.931/dump/pg_upgrade_dump_16384.custom" 'dbname=demo' >> "/var/lib/postgresql/data/new/pg_upgrade_output.d/20250221T121147.931/log/pg_upgrade_dump_16384.log" 2>&1
pg_dump: error: query failed: ERROR:  could not access file "$libdir/pgroonga": No such file or directory
pg_dump: detail: Query was: SELECT t.tableoid, t.oid, i.indrelid, t.relname AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, i.indkey, i.indisclustered, c.contype, c.conname, c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, t.reloptions AS indreloptions, i.indisreplident, inh.inhparent AS parentidx, i.indnkeyatts AS indnkeyatts, i.indnatts AS indnatts, (SELECT pg_catalog.array_agg(attnum ORDER BY attnum)   FROM pg_catalog.pg_attribute   WHERE attrelid = i.indexrelid AND     attstattarget >= 0) AS indstatcols, (SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum)   FROM pg_catalog.pg_attribute   WHERE attrelid = i.indexrelid AND     attstattarget >= 0) AS indstatvals, i.indnullsnotdistinct FROM unnest('{17390,17409,17414,17419,17425,17433,17439,17447}'::pg_catalog.oid[]) AS src(tbloid)
JOIN pg_catalog.pg_index i ON (src.tbloid = i.indrelid) JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) LEFT JOIN pg_catalog.pg_inherits inh ON (inh.inhrelid = indexrelid) WHERE (i.indisvalid OR t2.relkind = 'p') AND i.indisready ORDER BY i.indrelid, indexname

[justinclift]

Oh, that's interesting. I don't remember seeing PGroonga before. It looks like it adds some fairly extensive extension code that doesn't handle upgrades very well: https://pgroonga.github.io/upgrade/

We have some initial code (not in the main branch yet) for upgrading extensions, so we'd probably need to extend that to check for PGroonga and take special steps when its found.

[justinclift]

Took at look at how PGroonga makes its docker image(s), as to support upgrading it we'd need to install PGroonga in our docker images as well.

Looking at the Debian one's Dockerfile, it might be possible:

https://github.com/pgroonga/docker/blob/c8cf2059d645996d16012ae73d21e66f1e261f9c/debian/17/Dockerfile

Reading over the upgrade page, we'd probably only need to have the very latest version of PGroonga installed, and we could "automatically" upgrade that extension when we upgrade PostgreSQL.

The question then becomes, "what happens when we bundle a newer version of PGroonga" (ie a new release of that) without there being a new version of PostgreSQL?

For example, let say we have PostgreSQL 17 + PGroonga 4.0.1 together in our pgautoupgrade image. If a new version of PGroonga comes out (ie 5.0.0), then our build process would automatically start including that instead (ie PostgreSQL 17 + PGroonga 5.0.0).

Unless there's some way to detect the new PGroonga version when our image starts, then our image wouldn't know it needs to run the upgrade process for that extension.

---

I guess we really would need to start recording version numbers of PG (including minor version) and extensions in the data directory somewhere, so that we can look for changes on startup.