Does upgrading to GVM '22.8' mean losing all config and data?

Building from Source and Advanced Topics
1

Hi everybody ,

platform :
GVM ‘Source Edition’ v22.6 / v22.8 (depends on modules : gsad 22.6, openvas-scanner 22.7.4, gvmd 22.8.0, pg-gvm 22.6.1)

Debian virtual machine :
Distributor ID: Debian
Description: Debian GNU/Linux 12 (bookworm)
Release: 12.1
Codename: bookworm
Linux 6.1.0-12-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.52-1 (2023-09-07) x86_64 GNU/Linux

Context : installing GVM on new VM Debian 12.1 / GVM 22.6 Source Ed. / PostgreSQL v15 ,
in order to upgrade from Prod. platforms yet on Debian 10.12 / GVM 21.4.3 / PostgreSQL v11.

Compiling the new sources and installing the new test platform was successful. Minor problems (fixed) : temporary feedsync data directories accumulating in /tmp, improvements to key parameters in redis-server.conf (for the redis-server@openvas.service instance), and quite importantly, disabling hashsum verification for ‘Notus data’ in ospd-openvas.service - to get rid of GPG errors).

Up to now, I’ve always been using a v11 PG (postgresql) DB with three different versions of GVM, so a cold backup archive of the production DB could always be substituted to the ‘default’ one, and all GVM configurations, resources, users & groups, targets, scans configs, etc… AND scan data (reports) could always be totally recovered.

But now we have a v15 PG DB, and this no longer works. On forums and Q&As at postgresql.org, it is stated very clearly that “v11 DB and v15 DB files are NOT compatible: you must export your v11 data and re-import them in v15”. OK, fine.

I first tried to generate a v11 PG DB export with pg_dump, and import in v15 DB with ‘dropdb gvmd’ followed by pg_import. No luck : data not compatible. I managed to sort of understand that I had to output a ‘low level, ASCII (sql?)’ export.
So I tried : pg_dump -U gvm gvmd > ./dump/dbexport_20230916.pgsql; which seemed appropriate.
And : psql -U gvm gvmd < ./dbexport_20230916.pgsql to import in v15 on the target system
No luck. But this time, no ‘data compatibility’ problem, but ‘invalid SQL errors’.

And analyzing in PG logs, I discovered that tables structure has been altered between v11 and v15. For instance, the ‘users’ table now lacks 2 columns : ifaces and ifaces_allow … (!) (and there must be others).

Realizing this fact, I tried, just as a test, to export a single table (‘users’) from v11, modify it, suppressing these 2 fields, and try to re-import in v15. At first it failed : of course it failed, because of foreign key constraints (here on ‘users_owner_fkey’).
Now, if you retry the re-import using the ALTER TABLE users DROP CONSTRAINT users_owner_fkey; directive before the \copy users FROM ‘/home/gvm/sql/zetry.csv’ WITH(FORMAT CSV,HEADER true,DELIMITER ‘,’); command.
Now it sort of “work” (so to say …), but :
. no modification is visible in GSA (even with refresh or logout/login)
. and are we supposed to do ‘all that’ … ??
. to my knowledge there is nowhere any formal description of the ‘gvmd’ DB, all its tables, and all the foreign key constraints (!)
. and as far as I (correctly ?) checked, there are (in GVM v21.x) 102 gvmd DB tables …

Q1 : are we supposed to to all these cumbersome things within the DBs … ?? seriously … ?!
Q2 : has the sustainability of config and scans data in the gvmd even DB been foreseen by developers of GVM v22 / PostgreSQL v15 … ??? or … NOT_AT_ALL ?? (meaning: “From GVM v22.x onward, you are supposed to forget all your your previous data (configs and scans data), and re-start everything from scratch …”)

I know I’ve been a bit long (please excuse me, but some details were required).
Now I would greatly appreciate an honest and straightforward answer : “YES, you lose all DB data” (and this may happen again with a major upgrade in the future as well)

Because here, I’m really on the verge of totally giving up on GVM ‘Source Ed.’ if this is it.
It’s impossible to seriously and efficiently work in production in these conditions, managing all the users, groups, rights, configs, targets, schedules, scan data history, and so on and so on … that have accumulated so far in our DBs.

many thanks for any consideration you can give to my request,
best regards, J. Le Moigne jean.le-moigne@inrae.fr

2

Hi,

I can understand your frustrations with a PostgeSQL update from an old version to a newer one. This seems to be no easy task sometimes. It seems to be a generic issue with PostgeSQL and is not really an issue with our usage of PostgreSQL and gvmd.

Some of our version updates - for example from 21.4 to 22.4 - database structure changes need to be applied. You can run these updates by calling the command gvmd --migrate. So the db update as SQL plaint text export and import via pg_dump should work (assuming you are using the same install prefix) if you stop the gvmd service, run a gvmd --migrate as the gvm user and afterwards re-start the gvmd service.

Just a reminder/hint this is exactly the difference between an open source software that’s free and an enterprise product with support which you are paying for. You need to invest time to care about (database) updates by yourself on your specific platform with your specific update timeline.

2 Likes
3

Hello Björn, and many thanks for your quick and precise answer. I definetely confess that I have completely by-passed the --migrate option (I’m no DB, and most precisely, no postgresql DB ‘expert’).
So I’ll investigate this track immediately and let you (and the community) know.

As to your reminder, what should I say … ? That you’re just like a missionary priest trying to convert an already member of the Church … ? The key point is : "You’d better talk to … my manager … (!) "
;-D)) people are just like that, you know : he LOVES free software … (don’t ask me why)

many thanks again, I’ll investigate ’ --migrate’ right now, and hopefully … (cross my fingers).
But I’m still worried that some GVM DB tables HAVE CHANGED … (and this has nothing to do with the RDB you’re using, PostgreSQL or anything else: it’s exclusively ‘GVM internal’). Maybe ’ --migrate ’ can “compensate” for altered tables fields defs. upon import … ??? we shall see.

best regards, Jean jean.le-moigne@inrae.fr

4

Hi,

let me be clear, I love Free Software/Open Source and therefore I am working for Greenbone. I am a Free Software advocate. I am not a fan of closed source products.

If you are using Free Software for free, you need to spend your time (and therefore money) on that software and solve issues by yourself. In contrast you can buy a product and support from a company even for Free Software. That’s the power of choices.

The tables should not change on a pg_dump import. I guess the new version of gvmd did start automatically on a clean database and created the db tables for the new structure automatically. Therefore it is required to shut down the gvmd service before doing some database work.

2 Likes