action #76924
closedcoordination #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
0%
Description
Motivation¶
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
Acceptance criteria¶
- AC1: o3 runs openQA from the current default postgres database version
- AC2: no severe functional or performance related impact has been observed
Suggestions¶
- 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).
Further details¶
https://build.opensuse.org/package/view_file/openSUSE:Leap:15.2:Update/postgresql/postgresql.spec?expand=1
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
Updated by okurz almost 4 years ago
- Copied to action #76927: Upgrade postgresql database version on osd to default of Leap 15.2, like on o3 size:M added
Updated by okurz over 3 years ago
- Status changed from Workable to New
moving all tickets without size confirmation by the team back to "New". The team should move the tickets back after estimating and agreeing on a consistent size
Updated by ilausuch about 3 years ago
- Subject changed from Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 to Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 size:M
- Description updated (diff)
- Status changed from New to Workable
Updated by mkittler about 3 years ago
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
The postgresql-server
package has version 13-lp152.3.9.1
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 postgresql.service
starts /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 /usr/lib/postgresql10/bin/postgres
.
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.
Updated by mkittler about 3 years ago
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.
Updated by mkittler about 3 years ago
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.
Updated by okurz about 3 years ago
mkittler wrote:
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.
Well, you could cleanup older dirs, e.g. delete data.94
and data.96
Updated by okurz about 3 years ago
mkittler wrote:
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/ ?
Updated by mkittler about 3 years ago
- 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.
Updated by okurz about 3 years ago
- Copied to action #99201: Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M added