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