Subject: fail to run postgresql16 after update from postgresql13

From: Ianseeks <[email-masked]>
Date: Thu, 02 Nov 2023 08:14:03 +0000

Hi

i hope i've got the correct mailing list for this question/problem.  I'm a 
novice.

A little history. 
I use opensuse tumbleweed and the Kmail/Kontact PIM software which is 
contrlled by software called Akondai and this requires a DB.  The default was 
Mysql but it caused a lot of reliability issues for me so i migrated to 
Postgresql 9.6 (yes, it was a few years ago)

I've since then update the DB to 10, 11, 12 until ver 13 which i 
have been using until now.  I  did try updating to ver 14 but it failed with 
locale issues during the update and i couldn't get a resolution.

I just recently managed to get the database converted from postgresql13 to 
postgresql16 and the data was transferred to the new DB with no issues. 

I can't get Akonadi to load as it fails to start postgresql16, its failing 
with a message saying it needs postgesql9.6 as the DB was originally created 
using 9.6. I can't see why it insists on ver 9.6 being there as i've updated 
to ver 16 and that process means i create an empty DB.  I can't even install 
Ver 9.6 to see if that cures the problem as its no longer in the opensuse 
repos.

Thanks for any help.

-------------- the section below list processes and the errors ----------

I update the DB manually via scripts with one file per action.
1. Backup
# stop akonadi
akonadictl stop
# copy
cp -R $HOME/.local/share/akonadi/db_data $HOME/.local/share/akonadi/db_data_13
# rename
mv $HOME/.local/share/akonadi/db_data $HOME/.local/share/akonadi/db_data_old

2. Create new DB
/usr/lib/postgresql16/bin/initdb --pgdata=$HOME/.local/share/akonadi/db_data 
--lc-collate=en_US.UTF-8 --locale=en_US.UTF-8 

3. Check upgrade
/usr/lib/postgresql16/bin/pg_upgrade -b /usr/lib/postgresql13/bin -B /usr/lib/
postgresql16/bin  -d $HOME/.local/share/akonadi/db_data_13 -D $HOME/.local/
share/akonadi/db_data  --check

4. Do the upgrade if "3" worked okay 
/usr/lib/postgresql16/bin/pg_upgrade -b /usr/lib/postgresql13/bin -B /usr/lib/
postgresql16/bin -d $HOME/.local/share/akonadi/db_data_13 -D $HOME/.local/
share/akonadi/db_data

-----
Here are the error messages from akonadi and the journal.

--------------
~:> akonadictl start
org.kde.pim.akonadictl: Starting Akonadi Server...
org.kde.pim.akonadictl:    done.
~> Connecting to deprecated signal 
QDBusConnectionInterface::serviceOwnerChanged(QString,QString,QString)
org.kde.pim.akonadiserver: Starting up the Akonadi Server...
(QFileInfo(/usr/lib/postgresql/bin), QFileInfo(/usr/lib/postgresql/lib64))
QSqlDatabase: QPSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QMARIADB QMYSQL QMYSQL3
org.kde.pim.akonadiserver: Invalid database object during database server 
startup
QProcess: Destroyed while process ("/usr/bin/pg_ctl") is still running.
org.kde.pim.akonadiserver: Shutting down AkonadiServer...
org.kde.pim.akonadicontrol: Application '/usr/bin/akonadiserver' exited 
normally...

---------

This is from "systemctl status postgresql.service"

× postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; 
preset: disabled)
     Active: failed (Result: exit-code) since Tue 2023-10-31 16:24:13 GMT; 
1min 31s ago
    Process: 5916 ExecStart=/usr/share/postgresql/postgresql-script start 
(code=exited, status=1/FAILURE)
        CPU: 8ms

Oct 31 16:24:13 Lian-Li systemd[1]: Starting PostgreSQL database server...
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Your database files were 
created by PostgreSQL version 9.6.
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Could not find executables 
for this version.
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Please install the 
PostgreSQL server package for version 9.6.
Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Control process 
exited, code=exited, status=1/FAILURE
Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Failed with result 
'exit-code'.
Oct 31 16:24:13 Lian-Li systemd[1]: Failed to start PostgreSQL database 
server.

----------
From the journal using "journalctl -xeu postgresql.service"

