Convert from SQLite to PostgreSQL

[TM Co]

Hi everyone. I uploaded a bunch of DICOMs onto an Orthanc server and I did so using SQLite, however, I would prefer to use PostgreSQL. Is there an easy way to move the files over other than uploading the files again since that would take way to long. Thank you.

[Sébastien Jodogne]

Hello,

The only official procedure is to reimport the "OrthancStorage" (if you have direct access to the filesystem), or to use the "Replicate.py" script to duplicate from one running instance of Orthanc to another using the REST API:

https://book.orthanc-server.com/users/replication.html

That being said, the SQLite and the PostgreSQL database schemas are so close to each other than it should be fairly easy to make a SQL import/export between them, e.g.:

https://shravan-kuchkula.github.io/sql/sqlite-to-postgres/

https://pgloader.readthedocs.io/en/latest/index.html

You could start Orthanc with a PostgreSQL database so as to properly initialize an empty database (including triggers), then stop Orthanc and import the data by tools such as above. Make sure *not* to overwrite the content of the "GlobalProperties" table, as it contains system values. There could also be a challenge with AUTOINCREMENT columns.

If you have success with such a procedure, please contribute to Orthanc by sharing your story with the community. This could be useful to many people.

Regards,

Sébastien-

[TM Co]

Would I be able to do the second method you suggested if I'm using Docker containers? I currently have a Docker container for Orthanc and one for PostgreSQL.

[Sébastien Jodogne]

Nothing would prevent you from doing so.

[James Howard]

Just to say I had no success doing an SQLite to Postgres migration at the database level - even once I'd solved the issues with not overwriting the GlobalProperties tag and ensuring many of the integer tags in other tables are bigints.

In the end I ended up reworking the ImportDicomFiles.py script so that it is better suited towards a database migration and an order of magnitude faster: https://gist.github.com/jphdotam/21581fc4a205072ecf30d2c0c846f117