How can I migrate my data in DD MySQL to DD Postgres, using the Docker Images

[jasgggit]

Hello all,
I'm having a bit of difficulty in migrating all my data from the older deployment using the DD Docket using MySQL-RabitMq to the new DD using Postgres-(whatever):

Also, how can I use the external Postgres Server that we have in production?

Ok, confusing, lets drill down this:

1 - We need to export / Migrate the data that we already have in the DD PROD environment, with some tests and findings and so on, to a Postgres external DB.
Q: - How can this be done?

2 - We need to install the DD using the external Postgres DB. I have found already some info about this but its a bit confusing where all the info should be configured.

• On the Docker compose yml with the environment variables defined there?
• On the external (which one?) configuration file?

3 - Can we streamline the docker-compose file so we use only the configuration that we need (Postgres and so on), instead all teh confusion of the profiles?

• If so, how? - Trial and error until we get it?

Thanks in advance for your support.

Jorge Gomes

[manuel-sommer]

@quirinziessler , maybe you can help here

[quirinziessler]

Hi @jasgggit

I actually had nearly the same situation as you a few months ago. I will try to answer your question.
First of all I can really recommend you the postgres-redis setup. It's running super smooth in a 50-user and 200k findings setup. I am using aws aurora as DB provider which I can also really recommend.

Regarding 1.:
It depends on how big your database is currently. DD is built on DJango which comes with a function called "dumpdata". If you run docker exec -it uwsgi python manage.py dumpdata > dumpfile.json and then create your ne Postgres-Redis setup you can simply import the data from the file the same way. Please follow the official Django Docs for more details on this.
For me this didn't work as the database was too large and the dumpdata process got killed after 5 mins. That's why I looked for another solution and created a script for my purpose. The script works on base of the Django models and writes a duplicate of the current database to a second Database. As the DBs are mounted inside Django it will take care of the different DB types. I published the script here. You may have to do some adjustments but it works quite easy. If you get some errors because of depending models, simply add a if-clause that skips the model and re-run it a few times. Only make sure no one is using DD in that moment because of consistency.

Regarding 2:
Just pass DD_DATABASE_URL via the docker env to the compose file (clone repo, edit docker/environment/postgres-redis.env). Or pass it in any other way.

Regarding 3:
Use the docker env files as suggested within Answer 2.

Hope this helps you a bit.

[jasgggit]

Hello and thanj you, I'll try your suggestions and I'll report back ASAP.
If this is successful I'll write a How to!.
Best regards.
JG

[mtesauro]

+1 on PostgreSQL & Redis

We will shortly be deprecating MySQL and RabbitMQ since those appear to be the best combination of supported tech.

[jasgggit]

Hi and sorry for this ...

How can I import the DB dumped with:

docker exec -it django-defectdojo-uwsgi-1 python manage.py dumpdata > dumpfile.json ?

This is 75MB file so is not huge ... but can't figure how, tried with:

docker exec -it django-defectdojo-uwsgi-1 python manage.py loaddata --format=json -v 3 - dumpfile.json

But it stalls saying:

[19/Feb/2024 18:23:06] INFO [dojo.models:4505] enabling audit logging
/usr/local/lib/python3.11/site-packages/coreapi/codecs/download.py:5: DeprecationWarning: 'cgi' is deprecated and slated for removal in Python 3.13
import cgi
Installing json fixture '-' from absolute path.

And nothing more ...

Thanks in advance.
JG

[quirinziessler]

Hi,

the loaddata command is the right one. Why did you add "--format=json -v 3 -" to it? The "-" at the end defines you want to load the "-" Django fixture but you want to load all the data in the dumpfile. So simply "python manage.py loaddata dumpfile.json" should work for you.

[jasgggit]

@quirinziessler Hi, I did, the '-v3' should be verbose 3 ... this is what it outputs:

