Gvm-migrate-to-postgres fails to migrate (Error: no such table: permissions_get_tasks)

Hi there,

Sorry if this question sounds stupid, but I’m trying to migrate my Sqlite3 db from GVM-9 to postgres in GVM-11 using the gvm-migrate-to-postgres.

So basically the issue is:

I launch the script with no existing postgresql database (fresh install); the script fails with the following error:

psql: FATAL: database “gvmd” does not exist

Or

I launch the script with a fresh new gvmd.db database (created using the instructions in gvmd INSTALL.md file), and the script complains the database already exist !

<29>Feb 6 17:31:48 gvm-migrate-to-postgres: Postgres database exists already, skipping migration.

I’m not familliar with SQL so it’s certainly something stupid, but I would appreciate some guidance here.

Thanks !

Ok nevermind my previous message, when trying with existing gvmd database, it fails because the db was not fully empty. My bad. So now the script goes beyond but fails with the following error:

<31>Feb 6 18:00:27 gvm-migrate-to-postgres: SQLite: DROP TABLE IF EXISTS escalator_condition_data_trash;
<31>Feb 6 18:00:27 gvm-migrate-to-postgres: SQLite: SELECT EXISTS (SELECT FROM sqlite_master WHERE type=‘table’ AND name=‘permissions_get_tasks’);
Error: near “FROM”: syntax error
<31>Feb 6 18:00:27 gvm-migrate-to-postgres: SQLite: UPDATE permissions_get_tasks SET has_permission = 1 WHERE has_permission != ‘0’;
Error: no such table: permissions_get_tasks
<27>Feb 6 18:00:27 gvm-migrate-to-postgres: Failed to clean permission_get_tasks: sql command exited with code 1.

Please Upgrade from GVM-9 to GVM-10 (PSQL) and then to GVM-11 :wink: I think the migrate script can´s skip major versions.

Hi Lukas,

I’m not sure to understand why this is necesssary. Does that mean that the migration script provided with GVM-10 is different from the one provided in GVM-11 with no backward compatibility ? And do I just need to install the script from GVM-10, apply it to my SQLiteDB, then apply the script from GVM-11 ?

Or does that mean that I must do a full installation of GVM-10, migrate the DB, make it run with gvmd, etc… and once it’s working, upgrade to GVM-11 ?

Thank you

You can only migrate from one major to the next major version. So i think the GVM-11 Script can´t migrate the SQLite data model.

Yes with a feed sync, very important. And then you can migrate to GVM-11.

Here some hints for the Migration to GVM-10:

Thanks for the hints. Last question; once upgraded to GVM-10, do I need to migrate the db to postgres or keep it under sqlite model, so that the migration to postgres is only made at time of migration to gvm-11 ?

Hi Lukas,

I have migrated my db to GVM-10 revision (205), then launched the migration script, within a GVM-11 VM, but I end up with the same error. So the issue here doesn’t seems related with upgrading from gvm-9 to gvm-11 directly.

Any idea ?

Thanks

Please drop SQLite on GVM10 and migrate from GVM-9 to GVM-10 with the real database. GVM 11 does not have any SQLite support any more.

Did you migrated to postgres already ?

I finally managed to start the migration, but as you said everything must be done release by release. Right now I am under gvm-11. Migration to postgres is done, and gvm is now migrating the db to the right revision with “gvmd -m”.

But it takes ages ! I started gvmd -m 5hours ago and it is still running…

Tip: Run it on real metal virtualization is poison for IO performance.

Understood, right now I’m testing migration only; hence the VM. Thanks

If it helps … I know this will sound convoluted, but it worked for me.

New VM. I used Ubuntu 18.04.04 Server

Build 10 from source
copy tasks.db from openvas 9 to new vm and replace the gvmd.db

run:
gvmd --migrate
gvmd --optimize=vacuum

Build a new VM with Ubuntu 18.04.04
Build 11 from source
copy gvmd.db to new vm

run
gvm-migrate-to-postgres

viola!

I tried a few other paths, and this was the one that worked for me.

Make sure you are starting with an empty gvmd databse in postgres. If it fails for some reason, drop the database before trying again.

-Scott

1 Like

Thanks Immaus,I confirm that’s the way to do. You have to go through gvm9->gvm10->gvm11 otherwise it doesn’t work.

1 Like

Well, I’m almost there … when I move the database to my final target, a container … I get this:

The database was initialized with LC_COLLATE “en_US.UTF-8”, which is not recognized by setlocale().

and that was easy …

apt install locales-all

Guess I need to add that to my Dockerfile

:smiley: