Project

General

Profile

action #76924

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

Added by okurz about 1 year ago. Updated 3 months ago.

Status:
Resolved
Priority:
Low
Assignee:
Target version:
Start date:
2020-11-03
Due date:
% Done:

0%

Estimated time:

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


Related issues

Copied to openQA Infrastructure - action #76927: Upgrade postgresql database version on osd to default of Leap 15.2, like on o3 size:MResolved2020-11-03

Copied to openQA Infrastructure - action #99201: Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:MNew

History

#1 Updated by okurz about 1 year ago

  • Copied to action #76927: Upgrade postgresql database version on osd to default of Leap 15.2, like on o3 size:M added

#2 Updated by okurz 5 months 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

#3 Updated by ilausuch 4 months 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

#4 Updated by mkittler 3 months ago

  • Assignee set to mkittler

#5 Updated by mkittler 3 months 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.

#6 Updated by mkittler 3 months 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.

#7 Updated by mkittler 3 months 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.

#8 Updated by mkittler 3 months ago

  • Status changed from Workable to Feedback

#9 Updated by okurz 3 months 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

#11 Updated by mkittler 3 months 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.

#12 Updated by okurz 3 months ago

  • Copied to action #99201: Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M added

Also available in: Atom PDF