Oct 31 16:24:13 Lian-Li systemd[1]: Starting PostgreSQL database server...
Subject: A start job for unit postgresql.service has begun execution
Defined-By: systemd
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel

A start job for unit postgresql.service has begun execution.
 
The job identifier is 2241.
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Your database files were 
created by PostgreSQL version 9.6.
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Could not find executables 
for this version.
Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Please install the 
PostgreSQL server package for version 9.6.
Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Control process 
exited, code=exited, status=1/FAILURE
Subject: Unit process exited
Defined-By: systemd
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel

An ExecStart= process belonging to unit postgresql.service has exited.
 
The process' exit code is 'exited' and its exit status is 1.
Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Failed with result 
'exit-code'.
Subject: Unit failed
Defined-By: systemd
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel

The unit postgresql.service has entered the 'failed' state with result 'exit-
code'.
Oct 31 16:24:13 Lian-Li systemd[1]: Failed to start PostgreSQL database 
server.
Subject: A start job for unit postgresql.service has failed
Defined-By: systemd
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
A start job for unit postgresql.service has finished with a failure.

-----------------------

opensuse:tumbleweed:20231030
Qt: 5.15.11 KDE Frameworks: 5.111.0 - KDE Plasma:  5.27.9 - kwin 5.27.9
kmail2 5.24.2 (23.08.2) - akonadiserver 5.24.2 (23.08.2) - Kernel:  6.5.9-1-
default  - kernel-firmware-radeon  20231019

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 02 Nov 2023 11:16:33 +0100

> i hope i've got the correct mailing list for this question/problem.  I'm a 

If you upgraded all the way from 9.6 to v16, you are not a novice.
But that's fine.  I find that - for whatever reason - people who write
to the pgsql-novice rarely are.  Perhaps modesty.

Anyway.

> Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Your database files were created by PostgreSQL version 9.6.
> Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Could not find executables for this version.
> Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Please install the PostgreSQL server package for version 9.6.
> Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
> Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service: Failed with result 'exit-code'.
> Oct 31 16:24:13 Lian-Li systemd[1]: Failed to start PostgreSQL database server.

These error messages are not from PostgreSQL, but they seem to be from
the script "postgresql-script".  That script is not part of PostgreSQL.

Look into that script and try to figure out how it arrives at that conclusion.
If you cannot figure it out yourself, please share the relevant parts.

Yours,
Laurenz Albe

---

From: Ianseeks <[email-masked]>
Date: Thu, 02 Nov 2023 11:13:46 +0000

> to the pgsql-novice rarely are.  Perhaps modesty.

Thanks for responding
I am a novice with postgresql - i just copied someone elses work and just 
editted it to work on my system. :)

> > contrlled by software called Akondai and this requires a DB.  The default
> > was Mysql but it caused a lot of reliability issues for me so i migrated
> > to Postgresql 9.6 (yes, it was a few years ago)
> > 
> > have been using until now.  I  did try updating to ver 14 but it failed
> > with locale issues during the update and i couldn't get a resolution.
> > 
> > with a message saying it needs postgesql9.6 as the DB was originally
> > created using 9.6. I can't see why it insists on ver 9.6 being there as
> > i've updated to ver 16 and that process means i create an empty DB.  I
> > can't even install Ver 9.6 to see if that cures the problem as its no
> > longer in the opensuse repos.
> > 
> > created by PostgreSQL version 9.6. Oct 31 16:24:13 Lian-Li
> > postgresql-script[5916]:  Could not find executables for this version.
> > PostgreSQL server package for version 9.6. Oct 31 16:24:13 Lian-Li
> > systemd[1]: postgresql.service: Control process exited, code=exited,
> > status=1/FAILURE Oct 31 16:24:13 Lian-Li systemd[1]: postgresql.service:
> > Failed with result 'exit-code'. Oct 31 16:24:13 Lian-Li systemd[1]:
> > Failed to start PostgreSQL database server.
> Look into that script and try to figure out how it arrives at that
> conclusion. If you cannot figure it out yourself, please share the relevant
> parts.

Is it acceptable to attach text files to email in this mailing list?  I know 
some don't like it.
I've found the script file (/usr/share/postgresql/postgresql-script) and that 
message is in there but the coding in it is beyond my expertise as i can only 
do basic stuff.
Here is a cut and paste of part of it.

