Migrate from SQLlite to Postgres


at the moment i want to migrate my sql file to postgresql database, first i had problems with creating the database cause my “login-username” was “user” and like i think now postgresql have many problems when the username is “user”, because of that i had to change my username and all path’s… (Atm working to fix the .service scripts again, maybe you can help me there to= failed to connect to bus: no such file or directory### after changing the username)
But my current problem is that the migration doesnt work with the gvm-migrate-to-postgres script. Its output looks like that:

<30>Oct 17 09:26:14 gvm-migrate-to-postgres: Checking SQLite3 database.
<31>Oct 17 09:26:14 gvm-migrate-to-postgres: SQLite: DROP INDEX report_hosts_by_host;
<31>Oct 17 09:26:14 gvm-migrate-to-postgres: SQLite: CREATE INDEX report_hosts_by_host ON report_hosts (host);
<31>Oct 17 09:26:14 gvm-migrate-to-postgres: SQLite: PRAGMA integrity_check;
<28>Oct 17 09:26:15 gvm-migrate-to-postgres: Database integrity check failed.
<29>Oct 17 09:26:15 gvm-migrate-to-postgres: Corrupt database, skipping migration.

i found nothing here about the error “integrity check failed”.

maybe someone can help me?

GVM versions

gsa: 8.0.1
gvm: 8.0.1
openvas-scanner: 6.0.1
gvm-libs: 10.0.1
gvm-tools: 2.0.0.beta1

Environment Greenbone Server

**Operating system: Ubuntu Server 18.4
**Kernel: 4.15.0-58-generic
**Installation method / source: Github

Your sqlite3 database got corrupted. Nothing we can do about it.

okay… but why is it possible that the database got corrupted? and why is all other work with an corrupted database? @bricks is there a way to fix this?

and… i just want to migrate the sqlite3 to postgres because i have the hope that i can install the new gvm11 over the gvm10 without loosing all tasks/reports/alerts etc… you can say me if thats possible?

To “repair” the database i have used the .dump sqlite command now:
go to the db file, in my setup /home/gvm/install/var/lib/gvm/gvmd/
there should be a “gvmd.db” file
and than run this command:
sqlite3 gvmd.db “.dump” > gvmd.dmp
than remove the gvmd.db with “rm gvmd.db”
change the name from the gvmd.dmp “mv gvmd.dmp gvmd.db”
and then read in the new gvmd.db with this command:

.read gvmd.db


It seems this is a known process to recover a corrupted SQLite Database:

1 Like