How to migrate Postgres Database

[schimpke]

There should be a section in the documentation what to do , if the version of the database changes (eg 12 to 13.3) .
Even upgrading 21.4.0-v5 to 21.4.2-v1 does not work seem to work without some magical command (I tried a quick pg_dump -O gvmd > gsvmd.sql followed by psql -U gvm -f gvmd.sql ... in the new container without success...

[schimpke]

Hi,
it seems, that I finally managed to migrate the database from 12.x to 13.y. Here are the steps:

• On the 12.x side sop all relevant processes (gvmd...)
• connect to the database as postgres user
• Dump the gvmd schema: pg_dump -O /path/to/gvmd.sql gvmd
• Copy the gvmd.sql into the filesystem of the new container

On the 13.y database:

• Make sure you can see the dump fiule created in the first step
• Again: shut down all relevant provesses (gvmd...) Everything that could try to connect to the database
• Connect as postgres user to the template1 database
• Drop gvmd schema: drop database gvmd;
• Create new & empty gvmd schema: create database gvmd owner gvm;
• Disconnect from the database
• As user gvm import the dump: psql -U gvm -f /path/to/gvmd.sql gvmd
• After import run: gvmd -v -f --migrate as user gvm. I Think this did nothing for me. It just logged, that the database is already at the correct version, what seems to be somehow strange

If you do not drop the schema in the target database, then you receive errors regarding foreign key violations and the import fails. So dropping seems to be essential. Further I got one error about a missing object in the public schema during the import. I still have to investigate this issue. But at least all old scan results, host, network and task definitions seem to be present. My colleagues will make some tests now.

Regards,
Thomas

[Dexus]

For further support, use https://github.com/DeineAgenturUG/greenbone-gvm-openvas-for-docker

[Dexus]

Here we have now a image with the upgrade tool. That works.
DeineAgenturUG/greenbone-gvm-openvas-for-docker#16 (comment)