----- /usr/share/postgresql/postgresql-script ----

PG_SYSCONFIG=/etc/sysconfig/postgresql
test -f $PG_SYSCONFIG && . $PG_SYSCONFIG

eval DATADIR=${POSTGRES_DATADIR:-~postgres/data}
OPTIONS=${POSTGRES_OPTIONS}
INITDB_OPTS=${POSTGRES_INITDB_OPTS}
PIDFILE=$DATADIR/postmaster.pid

# 
if test -r $DATADIR/PG_VERSION ; then
    DATA_VERSION=$(cat $DATADIR/PG_VERSION)
    POSTGRES=/usr/lib/postgresql$(echo -n $DATA_VERSION | tr -d .)/bin/
postgres
fi
if test -x /usr/bin/postgres; then
    ACTIVE=$(readlink -q -f /usr/bin/postgres)
    test -z "$POSTGRES" && POSTGRES="$ACTIVE"
fi
if test -n "$DATA_VERSION"; then
    if test -z "$ACTIVE" -o "$ACTIVE" != "$POSTGRES"; then
	echo " Your database files were created by PostgreSQL version 
$DATA_VERSION."
	if test -x "$POSTGRES"; then
	    echo " Using the executables in $(dirname $POSTGRES)."
	else
	    echo " Could not find executables for this version."
	    echo " Please install the PostgreSQL server package for version 
$DATA_VERSION."
	fi
    fi
elif test -z "$ACTIVE"; then
    echo " Cannot find an active PostgreSQL server binary. Please install one 
of the PostgreSQL"
    echo " server packages or activate an already installed version using 
update-alternatives."
fi
if test ! -x "$POSTGRES"; then
    exit 1
fi

> Laurenz Albe

opensuse:tumbleweed:20231031
Qt: 5.15.11 KDE Frameworks: 5.111.0 - KDE Plasma:  5.27.9 - kwin 5.27.9
kmail2 5.24.2 (23.08.2) - akonadiserver 5.24.2 (23.08.2) - Kernel:  6.5.9-1-
default  - kernel-firmware-radeon  20231019

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 02 Nov 2023 15:27:34 +0100

> test -f $PG_SYSCONFIG && . $PG_SYSCONFIG

Look into "/etc/sysconfig/postgresql" and see if it sets POSTGRES_DATADIR.

> eval DATADIR=${POSTGRES_DATADIR:-~postgres/data}

If not, DATADIR is set to the subdirectory "data" of the home directory
of user "postgres".

Is DATADIR the data directory you upgraded?

>     POSTGRES=/usr/lib/postgresql$(echo -n $DATA_VERSION | tr -d .)/bin/postgres
> fi

DATA_VERSION is set to the first line in the PG_VERSION file in DATADIR.

What is that?  According to the error message, it should be 9.6.

POSTGRES is set to /usr/lib/postgresql<DATA_VERSION with periods removed>/bin/postgres

> 	echo " Your database files were created by PostgreSQL version $DATA_VERSION."

Looks like /usr/bin/postgres, if you resolve the symbolic link, is different
from POSTGRES constructed above.

Yours,
Laurenz Albe

---

From: Ianseeks <[email-masked]>
Date: Thu, 02 Nov 2023 16:34:44 +0000

On Thursday, 2 November 2023 14:27:34 GMT Laurenz Albe wrote:
> > > > Oct 31 16:24:13 Lian-Li postgresql-script[5916]:  Your database files
> > > > were created by PostgreSQL version 9.6.> > 
> > Is it acceptable to attach text files to email in this mailing list?  I
> > know some don't like it.
> > I've found the script file (/usr/share/postgresql/postgresql-script) and
> > that message is in there but the coding in it is beyond my expertise as i
> > can only do basic stuff.
Seems it points here /var/lib/pgsql/data
> Is DATADIR the data directory you upgraded?
No, its a database in my local login
 
> What is that?  According to the error message, it should be 9.6.
Yes, it is 9.6 so thats where its getting it from.

> from POSTGRES constructed above.

I'll have to pass this back to opensuse to solve.  
> Laurenz Albe

Thanks a lot for your help, its been very useful.
All the best.