Migrate from SQLlite to Postgres

Archive Greenbone Community Edition
1

Hello,

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

2

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

3

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?

4

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:
sqlite3

.read gvmd.db

2 Likes
5

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

1 Like