Project

General

Profile

action #99201

coordination #99183: [epic] Upgrade all our infrastructure, e.g. o3+osd workers+webui, to openSUSE Leap 15.3

Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M

Added by okurz 4 months ago. Updated 22 days ago.

Status:
Resolved
Priority:
Low
Assignee:
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:

Description

Motivation

See parent #99183

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

  • Check the latest stable version of postgres (at the time of writing that is version 14).
  • 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
  • Perform the upgrade in production
  • Monitor for functional and performance impact

Related issues

Copied from openQA Infrastructure - action #76924: Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 size:MResolved2020-11-03

History

#1 Updated by okurz 4 months ago

  • Copied from action #76924: Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 size:M added

#2 Updated by okurz 4 months ago

  • Subject changed from Upgrade postgresql database version on o3 to default of Leap 15.2, i.e. postgres12 size:M to Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres13 size:M
  • Description updated (diff)
  • Assignee deleted (mkittler)
  • Start date deleted (2020-11-03)

#3 Updated by okurz 4 months ago

  • Target version changed from Ready to future

#4 Updated by okurz 2 months ago

  • Subject changed from Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres13 size:M to Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M
  • Description updated (diff)

#5 Updated by okurz about 1 month ago

  • Target version changed from future to Ready

#6 Updated by cdywan about 1 month ago

  • Subject changed from Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M to Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14

size:M seems to have been copied w/o the ticket having been estimated

#7 Updated by okurz 22 days ago

  • Subject changed from Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 to Upgrade postgresql database version on o3 to default of Leap 15.3, i.e. postgres14 size:M
  • Description updated (diff)
  • Status changed from New to Workable

#8 Updated by okurz 22 days ago

  • Status changed from Workable to In Progress
  • Assignee set to okurz

following http://open.qa/docs/#_migrating_postgresql_database_on_opensuse

currently stuck on zypper ref trying to read "Update repository with updates from SUSE Linux Enterprise 15"

#9 Updated by okurz 22 days ago

  • Status changed from In Progress to Resolved

This is what I did:

oldver=12 newver=14
zypper in postgresql$newver-server postgresql$newver-contrib
sudo -u postgres /usr/lib/postgresql$newver/bin/initdb --encoding=UTF8 --locale=en_US.UTF-8 --lc-collate=C --lc-ctype=en_US.UTF-8 --lc-messages=C --lc-monetary=C --lc-numeric=C --lc-time=C -D /var/lib/pgsql/data.$newver
sudo -u postgres vimdiff/var/lib/pgsql/data.$oldver/postgresql.conf /var/lib/pgsql/data.$newver/postgresql.conf
sudo -u postgres /usr/lib/postgresql$newver/bin/pg_upgrade --check --link --old-bindir=/usr/lib/postgresql$oldver/bin --new-bindir=/usr/lib/postgresql$newver/bin --old-datadir=/var/lib/pgsql/data.$oldver --new-datadir=/var/lib/pgsql/data.$newver && systemctl stop openqa-webui openqa-scheduler openqa-livehandler openqa-gru postgresql && sudo -u postgres /usr/lib/postgresql$newver/bin/pg_upgrade --link --old-bindir=/usr/lib/postgresql$oldver/bin --new-bindir=/usr/lib/postgresql$newver/bin --old-datadir=/var/lib/pgsql/data.$oldver --new-datadir=/var/lib/pgsql/data.$newver && ln --force --no-dereference --relative --symbolic /var/lib/pgsql/data.$newver /var/lib/pgsql/data && systemctl start postgresql openqa-webui openqa-scheduler openqa-livehandler openqa-gru && sudo -u geekotest psql -c 'select version();' openqa

The final command took only some seconds so the webUI was only down for seconds if noticeable at all. Deleted the old "data.10" folder and called zypper rm -u postgresql12 postgresql12-contrib postgresql12-server.

Also available in: Atom PDF