coordination #69478: [epic] Upgrade o3+osd workers+webui to openSUSE Leap 15.2
Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 size:M
All our CI tests run against postgres12, see https://build.opensuse.org/package/live_build_log/devel:openQA:ci/base/containers/x86_64 , as the default postgres version within openSUSE:Leap:15.2 is postgres12 . We should use the same version for our production instances to reduce the risk of diff between production and testing environments and also to run a version that will be supported for longer
- AC1: o3 runs openQA from the current default postgres database version
- AC2: no severe functional or performance related impact has been observed
- Research how postgres database upgrades are conducted, e.g. see #43976#note-6
- Try it out in a test environment, e.g. container loading o3 database dump file
- Do it in real environment
- Monitor for functional and performance impact
- Check the latest stable version of postgres (currently is version 10).
shows that the default switched to 12 whereas in https://build.opensuse.org/package/view_file/openSUSE:Leap:15.1:Update/postgresql/postgresql.spec?expand=1 it looks a bit inconsistent, mixed 10+12. However all versions up from 9.6 are still supported in openSUSE Leap 15.1 and 15.2
Looks like the latest version in Leap 15.2 is PostgreSQL 12:
martchus@ariel:~> sudo zypper se postgres | grep -i server | freeradius-server-postgresql | Postgresql support for freeradius | Paket i+ | postgresql-server | The Programs Needed to Create and Run a PostgreSQL Server | Paket | postgresql-server-devel | PostgreSQL server development header files and utilities | Paket i+ | postgresql10-server | The Programs Needed to Create and Run a PostgreSQL Server | Paket | postgresql11-server | The Programs Needed to Create and Run a PostgreSQL Server | Paket | postgresql11-server-devel | PostgreSQL server development header files and utilities | Paket | postgresql12-server | The Programs Needed to Create and Run a PostgreSQL Server | Paket | postgresql12-server-devel | PostgreSQL server development header files and utilities | Paket | postgresql96-server | The Programs Needed to Create and Run a PostgreSQL Server | Paket | zabbix-server-postgresql | Zabbix server with PostgreSQL support | Paket
postgresql-server package has version
13-lp188.8.131.52 but does not contain the actual server. It only contains the service file and start script.
The currently used version is 10:
openqa=> select version(); version --------------------------------------------------------------------------------------- PostgreSQL 10.17 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit (1 Zeile)
I was wondering where we configure the version being used. Apparently
/usr/share/postgresql/postgresql-script (both from the
postgresql package) which checks for the database version and then starts the corresponding PostgreSQL version, in this case
I'll check the documentation to figure out how the migration can be done with as less downtime as possible and try it out on my workstation (which is conveniently also still at PostgreSQL 10) first.
It looks like using
pg_upgrade is the easiest way. I've just been running it locally to upgrade from PostgreSQL 10 to 12 and I had multiple production databases present. It only took a few seconds.
These are the concrete commands I've been using: https://github.com/Martchus/openQA-helper#postgresql-migration-on-opensuse
The commands should work on o3 as well so if nobody has any objections I'd do the update there as well.
The PostgreSQL upgrade seems to have worked. It needed a few attempts because the db uses inconsistent locale settings (at some places en_US.UTF-8 and in others C) and one really has to stick with the previous locale settings. I also took over the
log_min_duration_statement = 5000 which seems to be the only other custom setting in our db config. I've been updating the steps on https://github.com/Martchus/openQA-helper#postgresql-migration-on-opensuse.
I didn't execute the last step (removal of the old data dir) because it seems we generally don't do that as even older data dirs are still present. Should I remove it? As hardlinks are used it doesn't not take much additional space but I assume it also means we cannot make use of it anymore anyways.
I've been updating the steps on https://github.com/Martchus/openQA-helper#postgresql-migration-on-opensuse.
Why not update either http://open.qa/docs or https://progress.opensuse.org/projects/openqav3/wiki/ ?
- Status changed from Feedback to Resolved
I'm using the markdown document in my helpers repo as starting point because it is way easier to edit the markdown document while exploring how it is actually done than editing a Wiki page or creating a correctly formatted AsciiDoc document. However, I also like to have this in the official documentation so since you've already been indirectly asking I took the opportunity to spend the effort: https://github.com/os-autoinst/openQA/pull/4171
The ticket itself can be considered resolved as it looks like the migration went well.