/opt/containers/django-DefectDojo # docker exec -it django-defectdojo-uwsgi-1 python manage.py loaddata -v 3 dumpfile.json
[19/Feb/2024 21:20:52] INFO [dojo.models:4505] enabling audit logging
/usr/local/lib/python3.11/site-packages/coreapi/codecs/download.py:5: DeprecationWarning: 'cgi' is deprecated and slated for removal in Python 3.13
import cgi
Loading 'dumpfile' fixtures...
Checking '/app/dojo/fixtures' for fixtures...
No fixture 'dumpfile' in '/app/dojo/fixtures'.
Checking '/app' for fixtures...
No fixture 'dumpfile' in '/app'.
CommandError: No fixture named 'dumpfile' found.

Can't figure out what the issue is ....

[mtesauro]

About this:

3 - Can we streamline the docker-compose file so we use only the configuration that we need (Postgres and so on), instead all teh confusion of the profiles?

An example of that is available in the Community Contribs repo:
https://github.com/DefectDojo/Community-Contribs/tree/master/CentOS-7-deploy

If you navigate into the dojo directory in that part of Community Contribs, you can see a 'simplified' version of compose at:
https://github.com/DefectDojo/Community-Contribs/blob/master/CentOS-7-deploy/dojo/docker-compose.yml
though the image references will need to be updated

[jasgggit]

@mtesauro Hi and thank you for this insight, although there are substantial changes on DD for this to work properly I managed to adapt a version to work on our DEV env.
The only question that I have I can't figure out is the import of the dumped info into the Django DB.

It keeps giving the error of the fixtures missing ...

