Mercurial > hg > orthanc-book
annotate Sphinx/source/plugins/postgresql.rst @ 336:a5f7fc9fb611
Orthanc 1.6.0
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Wed, 18 Mar 2020 15:15:39 +0100 |
| parents | 6cbcdb965ad3 |
| children | 011b01ccf52d |
| rev | line source |
|---|---|
| 24 | 1 .. _postgresql: |
| 2 | |
| 3 | |
| 4 PostgreSQL plugins | |
| 5 ================== | |
| 6 | |
| 27 | 7 .. contents:: |
| 8 | |
| 9 The Orthanc project provides two **official** plugins to replace the | |
| 10 default storage area (on the filesystem) and the default SQLite index | |
| 11 by a PostgreSQL database. | |
| 12 | |
| 13 For general information, check out the `official homepage of the | |
| 14 plugins <http://www.orthanc-server.com/static.php?page=postgresql>`__. | |
| 15 | |
| 16 | |
| 17 | |
| 18 Compilation | |
| 19 ----------- | |
| 20 | |
| 128 | 21 Static linking |
| 22 ^^^^^^^^^^^^^^ | |
| 23 | |
| 27 | 24 .. highlight:: text |
| 25 | |
| 154 | 26 The procedure to compile these plugins is similar to that for the |
| 27 | 27 :ref:`core of Orthanc <compiling>`. The following commands should work |
| 154 | 28 for most UNIX-like distribution (including GNU/Linux):: |
| 27 | 29 |
| 154 | 30 $ mkdir BuildPostgreSQL |
| 31 $ cd BuildPostgreSQL | |
| 32 $ cmake ../PostgreSQL -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release | |
| 27 | 33 $ make |
| 34 | |
| 35 The compilation will produce 2 shared libraries, each containing one plugin for Orthanc: | |
| 36 | |
| 37 * ``OrthancPostgreSQLIndex`` replaces the default SQLite index of Orthanc by PostgreSQL. | |
| 38 * ``OrthancPostgreSQLStorage`` makes Orthanc store the DICOM files it receives into PostgreSQL. | |
| 39 | |
| 128 | 40 |
| 41 Microsoft Windows and Apple OS X | |
| 42 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 43 | |
| 28 | 44 Pre-compiled binaries for Microsoft Windows `are also available |
| 27 | 45 <http://www.orthanc-server.com/browse.php?path=/plugin-postgresql>`__. |
| 30 | 46 A package for `Apple's Mac OS X |
|
94
b0a71b880ca0
fix link + remark about disk usage overhead
Alain Mazy <alain@mazy.be>
parents:
81
diff
changeset
|
47 <http://www.osimis.io/en/download.html>`__ |
| 128 | 48 is available courtesy of `Osimis <http://osimis.io/>`__. |
| 27 | 49 |
| 50 | |
|
263
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
51 .. _postgresql-ubuntu1604: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
52 |
| 128 | 53 Dynamic linking on Ubuntu 16.04 |
| 54 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 55 | |
| 56 .. highlight:: text | |
| 57 | |
| 58 If static linking is not desired, here are build instructions for | |
| 59 Ubuntu 16.04 (provided build dependencies for the :ref:`core of | |
| 60 Orthanc <compiling>` have already been installed):: | |
| 61 | |
| 62 $ sudo apt-get install libpq-dev postgresql-server-dev-all | |
| 154 | 63 $ mkdir BuildPostgreSQL |
| 64 $ cd BuildPostgreSQL | |
| 65 $ cmake ../PostgreSQL -DCMAKE_BUILD_TYPE=Release \ | |
| 66 -DALLOW_DOWNLOADS=ON \ | |
| 67 -DUSE_SYSTEM_GOOGLE_TEST=OFF \ | |
| 68 -DUSE_SYSTEM_ORTHANC_SDK=OFF | |
| 128 | 69 $ make |
| 70 | |
|
263
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
71 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
72 .. _postgresql-cmake: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
73 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
74 Dynamic linking on other GNU/Linux distributions |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
75 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
76 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
77 .. highlight:: text |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
78 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
79 The build instructions should always be very similar to those for |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
80 :ref:`Ubuntu 16.04 <postgresql-ubuntu1604>`. One difficulty that could |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
81 however arise is that it is possible that the CMake environment that |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
82 is shipped with the GNU/Linux distribution cannot locate a recent |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
83 version of the development headers for PostgreSQL. This leads to an |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
84 error while invoking CMake that looks like:: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
85 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
86 -- Could NOT find PostgreSQL (missing: PostgreSQL_TYPE_INCLUDE_DIR) |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
87 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
88 In such a situation, please add your version of PostgreSQL to the |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
89 macro ``PostgreSQL_ADDITIONAL_VERSIONS`` that is defined at the end of |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
90 the `Resources/CMake/PostgreSQLConfiguration.cmake file |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
91 <https://bitbucket.org/sjodogne/orthanc-databases/src/default/Resources/CMake/PostgreSQLConfiguration.cmake>`__ in the sources of the project. |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
92 |
| 128 | 93 |
| 27 | 94 Usage |
| 95 ----- | |
| 96 | |
| 97 .. highlight:: json | |
| 98 | |
|
31
93bbfaf0e62c
worklist instructions migrated to a specific page, indexing of Osimis Web viewer
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
30
diff
changeset
|
99 You of course first have to :ref:`install Orthanc <binaries>`, with a |
| 154 | 100 version above 0.9.5. You then have to **create a database** dedicated |
| 28 | 101 to Orthanc on some PostgreSQL server. Please refer to the `PostgreSQL |
| 102 documentation | |
| 27 | 103 <https://www.postgresql.org/docs/current/static/tutorial-createdb.html>`__. |
| 104 | |
| 105 Once Orthanc is installed and the database is created, you must add a | |
| 106 section in the :ref:`configuration file <configuration>` that | |
| 107 specifies the address of the **PostgreSQL server together with your | |
| 108 credentials**. You also have to tell Orthanc in which path it can find | |
| 109 the plugins: This is done by properly modifying the ``Plugins`` | |
| 110 option. You could for instance adapt the following configuration | |
| 111 file:: | |
| 112 | |
| 113 { | |
| 114 "Name" : "MyOrthanc", | |
| 115 "PostgreSQL" : { | |
| 116 "EnableIndex" : true, | |
| 117 "EnableStorage" : true, | |
| 118 "Host" : "localhost", | |
| 119 "Port" : 5432, | |
| 120 "Database" : "orthanc", | |
| 121 "Username" : "orthanc", | |
| 181 | 122 "Password" : "orthanc", |
| 123 "EnableSsl" : false // New in version 3.0 | |
| 27 | 124 }, |
| 125 "Plugins" : [ | |
| 154 | 126 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 127 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 128 ] |
| 129 } | |
| 130 | |
| 154 | 131 **Important:** The ``EnableIndex`` and ``EnableStorage`` options must |
| 132 be explicitly set to ``true``, otherwise Orthanc will continue to use | |
| 133 its default SQLite back-end and the filesystem storage area. | |
| 27 | 134 |
| 181 | 135 **Remark 1:** When using the ``Storage`` PostgreSQL plugin, the DICOM |
| 154 | 136 files are stored as large objects in the database. This might |
| 137 actually consume more space than the DICOM file itself. We have | |
| 138 observed overhead up to 40%. However, it seems this overhead is | |
| 139 temporary and comes from Write-Ahead Logging. Check this `discussion | |
| 140 <https://groups.google.com/d/msg/orthanc-users/pPzHOpb--iw/QkKZ808gIgAJ>`__ | |
| 141 on the Orthanc Users group for more info). | |
| 118 | 142 |
| 181 | 143 **Remark 2:** A typical usage of the PostgreSQL plugin is to enable |
| 144 only the ``Index``, and to use the default filesystem storage for | |
| 145 DICOM files (on a NAS with proper disaster recovery strategies). This | |
| 146 setup provides best performance for large-scale databases. | |
| 147 | |
| 148 **Remark 3:** Setting the ``EnableSsl`` to ``true`` forces the use of | |
| 149 `SSL connections | |
| 150 <https://www.postgresql.org/docs/current/libpq-ssl.html>`__ between | |
| 151 Orthanc and the PostgreSQL server. It is a synonym for | |
| 152 ``sslmode=require`` in connections URI (see below). Setting | |
| 153 ``EnableSsl`` to ``false`` corresponds to ``sslmode=disable`` | |
| 154 (i.e. SSL is not used, even if it is both available in Orthanc and | |
| 155 PostgreSQL). To choose other values for the SSL mode (i.e. ``allow`` | |
| 156 and ``prefer``), please use connection URIs. | |
| 118 | 157 |
| 158 | |
|
94
b0a71b880ca0
fix link + remark about disk usage overhead
Alain Mazy <alain@mazy.be>
parents:
81
diff
changeset
|
159 |
| 27 | 160 .. highlight:: text |
| 161 | |
| 162 Orthanc must of course be **restarted** after the modification of its | |
| 163 configuration file. The log will contain an output similar to:: | |
| 164 | |
| 165 $ ./Orthanc Configuration.json | |
| 166 W0212 16:30:34.576972 11285 main.cpp:632] Orthanc version: 0.8.6 | |
| 167 W0212 16:30:34.577386 11285 OrthancInitialization.cpp:80] Using the configuration from: Configuration.json | |
| 168 [...] | |
| 154 | 169 W0212 16:30:34.598053 11285 main.cpp:379] Registering a plugin from: /home/jodogne/Subversion/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so |
| 27 | 170 W0212 16:30:34.598470 11285 PluginsManager.cpp:258] Registering plugin 'postgresql-index' (version 1.0) |
| 171 W0212 16:30:34.598491 11285 PluginsManager.cpp:148] Using PostgreSQL index | |
| 154 | 172 W0212 16:30:34.608289 11285 main.cpp:379] Registering a plugin from: /home/jodogne/Subversion/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so |
| 27 | 173 W0212 16:30:34.608916 11285 PluginsManager.cpp:258] Registering plugin 'postgresql-storage' (version 1.0) |
| 174 W0212 16:30:34.608947 11285 PluginsManager.cpp:148] Using PostgreSQL storage area | |
| 175 [...] | |
| 176 W0212 16:30:34.674648 11285 main.cpp:530] Orthanc has started | |
| 177 | |
| 178 | |
| 179 .. highlight:: json | |
| 180 | |
| 181 Instead of specifying explicit authentication parameters, you can also | |
| 182 use the `PostgreSQL connection URIs syntax | |
| 183 <https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING>`__. For | |
| 184 instance:: | |
| 185 | |
| 186 { | |
| 187 "Name" : "MyOrthanc", | |
| 188 "PostgreSQL" : { | |
| 189 "EnableIndex" : true, | |
| 190 "EnableStorage" : true, | |
| 181 | 191 "ConnectionUri" : "postgresql://username:password@localhost:5432/database?sslmode=prefer" |
| 27 | 192 }, |
| 193 "Plugins" : [ | |
| 154 | 194 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 195 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 196 ] |
| 197 } | |
| 198 | |
| 199 | |
| 200 **Remark:** The Debian Med project maintains `another useful set of | |
| 201 instructions | |
| 202 <https://anonscm.debian.org/viewvc/debian-med/trunk/packages/orthanc-postgresql/trunk/debian/README.Debian?view=markup>`__. | |
| 203 | |
| 204 | |
| 205 Advanced options | |
| 206 ---------------- | |
| 207 | |
| 208 Several advanced options are available as well to fine-tune the | |
| 209 configuration of the PostgreSQL plugins. They are documented below. | |
| 210 | |
| 211 | |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
212 .. _postgresql-lock: |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
213 |
| 27 | 214 Locking |
| 215 ^^^^^^^ | |
| 216 | |
| 217 .. highlight:: json | |
| 218 | |
| 219 By default, the plugins lock the database (using `PostgreSQL advisory | |
| 220 locks | |
| 221 <https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS>`__) | |
| 222 to prevent other instances of Orthanc from using the same PostgreSQL | |
| 223 database. If you want several instances of Orthanc to share the same | |
| 224 database, set the ``Lock`` option to ``false`` in the configuration | |
| 225 file:: | |
| 226 | |
| 227 { | |
| 228 "Name" : "MyOrthanc", | |
| 229 "PostgreSQL" : { | |
| 230 "EnableIndex" : true, | |
| 231 "EnableStorage" : true, | |
| 232 "Lock" : false, | |
| 233 "ConnectionUri" : "postgresql://username:password@localhost:5432/database" | |
| 234 }, | |
| 235 "Plugins" : [ | |
| 154 | 236 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 237 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 238 ] |
| 239 } | |
| 240 | |
| 241 Obviously, one must be very cautious when sharing the same database | |
| 242 between instances of Orthanc. In particular, all these instances | |
| 243 should share the same configuration. | |
| 244 | |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
245 Furthermore, the core of Orthanc does not currently support the replay |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
246 of database transactions, which is necessary to deal with conflicts |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
247 between several instances of Orthanc that would simultaneously write |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
248 to the database. |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
249 |
| 336 | 250 As a consequence, as of Orthanc 1.6.0, when connecting multiple |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
251 Orthanc to a single database by setting ``Lock`` to ``false``, there |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
252 should only be one instance of Orthanc acting as a writer and all the |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
253 other instances of Orthanc acting as readers only. Be careful to set |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
254 the option ``SaveJobs`` to ``false`` in the configuration file of all |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
255 the instances of Orthanc acting as readers. |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
256 |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
257 A refactoring is needed to improve the core of Orthanc in that |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
258 respect, for which we are looking for funding/donation from the |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
259 industry. Some issues reported in our bug tracker call for this |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
260 refactoring: `issue 83 |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
261 <https://bitbucket.org/sjodogne/orthanc/issues/83/>`__, `issue 121 |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
262 <https://bitbucket.org/sjodogne/orthanc/issues/121/>`__, `issue 151 |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
263 <https://bitbucket.org/sjodogne/orthanc/issues/151/>`__. |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
264 |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
265 |
| 27 | 266 |
| 267 Keep-alive | |
| 268 ^^^^^^^^^^ | |
| 269 | |
| 270 .. highlight:: text | |
| 271 | |
| 272 After some period of inactivity (users have reported 10 hours), you | |
| 273 might `experience an error | |
| 274 <https://bitbucket.org/sjodogne/orthanc/issues/15/postgresql-exceptions-after-time>`__ | |
| 275 such as:: | |
| 276 | |
| 277 E0220 03:20:51.562601 PluginsManager.cpp:163] Exception in database back-end: Error in PostgreSQL: server closed the connection unexpectedly. | |
| 278 This probably means the server terminated abnormally before or while processing the request. | |
| 279 E0220 06:51:03.924868 PluginsManager.cpp:163] Exception in database back-end: Error in PostgreSQL: no connection to the server | |
| 280 | |
| 281 This is due to a timeout in the PostgreSQL server. Please make sure to | |
| 282 `enable keep-alive | |
| 283 <http://dba.stackexchange.com/questions/97534/is-there-a-timeout-option-for-remote-access-to-postgresql-database>`__ | |
| 284 in the configuration of your PostgreSQL server |