Thread: fail to run postgresql16 after update from postgresql13
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
On Thu, 2023-11-02 at 08:14 +0000, Ianseeks wrote: > i hope i've got the correct mailing list for this question/problem. I'm a > novice. 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. > 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. > > Here are the error messages from akonadi and the journal. > > -------------- > > This is from "systemctl status postgresql.service" > > 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. 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
On Thursday, 2 November 2023 10:16:33 GMT Laurenz Albe wrote:
> On Thu, 2023-11-02 at 08:14 +0000, Ianseeks wrote:
> > i hope i've got the correct mailing list for this question/problem. I'm a
> > novice.
>
> 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.
Thanks for responding
I am a novice with postgresql - i just copied someone elses work and just
editted it to work on my system. :)
> Anyway.
>
> > 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.
> >
> > Here are the error messages from akonadi and the journal.
> >
> > --------------
> >
> > This is from "systemctl status postgresql.service"
> >
> > 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.
> 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.
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
> Yours,
> 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
On Thu, 2023-11-02 at 11:13 +0000, Ianseeks wrote:
> On Thursday, 2 November 2023 10:16:33 GMT Laurenz Albe wrote:
>
> > > 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.
> > >
> > > This is from "systemctl status postgresql.service"
> > >
> > > 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.
> >
> > 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.
>
> 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
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?
> 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
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
> 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."
Looks like /usr/bin/postgres, if you resolve the symbolic link, is different
from POSTGRES constructed above.
Yours,
Laurenz Albe
On Thursday, 2 November 2023 14:27:34 GMT Laurenz Albe wrote:
> On Thu, 2023-11-02 at 11:13 +0000, Ianseeks wrote:
> > On Thursday, 2 November 2023 10:16:33 GMT Laurenz Albe wrote:
> > > > 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.
> > > >
> > > > This is from "systemctl status postgresql.service"
> > > >
> > > > 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.> >
> > > 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.
> >
> > 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
>
> Look into "/etc/sysconfig/postgresql" and see if it sets POSTGRES_DATADIR.
>
> > eval DATADIR=${POSTGRES_DATADIR:-~postgres/data}
Seems it points here /var/lib/pgsql/data
> If not, DATADIR is set to the subdirectory "data" of the home directory
> of user "postgres".
>
> Is DATADIR the data directory you upgraded?
No, its a database in my local login
> > 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
>
> 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.
Yes, it is 9.6 so thats where its getting it from.
> POSTGRES is set to /usr/lib/postgresql<DATA_VERSION with periods
> removed>/bin/postgres
> > 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."
> Looks like /usr/bin/postgres, if you resolve the symbolic link, is different
> from POSTGRES constructed above.
I'll have to pass this back to opensuse to solve.
> Yours,
> Laurenz Albe
Thanks a lot for your help, its been very useful.
All the best.
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