MySQL to PostgreSQL migration of existing server

General
1

Hi,
we have relatively large server with 34TB DICOM images and we found that MySQL database is larger than 120GB. Is there an easy way to migrate from MySQL to PostgreSQL? Re-importing will be really slow process and if possible we would like to avoid it.

2

Hello,

The only official way of migrating from MySQL to PostgreSQL is to apply the replication process. Favour the “direct access to the filesystem” approach, as it should be the fastest and the simplest (no need to keep the original Orthanc server with MySQL running).

We don’t provide any SQL export script, as this would require careful validation and quality assurance, but feel free to contribute.

Kind Regards,
Sébastien-

1 Like
3

Note that the DB won’t be smaller with PostgreSQL.

1 Like
4

Hi Sebastien/Alain,

We have a similar case and I would like to confirm the migration process. I choose to continue this thread instead of creating a new one, but feel free to migrate it to a new thread if preferred.

We have 23TB of images, with Mysql plugin, and are studying passing to Postgres in order to be able to used the advanced storage plugin (a long time awaited gift).

Since we have direct calls from our existing HIS Mariadb database to Orthanc DB, we need to solve other matters too, but we are investigating seriously migrating to Postgres for OrthanC.

Is this plan the correct way to proceed?

  1. Stop OrthanC
  2. Rename mysql.json to mysql.json.bak
  3. Create and configure the postgres.json
  4. Start OrthanC service
  5. Call the script python ImportDicomFiles.py 127.0.0.1 8042 OrthancStorage

Other additional questions:

  • Any idea of how long the import could take. I assume it depends on disk and CPU, but any average? Server is Dell R720 and disks as 5300 RPM in RAID5 ;(
  • Is it correct that while the import takes place, OrthanC will be working to receive new studies sent by equipment and users will be able to access those new studies
  • If the import stops for any reason, it can be restarted (and will import only not yet imported studies)
  • Storage and Index directories are at the same location. Should the index directory need to be best changed? Or will the existing index be used at import time?
  • And finally, with advanced storage, if I define 2 storage (“dicom1” and “dicom2”) having “dicom2” as CurrentWriteStorage, will the “import” process reviewing “dicom1” (too) ? Should I call the script python ImportDicomFiles.py 127.0.0.1 8042 dicom1 ?

Also, is there any timeline defined to make advanced storage plugin compatible with mysql? If you need cooperation in MySQL stuff (SQL), please let me know and I will be happy to help.

Many thanks in advance for your help, and cheers from Nicaragua.

5

Hi @csimon3

I would recommend this kind of migration procedure:

  • keep your Orthanc with MySQL active but change the ports to e.g 8041 and 4241
  • initiate a new Orthanc with PG with a new StorageDirectory → this becomes the new active Orthanc and will immediately receive and display new studies.
  • start an OrthancCloner and use a --persist-state-path so it can restart where it stopped in case it is interrupted (the ImportDicomFiles.py does not recover if it is interrupted)

If dicom1 is the old MySQL storage, there is no need for the PG Orthanc to know about it and it is really important to have separate folders so you can easily get rid of the MySQL storage once the migration is complete.

No timeline sorry. The work is not huge because the PG and MySQL plugins share a lot of code but it is a matter of budget… with the current donations being quite low we will basically implement it only once we find someone who agrees to pay for a MySQL plugin update and release …

Hope this helps,

Alain

6

Hi Alain,

Thank you very much for the very constructive and detailed feedback.

Could you send me by private mail what would be the cost to implement advanced storage in MySQL?

Have a nice day,

Cedric.