Establish Postgres database format upgrade

[phillxnet]

Thanks to @FroggyFlox & KarstenV on the forum for identifying this need. From testing channel/branch v5.0.6.0 onwards we have fully updated our Django to 4.2 LTS, and the related python interface library of psycopg to V3: this has surfaced a Django/db-format incompatibility requiring an in-place db update capability.

From @FroggyFlox's forum comment here: https://forum.rockstor.com/t/after-updating-to-5-0-6-0-web-interface-is-not-accesible-and-network-shares-are-also-not-accesible/9226/29

We see that although we have previously update our Postgres dependency to 13, and enforce that binaries use via the alternatives system when doing an rpm update, this does not in-itself adjust the underlying DB format, and the newer start up scripts, if they find an older database, will in-fact invoke the relevant older binary of Postgres, regardless of the alternatives system.

New, or more recent installs are not affected assuming the use of our official installer as this always wipes the entire system disk, and so always establish a fresh and alternatives guided postgresql version. But Rockstor installs that have been updated from older Leap instances will inherit DB formats that were default at that time: to date we have reports of Postgres version 10 databases.

It is proposed that for the next Stable release we develop a script/mechanism to identify and then migrate, in-place, older db versions to the newer 13 version that we currently favour. From the reports in our ongoing testing phase we have the following from KarstenV's testing efforts with 5.0.6-0 (RC1) rpm. Where we significantly updated our Django (twice), as well as adopting psycopg3 which are both very significant update.

NotSupportedError(', ‘django.db.utils.NotSupportedError: PostgreSQL 12 or later is required (found 10.23).’, ‘’]

Due to the Postgresql startup scripts failing over to the older Postgres to accomodate the older DB format found:

Jan 18 22:28:33 RockstorNAS postgresql-script[7819]: Your database files were created by PostgreSQL version 10.

The latter resulted when removing the co-resident postgres v10 as openSUSE can carry multiple postgres package installs and auto-selects the newest able to read the database in question.

I.e. form @FroggyFlox's exposition of this issue on the linked form thread we have:

This happens when the postgresql.service systemd service tries to start. If we look at that service (systemctl cat postgresql.service), we can see it tries to run the following:

ExecStart=/usr/share/postgresql/postgresql-script start That file is provided by the system postgresql-server package

and

That script is just a bash script that does some checks, such as compatibility between postgresql versions. The part that is failing on your machine is:

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

Similarly we might establish our own script to identify older 10 db instances: established by old official installers, and provide an in-place update mechanism via presumably: pg_upgrade which must be run under the newer/target Postgres version involved in the migration. Note that a prior name for pg_upgrade was pg_migrator.

[phillxnet]

Database format versions per official installer.

Rockstor's installers contain Stable Release Candidate (RC) or actual Stable Release rpms. So these are our main concern regarding ensuring an update path.

postgres --version
postgres (PostgreSQL) 10.19
cat /var/lib/pgsql/data/PG_VERSION
10

Leap Version	Installer Version	DB Version	PG_VERSION
15.4	4.5.8-0 RC5	13.10	13
15.3	4.5.8-0 RC5	13.9	13
15.3	4.1.0-0 stable	10.19	10

From the above table we have our 10 versioned database as having been created from most-recently a Leap 15.3, 4.1.0-0 Rockstor installer:

This incompatibility only arises when updating to testing channel rpm version 5.0.6-0 released on 16th January 2024 (2 days ago) for Leap 15.4 (curtousy) 15.5 (main target) & TW (Dev/Advcd/Rescue use only) only. As such this represents a concern for us regarding our next stable release only for those having followed a zypper dup distribution upgrade process, given the last stable rpm released for 15.3 was 4.1.0-0 (and for 15.4 stable it was 4.6.1-0).

We do not yet have a stable rpm release for 15.5, but our current testing rpm of 5.0.6-0 is RC1, and we have sceduled 5.1.0-0 as our planned next stable rpm - to be published for 15.5 & TW only in the Stable channel.

[phillxnet]

Given the last comments exposition: this issue affects only those installs subscribed to our development testing channel (5.0.6-0 rpm currently) that have also zypper dup distribution updated from a prior installer that defaulted to Postgres version 10. As such we need to add what ever migration mechanism/script we devise/advise to the next update doc re:

"Distribution update from 15.4 to 15.5 - howto": https://github.com/rockstor/rockstor-doc/issues/467

[phillxnet]

Proposed reproducer given the above is a Leap 15.3 4.1.0-0 install that is then dup updated via:

• "Distribution update from 15.3 to 15.4": https://rockstor.com/docs/howtos/15-3_to_15-4.html

We should then have a DB created by Posgres10 but via a later testing channel/branch update past:

rpmbuild 5.0.2-0: https://github.com/rockstor/rockstor-rpmbuild/releases/tag/5.0.2-0 where we enforced an alternatives setting for Postgresql 13

and rpm version 4.5.0-0 where we adopted a Postgresql 13 dependency: 4.5.0-0: our first testing after 4.1.0-0 stable rpm: https://github.com/rockstor/rockstor-core/releases/tag/4.5.0-0

[phillxnet]

Dev notes:

Initial experimentation re in-development bash script to prepare DBs/env for, and then use pg_upgrade in a modified rockstor Postgres user ~ directory:

installer:/var/lib/pgsql # sudo -u postgres pg_upgrade \
>      --old-bindir="${BIN_BASEDIR}${CURRENT_DATA_VERSION}/bin"     \
>      --new-bindir="${BIN_BASEDIR}${TO_VERSION}/bin"       \
>      --old-datadir="${DATA_BASEDIR}/data${CURRENT_DATA_VERSION}/" \
>      --new-datadir="${DATA_BASEDIR}/data${TO_VERSION}/"
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 system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for removed "abstime" data type in user tables     ok
Checking for removed "reltime" data type in user tables     ok
Checking for removed "tinterval" data type in user tables   ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          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
------------------
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
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

[phillxnet]

vacuumdb

installer:~ # pushd /var/lib/pgsql
installer:/var/lib/pgsql # sudo -u postgres ${BIN_BASEDIR}"${TO_VERSION}"/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "smartdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "storageadmin": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "smartdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "storageadmin": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "smartdb": Generating default (full) optimizer statistics
vacuumdb: processing database "storageadmin": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

reindexdb

installer:/var/lib/pgsql # sudo -u postgres ${BIN_BASEDIR}"${TO_VERSION}"/bin/reindexdb --all --concurrently
reindexdb: reindexing database "postgres"
WARNING:  cannot reindex system catalogs concurrently, skipping all
reindexdb: reindexing database "smartdb"
WARNING:  cannot reindex system catalogs concurrently, skipping all
reindexdb: reindexing database "storageadmin"
WARNING:  cannot reindex system catalogs concurrently, skipping all
reindexdb: reindexing database "template1"
WARNING:  cannot reindex system catalogs concurrently, skipping all

[phillxnet]

DB size differences

These are default stable Leap 15.3 to 15.4 4.1.0-0 to 4.6.1-0 derived databases with trivial history.

installer:/var/lib/pgsql # du -sh data10 data13
78M     data10
81M     data13

[phillxnet]

Closing as: Fixed by #2787