docker compose --env-file "./docker/environments/postgres-redis.env" exec -it uwsgi python manage.py loaddata -v 3 dumpfile /app/dojo/models.py:4343: ManagerInheritanceWarning: PolymorphicModel: "Question.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses class Question(PolymorphicModel, TimeStampedModel): /app/dojo/models.py:4474: ManagerInheritanceWarning: PolymorphicModel: "Answer.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses class Answer(PolymorphicModel, TimeStampedModel): [21/Feb/2024 17:22:38] INFO [dojo.models:4513] enabling audit logging /usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager warnings.warn( /usr/local/lib/python3.11/site-packages/coreapi/codecs/download.py:5: DeprecationWarning: 'cgi' is deprecated and slated for removal in Python 3.13 import cgi /usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager warnings.warn( Loading 'dumpfile' fixtures... Checking '/app/dojo/fixtures' for fixtures... No fixture 'dumpfile' in '/app/dojo/fixtures'. Checking '/app' for fixtures... No fixture 'dumpfile' in '/app'.

Can you share some light on this ?

Thanks in advance.
JG

[mtesauro]

I haven't had consistent results using the Django fixtures. If you're going from one DB to another e.g. PostgreSQL to Postgres (just running in different places), then I've had luck with just using the 'normal' PostgreSQL tools.

Something like:

docker exec [CONTAINER] -u postgres /usr/bin/pg_dump [DATABASE] > backup.sql

to create the backup. Then move that .sql file over to the new location and do something like:

psql -h [HOST OR IP] -p 5432 -d [DATABASE] -U [DB USER] -f backup.sql

Just make sure and stop the nginx container or otherwise make sure users don't try to change data during the backup and restore operations.

If you Google for pg_backup, there's loads of resources and different ways to use the 'native' PostgreSQL tools to backup and relocate DB data.

HTH

[jasgggit]

@mtesauro the issue here is that I'm trying to export from a Mysql to import on a Postgres ...
I'm still trying to execute the below suggestions from @quirinziessler but it's still giving me the same errors, trying to figure out the issue, I'll let you all know if I can find it, or not.
Thanks for all the support.
JG

[quirinziessler]

Hi @jasgggit

I just tried to reproduce your case. However dumping and loading the file works fine for me. Could you please check if the dumpfile is loaded inside the container?

Just a side note: The command I sent you saved the file in the current directory (not inside the docker container/volume). In order to have it inside the container, move it to one of the defectdojo volumes e.g. media. Another option would be to login to the uwsgi container and drop it directly to media. After configuring with Postgres login again and upload from media.

[jasgggit]

@quirinziessler Actually I was following the same principle that if the dump was written to the local FS it can also be loaded from it. So to answer the question no, I wasnt placing the file inside the container, although I kept remembering that it should be ...
I'm going to do it now and test it ... brb

[jasgggit]

So ...

docker compose --env-file "./docker/environments/postgres-redis.env" cp dumpfile.json uwsgi:/media/.
docker compose --env-file "./docker/environments/postgres-redis.env" exec -it uwsgi python manage.py loaddata -v 3 /media/dumpfile.json

Outputs:
/app/dojo/models.py:4343: ManagerInheritanceWarning: PolymorphicModel: "Question.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses
class Question(PolymorphicModel, TimeStampedModel):
/app/dojo/models.py:4474: ManagerInheritanceWarning: PolymorphicModel: "Answer.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses
class Answer(PolymorphicModel, TimeStampedModel):
[21/Feb/2024 21:29:55] INFO [dojo.models:4513] enabling audit logging
/usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager
warnings.warn(
/usr/local/lib/python3.11/site-packages/coreapi/codecs/download.py:5: DeprecationWarning: 'cgi' is deprecated and slated for removal in Python 3.13
import cgi
/usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager
warnings.warn(
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: jgomes
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: tcardoso
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: pcunha
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: jsantos
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: tqueiros
[21/Feb/2024 21:30:01] INFO [dojo.utils:1757] creating default set of notifications for: jines
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(watson, searchentry) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/app/manage.py", line 11, in
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.11/site-packages/django/core/management/init.py", line 446, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.11/site-packages/django/core/management/init.py", line 440, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 102, in handle
self.loaddata(fixture_labels)
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 163, in loaddata
self.load_label(fixture_label)
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 253, in load_label
if self.save_obj(obj):
^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 209, in save_obj
obj.save(using=self.using)
File "/usr/local/lib/python3.11/site-packages/django/core/serializers/base.py", line 288, in save
models.Model.save_base(self.object, using=using, raw=True, **kwargs)
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 863, in save_base
updated = self._save_table(
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 976, in _save_table
updated = self._do_update(
^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 1040, in _do_update
return filtered._update(values) > 0
^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1216, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1822, in execute_sql
cursor = super().execute_sql(result_type)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1398, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.IntegrityError: Problem installing fixture '/media/dumpfile.json': Could not load contenttypes.ContentType(pk=107): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(watson, searchentry) already exists.

Does it need to be empty? like a new DB with no initialization?

[quirinziessler]

I just tested it and it worked even I had the exact values already present. Here are the steps I was sucessfully going trough. Maybe this helps you a bit.

1. login to uwsgi docker exec -it django-defectdojo-uwsgi-1 bash
2. exec dumpdata python manage.py dumpdata media/dumpdata.json
3. change setting to your new db setup
4. exec loaddata python manage.py loaddata media/dumpdata.json

If watson is the only foreign key issue comming up you may can try to run python manage.py uninstallwatson, load the data and run python manage.py installwatson afterwards. Maybe this solves it. Otherwise do a clean via python manage.py flush. But keep in mind that this deletes everything.

[jasgggit]

@quirinziessler Thank you for your good steps but unfortunately, they seem only to work on the same instance.
Manning that I dump and load the data file on the same container it works as you say, but if I try to load my dumped data from the other container, the one that has the MYSQL DB, it shows the following errors:

BTW, I flush the DB prior to the load, and this is to be a MySQL to PostGRES dump and load.

f0ae910a19fe:/app$ python manage.py loaddata media/dumpdata.json
/app/dojo/models.py:4343: ManagerInheritanceWarning: PolymorphicModel: "Question.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses
class Question(PolymorphicModel, TimeStampedModel):
/app/dojo/models.py:4474: ManagerInheritanceWarning: PolymorphicModel: "Answer.objects" manager is of type "Manager", but must be a subclass of PolymorphicManager. to support retrieving subclasses
class Answer(PolymorphicModel, TimeStampedModel):
[22/Feb/2024 10:01:29] INFO [dojo.models:4513] enabling audit logging
/usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager
warnings.warn(
/usr/local/lib/python3.11/site-packages/coreapi/codecs/download.py:5: DeprecationWarning: 'cgi' is deprecated and slated for removal in Python 3.13
import cgi
/usr/local/lib/python3.11/site-packages/polymorphic/base.py:197: ManagerInheritanceWarning: PolymorphicModelBase._default_manager is not a PolymorphicManager
warnings.warn(
[22/Feb/2024 10:01:34] INFO [dojo.utils:1757] creating default set of notifications for: admin
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(dojo, benchmark_category) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/app/manage.py", line 11, in
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.11/site-packages/django/core/management/init.py", line 446, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.11/site-packages/django/core/management/init.py", line 440, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 102, in handle
self.loaddata(fixture_labels)
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 163, in loaddata
self.load_label(fixture_label)
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 253, in load_label
if self.save_obj(obj):
^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 209, in save_obj
obj.save(using=self.using)
File "/usr/local/lib/python3.11/site-packages/django/core/serializers/base.py", line 288, in save
models.Model.save_base(self.object, using=using, raw=True, **kwargs)
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 863, in save_base
updated = self._save_table(
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 976, in _save_table
updated = self._do_update(
^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 1040, in _do_update
return filtered._update(values) > 0
^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1216, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1822, in execute_sql
cursor = super().execute_sql(result_type)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1398, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.IntegrityError: Problem installing fixture '/app/media/dumpdata.json': Could not load contenttypes.ContentType(pk=9): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(dojo, benchmark_category) already exists.

Thanks for the support.

[jasgggit]

@mtesauro @quirinziessler Hello,

So, for the past 2 days, I've been trying to migrate my small tests DB from a setup made in 2022 using docker and MySQL-RabbitMQ.
Using your suggestions I was able to understand a bot how this could work and somehow to be accomplished.

So far I wasn't able to import the JSON dump file, either to a Mysql or Postgres deployed DB. It keeps giving the above errors and nothing is imported.
1- So, I tried the use of MySQL to Postgres migration using the 'dimitri/pgloader', after exporting the MySQL database to a SQL dump file using 'defecdojo' user and pwd, but this throws an error in the exported file saying that the 'defectdojo' user has no permissions to dump some tablespaces, but exports somehow the DB.

File error:"mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces".

2- After the exported DB, I redeployed the DEV setup, using the profiles, and a MySQL-Redis setup, and this time I took note of the 'root' pwd generated during the process, imported the MySQL dump file, and all my products and engagements with the tests and findings are there. So far so good.

3- Then I exported that DB, this time using the 'root' user, with no errors on the dump, and tried to migrate that to the version of DD with the Postgres DB, using the above-mentioned utility, with no errors on the migration process, and when I open the DD all the products and engagements and tests are there, with the exception o the contents of the findings in the tests!. This is the part I need the most because using this allows me to compare the findings with the new tests added after, and compare them, DD is useless for me without this, and unfortunately, I have no original tests to reimport because we lost them when we lost the Nessus server.
What happens:
When I click on the test to see its findings it gives me an HTTP 500 error saying "wee, this was unexpected, 500 Internal Server Error", and I can't figure out where the issue is.

Comparing both deployments using the original MySQL dump file, used to recreate to new MySQL DB on DEV:

• This does not happen if I use the MySQL profile, only with the Postgres one.

Can you help me finish this? Any ideas on how I might see this through?

Best regards.
JG

[jasgggit]

Hello all, the problem has been solved by one of my colleagues, we know more about Postgres than I do, and he found the issue of the import of the DUMP JSON file was related to the 'Sequences', on the 'auditlog_logentry_id_seq'.
It needs to be changed from the default '1' to something greater than the one on the JSON file.
Then we can use the commands to load the JSON after we flush it first.

This way all is imported.

Thanks for the support.

Regards.

JG

[StephanPillhofer]

@jasgggit , thank you for your initial work on migrating DefectDojo to Postgres.

Would you be open to marking my comment as the official answer on the MySQL-to-Postgres migration? It offers a comprehensive guide based on my own migration experience and addresses all known issues encountered in this process so far. This would make it easier for others to quickly find a reliable guide for completing the migration and troubleshooting specific errors. I also plan to keep the guide updated when new issues arise. Let me know how you feel about it.

[mtesauro]

For future readers of this discussion, there's some good info in OWASP's Slack instance in the #defectdojo channel.

I'm summarizing greatly here but one suggestion I found interesting was to use mysqldump to get the schema/data from MySQL then use a tool like pgLoader to convert the mysqldump to Postgres.

Anyway, hope that helps those that want/need to migrate.

[jasgggit]

@mtesauro, thank you

[navzen2000]

Well, it just does not work for me either:

bash-4.4$ python3 manage.py loaddata media/dojo.json
[07/Jun/2024 11:53:28] INFO [dojo.models:4295] enabling audit logging


Traceback (most recent call last):
  File "/app/manage.py", line 11, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 102, in handle
    self.loaddata(fixture_labels)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 163, in loaddata
    self.load_label(fixture_label)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 251, in load_label
    for obj in objects:
  File "/usr/local/lib/python3.11/site-packages/tagulous/serializers/base.py", line 51, in wrapper
    for obj in obj_generator:
  File "/usr/local/lib/python3.11/site-packages/django/core/serializers/json.py", line 67, in Deserializer
    stream_or_string = stream_or_string.decode()
                       ^^^^^^^^^^^^^^^^^^^^^^^^^
MemoryError: Problem installing fixture '/app/media/dojo.json':

My DB dump from mysql is 21 G that am trying to load into postgres

$ du -shc media/dojo.json
21G     media/dojo.json
21G     total

[mtesauro]

So this is using Django fixtures?

TBH, beyond loading default data, I've not had great success with Django fixtures.

Did you try pgLoader mentioned ☝️ ?

FWIW, when I've done big migrations from one DB vendor to another using a SQL dump has been much more successful. You likely have to deal only with SQL syntax differences which usually are a smaller problem set. Like I said, I've not used pgLoader but I suspect it's doing that programmatically for you.

[mtesauro]

There's also this answer about Postgres Sequences which needed to be adjusted for the JSON fixtures to be imported - #9480 (comment)

[StephanPillhofer]

THE FOLLOWING PROCEDURE HAS BEEN REVISED BASED ON FEEDBACK RECEIVED ON MY ORIGINAL ANSWER AND ISSUES ENCOUNTERED BY OTHER USERS WITHIN THIS DISCUSSION

---

I can confirm that the tool pgLoader works effectively and is a recommended solution for this migration!

For a day and a half, I attempted to migrate using Django's dumpdata and loaddata functionalities but kept encountering duplicate key violations. I managed to bypass some errors by excluding specific tables from the dumpdata command and adjusting the auditlog_logentry_id_seq value, yet duplicate key errors persisted.

So in summary, this is the successful approach I used for migrating DefectDojo with Docker:

1. Stop your current DefectDojo (DD) MySQL stack and deploy the new PostgreSQL DD stack. Ensure the PostgreSQL and MySQL stacks are on the same or at least similar DD versions; mismatched versions may lead to migration failures due to structural database differences between DD versions. The last DD version with MySQL support is 2.36.6.

2. Restart only the old MySQL container and join it to the network of the new PostgreSQL stack (if not already in the same network).

3. Create a mysql_to_pg.load file with the following configuration (replace container IDs and credentials as needed):

LOAD DATABASE
     FROM mysql://defectdojo:defectdojo@django-defectdojo-2170-mysql-1/defectdojo
     INTO postgresql://defectdojo:defectdojo@django-defectdojo-postgres-1/defectdojo

WITH prefetch rows = 100, max parallel create index = 1, truncate, reset sequences, quote identifiers, data only

excluding table names matching ~/auth_permission/, ~/django_migrations/, ~/django_content_type/, ~/django_site/
;

• This configuration copies only the data, leaving existing table structures in the new PostgreSQL database unmodified. Without the data only property, some columns may be created with incorrect data types (e.g., jsonb instead of json) within PostgreSQL, resulting in 500 errors.

4. Access the uwsgi container using:

docker exec -it django-defectdojo-uwsgi-1 bash

5. Clear any prefilled values in the database using python manage.py flush. Then, verify the current DB schema name using python manage.py dbshell in the uwsgi container. If the schema name is "public," change it to "defectdojo":

ALTER SCHEMA public RENAME TO defectdojo;

6. Start the migration with the following command (adjust the network name as needed):

docker run --rm --network django-defectdojo_default -v $(pwd):/pgloader dimitri/pgloader:latest pgloader /pgloader/mysql_to_pg.load

• Ignore any warnings as long as the migration still completes successfully without any errors. Once complete, stop the old MySQL container and verify that all data is accessible in the new stack.

7. If migration errors arise due to watson_searchentry, add it to the list of excludet tables in step 3. After a successful migration, use python manage.py buildwatson to recreate this table (note that it may take some time).

8. For large databases and if you do not need the audit log feature of DD, consider also excluding auditlog_entry, as it is typically the largest table.

9. Finally, in my case, the postgres sequences were not reset correctly (even though the data only should not effect reset sequences), so lastly we need to reset them manually. Enter the container again (see step 4) and open a psql shell again using python manage.py dbshell, copy and paste this script into the command line and copy its output. Finally execute the copied sql statements inside the shell. You shouldn't see any errors and the correct values of the sequences should be displayed.

You're all set! Explore DefectDojo to ensure functionality and that no 500 errors appear.

---

I hope this helps others! Please share any feedback on how these steps could be refined, especially if there’s a way to avoid using the data only property while preserving correct data types in the DB during migration, which would also remove the need for manual sequence fixes.

[lfama]

That's a nice catch thank you!
I'll try to exclude this table as well and follow your instructions about recreating it, together with the postgres sequences. Hopefully it will work.. :D

[lfama]

@StephanPillhofer Thank you or your great support! pgloader terminated without any error (so it was the watson_searchentry as you found out). Now I'm rebuilding the search indexes and it's taking some time (but I believe it's ok).
Thank you again for helping me out!

[lfama]

Finally (after more than 15 hours..) the buildwatson command terminated. I was also able to run the script to fix the postgres sequences and everything seems working fine.

Last question: let's say I'd like to migrate this postgres db to a different machine, it should be enough to dump and restore the database as we could do for any postgres db migration right? (something like this)
I've tested locally and seems it worked fine.

[StephanPillhofer]

That's great! Let's hope you don't run into any 500er errors in the app ;)

I have never done that before but I don't see any problems why it shouldn't work :)

[mtesauro]

@lfama FWIW, I've used pg_dump and pg_restore many, many times to move around DefectDojo databases without issues.

[alaaseeku]

I had the same issue, I loaded the data with this command and it works
./manage.py loaddata -v 3 --exclude auth.permission --exclude contenttypes media/dumpdata.json

[mikesindieiev]

I was trying to migrate django fixtures to postgres but got this weird error:

The steps I did:
On the mysql setup:

1. ./manage.py dumpdata contenttypes --verbosity 3 --indent=4 --natural-foreign --natural-primary -o contenttype.json
2. ./manage.py dumpdata --verbosity 3 --exclude contenttypes --indent=4 --natural-foreign --natural-primary -o data.json

Then, I copy contenttype.json and data.json from mysql setup to postgres

On the postgres setup:

1. ./manage.py sqlflush | ./manage.py dbshell
2. ./manage.py loaddata --verbosity 3 contenttype.json
3. ./manage.py loaddata --verbosity 3 data.json

I have tried to make a dump without --natural-foreign --natural-primary but got the same error.

I am trying to do that on defectdojo v2.36.4 running in k8s with official helm chart.

Could anyone suggest how to migrate django fixtures in this case?

[mikesindieiev]

I was able to migrate with pgloader

[drunkhathacker]

Hi, I am updating my defectdojo from v 2.30.1 to the latest release. My current setup uses MySQL as database. As I want to test the upgrade on my local environment first, I exported the .sql db file from the prod environment, set up my local environment in docker with latest DD and postgres and migrated using pgloader. Everything went smoothly except whenever I open a finding, I get 500 Internal Server error
On inspecting the container logs I get the following exception which I am unable to solve. Please guide me:

[14/Oct/2024 13:43:56] ERROR [dojo.finding.views:658] error_no_jira_project [14/Oct/2024 13:43:57] ERROR [django.request:241] Internal Server Error: /finding/1743 Traceback (most recent call last): File "/usr/local/lib/python3.11/site-packages/django/core/handlers/exception.py", line 55, in inner response = get_response(request) ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/core/handlers/base.py", line 197, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/views/generic/base.py", line 104, in view return self.dispatch(request, *args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/views/generic/base.py", line 143, in dispatch return handler(request, *args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/app/dojo/finding/views.py", line 775, in get return render(request, self.get_template(), context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/shortcuts.py", line 25, in render content = loader.render_to_string(template_name, context, request, using=using) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/loader.py", line 62, in render_to_string return template.render(context, request) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/backends/django.py", line 61, in render return self.template.render(context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 171, in render return self._render(context) ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 163, in _render return self.nodelist.render(context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in render return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in <listcomp> return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 961, in render_annotated return self.render(context) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/loader_tags.py", line 159, in render return compiled_parent._render(context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 163, in _render return self.nodelist.render(context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in render return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in <listcomp> return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 961, in render_annotated return self.render(context) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/loader_tags.py", line 65, in render result = block.nodelist.render(context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in render return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in <listcomp> return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 961, in render_annotated return self.render(context) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/defaulttags.py", line 326, in render return nodelist.render(context) ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in render return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 1000, in <listcomp> return SafeString("".join([node.render_annotated(context) for node in self])) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/base.py", line 961, in render_annotated return self.render(context) ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/template/defaulttags.py", line 325, in render if match: File "/usr/local/lib/python3.11/site-packages/django/core/paginator.py", line 191, in __len__ return len(self.object_list) ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 382, in __len__ self._fetch_all() File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1930, in _fetch_all self._prefetch_related_objects() File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1320, in _prefetch_related_objects prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups) File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 2381, in prefetch_related_objects obj_list, additional_lookups = prefetch_one_level( ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 2582, in prefetch_one_level all_related_objects = list(rel_qs) ^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 400, in __iter__ self._fetch_all() File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1928, in _fetch_all self._result_cache = list(self._iterable_class(self)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 123, in __iter__ for row in compiler.results_iter(results): File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1500, in apply_converters value = converter(value, expression, connection) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/django/db/models/fields/json.py", line 94, in from_db_value return json.loads(value, cls=self.decoder) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/json/__init__.py", line 339, in loads raise TypeError(f'the JSON object must be str, bytes or bytearray, ' TypeError: the JSON object must be str, bytes or bytearray, not dict

[StephanPillhofer]

based on your logs the migartion is failing for 4 models (including dojo_product) because the insertion into the new DB tables is violating a not-null constraint of certain columns. The affacted columns have been added in various releases after 2.30.1 and should be assigned a default value automatically in the migration process during the update of Defect Dojo to a newer version.

Therefore my guess is that you did not finish the update process (correctly) to the latest Defect Dojo version before starting the migration. Make sure that you update DefectDojo correctly and that all migrations are applied before trying to migrate to the new database. Todo this follow the instructions documented here: https://defectdojo.github.io/django-DefectDojo/getting_started/upgrading/

Take note of the following two instructions listed in the docs:

Database migrations will be run automatically by the initializer. Check the output via docker compose logs initializer or relevant k8s command

If you have the initializer disabled (or if you want to be on the safe side), run the migration command: docker compose exec uwsgi /bin/bash -c "python manage.py migrate"

[drunkhathacker]

So just to be clear here is the steps I need to follow (I have currently version 2.30.1 with mysql-rabbitmq):

1> Upgrade to a version of DD that supports Mysql (Because the latest version does not have MySql support, right?)
2> Let the initialiser make migrations
3> Install Postgres container
4> Migrate to Postgres using pgloader
5> Install latest DD and remove MySql version

[mikesindieiev]

Yes, you are right. However, you might need to purge the data from postgres before migration and reset postgres sequences after migration.

Edit: I was able to successfully migrate data with defectdojo v2.36.4

[drunkhathacker]

Thank you everyone. Using the guide I was able to upgrade and migrate from Mysql to Postgres. For those coming from an older version, first upgrade to an intermediate version that uses mysql like v2.36.4 and then update to the latest.

[StephanPillhofer]

@drunkhathacker Based on your issues I adjusted step 1 of my migration guide. Ty!

[drunkhathacker]

There is just one issue that I noticed after migrating to Postgres. So I have a code written that runs in jenkins that imports scan results everynight to DD. In MySQL, it worked normally as intended. But after upgrading to Postgres, I get 10k + new findings evrytime the import occurs. I have turned on the options to deduplicate the findings but no luck. Maybe anyone has had similar problem or an insight?

[StephanPillhofer]

Check the output of the deduplication logger after importing the new findings. It should provide detailed info why the deduplication did not work.

[drunkhathacker]

Ok so I ran the reimport with logger level set to debug. Looks like when using postgres, hashcode for around 300 findings is not being found. I will attach code snippets from the logger:

MySQL:

[19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:195] unsaved finding's hash_code: 9bd8cc477a96e5209a7faefd922c7aabfcc8fc0bc8785bde4bede83064161e90 [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:371] return findings bases on algorithm: legacy [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:198] found 1 findings matching with current new finding [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:2642] Hash_code already computed for finding [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:2148] HASHCODE_FIELDS_PER_SCANNER is: None [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:2765] No configuration for hash_code computation found; using default fields for dynamic scanners [19/Nov/2024 13:54:13] DEBUG [dojo.specific-loggers.deduplication:2806] compute_hash_code_legacy - fields_to_hash = SSLv3 Padding Oracle On Downgraded Legacy Encryption Vulnerability (POODLE)0NoneNoneIt is possible to obtain sensitive information from the remote host with SSL/TLS-enabled services.

Postgres for the same hash:

[19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:195] unsaved finding's hash_code: 9bd8cc477a96e5209a7faefd922c7aabfcc8fc0bc8785bde4bede83064161e90 [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:371] return findings bases on algorithm: legacy [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:198] found 0 findings matching with current new finding [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:2642] Hash_code already computed for finding [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:2148] HASHCODE_FIELDS_PER_SCANNER is: None [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:2765] No configuration for hash_code computation found; using default fields for dynamic scanners [19/Nov/2024 13:34:21] DEBUG [dojo.specific-loggers.deduplication:2806] compute_hash_code_legacy - fields_to_hash = SSLv3 Padding Oracle On Downgraded Legacy Encryption Vulnerability (POODLE)0NoneNoneIt is possible to obtain sensitive information from the remote host with SSL/TLS-enabled services.

Update: I just checked both databases for the finding with above mentioned hash, and it exists in both table.

[StephanPillhofer]

Thank you!

So I think you are right that some values must have changed during the migration. The algorithm is using those finding properties to find duplicates, however based on your provided migration logs, I think some test sub-property of the finding must have changed because I don't see any warnings regarding the other finding properties in your logs (title, severity and numerical_severity).

Can you use the swagger UI (/api/v2/oa3/swagger-ui/) to query a test using its id before migration and again after migration and compare the outputs? If they are different that could be the culprit. If they are the same, also look at the already mentioned properties title, severity and numerical_severity of a finding.

[drunkhathacker]

After diving deeper in the issue I found that the all the fields are the same for the original and duplicated findings before and after import. Another observation is that after the reimport the original finding as getting marked as mitigated and when the reimport occurs, a new finding is created because the old was mitigated(maybe this is the reason?). I already checked if the finding is getting marked as mitigated before reimporting, before migration everything but so is not the case.

Another observation is when i do the reimport without resetting the sequences, I get around 20 duplicate findings as compared to 300 after resetting sequences. But without resetting sequences i get 500 errors in some places.

[drunkhathacker]

Unfortunately, I still haven't been able to solve the issue. If anyone has any insights, that would be really helpful