QA » openQA Project » openQA Infrastructure

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.

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.

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

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/ ?