Dear All,
We need advice on the correct, supported way to migrate our Orthanc index from the built-in SQLite to PostgreSQL. We have already rehearsed on a staging server and solved
most of the setup, but we are stuck on the data copy step and want to confirm the right approach before touching production. I’ll describe our goal, our environment,
exactly what we did, where we’re stuck, and our proposed plan.
-
Why we want to migrate
Several users run heavy operations in parallel (DICOM uploads, /modify, change-patient, bulk operations). On SQLite these serialize on the single writer and block each
other badly (one large change-patient of ~5,000 instances blocked everything for hours, and Orthanc eventually spun at 100% CPU). We want PostgreSQL with multiple writers
(ReadCommitted) so these operations no longer block each other. -
Environment
- Orthanc 1.12.10, Docker image orthancteam/orthanc:latest-full.
- Current index: built-in SQLite.
- Storage: Azure Blob Storage plugin v2.3.1 (object storage, HybridMode disabled). This is NOT a local StorageDirectory.
- Target DB: Azure Database for PostgreSQL – Flexible Server, PostgreSQL 16.
- DB plugins available in the image: postgresql-index 10.0, postgresql-storage 10.0 (we enable index only, storage stays on Azure Blob).
- Data size: staging ≈ 31,000 instances / 42 studies; production ≈ 396,000 instances / 630 studies.
-
Critical constraint — data stored only in the Orthanc DB
We make heavy use of UserMetadata (keys 1024–1031) (custom values: folder name, subspeciality/modality slugs, faculty information, etc.) and Orthanc labels. These drive
our application. We understand these live in the Orthanc database, not in the DICOM files, so any method that rebuilds the index from the files would lose them. Preserving
(or restoring) them is mandatory for us. -
What we have done so far (works)
- Created the Azure PostgreSQL Flexible Server, allow-listed the pg_trgm extension (via the azure.extensions server parameter).
- Added this to orthanc.json (index only; storage stays Azure Blob):
“PostgreSQL”: {
“EnableIndex”: true, “EnableStorage”: false,
“Host”: “…”, “Port”: 5432, “Database”: “orthanc”, “Username”: “orthanc”, “Password”: “…”,
“EnableSsl”: true, “Lock”: false, “IndexConnectionsCount”: 5,
“TransactionMode”: “ReadCommitted”, “MaximumConnectionRetries”: 20, “ConnectionRetryInterval”: 5
} - Orthanc connects to PostgreSQL and creates the schema successfully (“PostgreSQL is creating the database schema”, “Database revision … is 10”, “Orthanc has started”),
using READ COMMITTED with 5 connections. So the PostgreSQL setup itself is fine; we just have an empty index at this point.
-
Where we are stuck — copying the existing SQLite index
We tried pgloader 3.6.7 to copy the existing SQLite index into the freshly-created PostgreSQL schema (data only, keeping the Orthanc-created tables):
LOAD DATABASE
FROM sqlite:///data/index
INTO postgresql://orthanc:***@:5432/orthanc
WITH data only, create no tables, reset sequences, disable triggers
EXCLUDING TABLE NAMES LIKE ‘GlobalProperties’;
It connects and starts, warns about type casts (e.g. bigint vs integer, text vs varchar), then fails with:
pgloader failed to find column “public”.“AttachedFiles”.“uncompressedmd5”
in target table “public”.“attachedfiles”
So the SQLite schema column AttachedFiles.uncompressedMD5 / compressedMD5 does not exist in the PostgreSQL schema (which appears to use uncompressedhash / compressedhash).
The two schemas are not column-identical, so a plain pgloader copy cannot map them — and we expect further mismatches in other tables. -
Other methods we evaluated (from the forum)
- OrthancCloner (python-orthanc-tools): re-ingests the data, which creates new storage paths (would rewrite all our Azure Blob objects — very heavy for 396k instances) and
loses labels/metadata. Not suitable. - Advanced Storage plugin re-index: recommended as the modern approach, but the official sqlite-to-postgresql sample uses a local StorageDirectory, and re-indexing
rebuilds from the files, which would lose our UserMetadata + labels.
-
Our proposed plan (please validate)
Because our UserMetadata (slugs/faculty/folder) is also stored in our application’s MySQL database, we are considering: use the Advanced Storage re-index to rebuild the
PostgreSQL index from the existing Azure Blob storage, then re-apply the UserMetadata (1026/1027/1028/1030/1031) and labels from our MySQL database via the REST API (PUT
/studies/{id}/metadata/{key} and PUT /studies/{id}/labels/{label}). We would keep the SQLite index and the Azure Blob storage untouched for rollback. -
Our questions
-
What is the recommended, supported way to migrate an existing SQLite index to PostgreSQL that preserves (or lets us restore) UserMetadata and labels, when storage is
the Azure Blob plugin rather than a local directory? -
Does the Advanced Storage re-index method work with the Azure Blob storage plugin (object storage), or only with a local StorageDirectory?
-
Does the re-index method preserve UserMetadata and labels? If not, is re-applying them afterward from our own external DB (via REST) a sound and supported approach?
-
Is pgloader supported given the SQLite-vs-PostgreSQL column-name differences (e.g. uncompressedMD5 vs uncompressedhash)? If so, is there a maintained column-mapping /
-
Is there any first-party tool that migrates the index without re-ingesting and without losing metadata/labels, suitable for object storage?
-
For ~400,000 instances, what downtime/duration should we expect for the recommended method?
-
Can you confirm that TransactionMode: ReadCommitted + Lock: false + IndexConnectionsCount > 1 is the correct configuration for a single Orthanc instance that must
handle multiple parallel write operations (not multiple Orthanc instances)?
Our SQLite index and Azure Blob storage are fully backed up and untouched, so we can safely test any suggested method on staging. Thank you very much for your guidance.