Mercurial > hg > orthanc-book
annotate Sphinx/source/plugins/postgresql.rst @ 1036:b5f57cec7190
PG upgrades on large DB
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Sat, 02 Mar 2024 16:38:17 +0100 |
| parents | bbcec5f44c34 |
| children | edd8e2d0eb99 |
| 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 | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
14 plugins <https://www.orthanc-server.com/static.php?page=postgresql>`__. |
| 27 | 15 |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
16 For information about scalability, make sure to read the section about |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
17 :ref:`multiple writers in large-scale deployments <multiple-writers>`. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
18 |
| 750 | 19 The source code of the PostgreSQL plugins can be found in the |
| 20 ``orthanc-databases`` `Mercurial repository | |
|
991
1316bc62b5d5
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
750
diff
changeset
|
21 <https://orthanc.uclouvain.be/hg/orthanc-databases/>`__, next to the |
| 750 | 22 source code of the :ref:`ODBC <odbc>` and |
| 23 :ref:`MySQL/MariaDB <mysql>` plugins. | |
| 27 | 24 |
| 25 | |
| 26 Compilation | |
| 27 ----------- | |
| 28 | |
| 128 | 29 Static linking |
| 30 ^^^^^^^^^^^^^^ | |
| 31 | |
| 27 | 32 .. highlight:: text |
| 33 | |
| 154 | 34 The procedure to compile these plugins is similar to that for the |
| 27 | 35 :ref:`core of Orthanc <compiling>`. The following commands should work |
| 154 | 36 for most UNIX-like distribution (including GNU/Linux):: |
| 27 | 37 |
| 154 | 38 $ mkdir BuildPostgreSQL |
| 39 $ cd BuildPostgreSQL | |
| 40 $ cmake ../PostgreSQL -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release | |
| 27 | 41 $ make |
| 42 | |
| 43 The compilation will produce 2 shared libraries, each containing one plugin for Orthanc: | |
| 44 | |
| 45 * ``OrthancPostgreSQLIndex`` replaces the default SQLite index of Orthanc by PostgreSQL. | |
| 46 * ``OrthancPostgreSQLStorage`` makes Orthanc store the DICOM files it receives into PostgreSQL. | |
| 47 | |
| 128 | 48 |
| 49 Microsoft Windows and Apple OS X | |
| 50 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 51 | |
| 750 | 52 Pre-compiled binaries for Microsoft Windows 32bit `are also available |
|
993
05b106383b2a
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
991
diff
changeset
|
53 <https://orthanc.uclouvain.be/downloads/windows-32/orthanc-postgresql/index.html>`__. |
| 30 | 54 A package for `Apple's Mac OS X |
|
993
05b106383b2a
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
991
diff
changeset
|
55 <https://www.orthanc-server.com/static.php?page=download-mac>`__ |
|
05b106383b2a
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
991
diff
changeset
|
56 is available courtesy of `Orthanc Team <https://orthanc.team/>`__. |
| 27 | 57 |
| 58 | |
|
263
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
59 .. _postgresql-ubuntu1604: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
60 |
| 128 | 61 Dynamic linking on Ubuntu 16.04 |
| 62 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 63 | |
| 64 .. highlight:: text | |
| 65 | |
| 66 If static linking is not desired, here are build instructions for | |
| 67 Ubuntu 16.04 (provided build dependencies for the :ref:`core of | |
| 68 Orthanc <compiling>` have already been installed):: | |
| 69 | |
| 70 $ sudo apt-get install libpq-dev postgresql-server-dev-all | |
| 154 | 71 $ mkdir BuildPostgreSQL |
| 72 $ cd BuildPostgreSQL | |
| 73 $ cmake ../PostgreSQL -DCMAKE_BUILD_TYPE=Release \ | |
| 74 -DALLOW_DOWNLOADS=ON \ | |
| 75 -DUSE_SYSTEM_GOOGLE_TEST=OFF \ | |
| 76 -DUSE_SYSTEM_ORTHANC_SDK=OFF | |
| 128 | 77 $ make |
| 78 | |
|
263
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
79 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
80 .. _postgresql-cmake: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
81 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
82 Dynamic linking on other GNU/Linux distributions |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
83 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
84 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
85 .. highlight:: text |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
86 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
87 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
|
88 :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
|
89 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
|
90 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
|
91 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
|
92 error while invoking CMake that looks like:: |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
93 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
94 -- 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
|
95 |
|
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
96 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
|
97 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
|
98 the `Resources/CMake/PostgreSQLConfiguration.cmake file |
|
991
1316bc62b5d5
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
750
diff
changeset
|
99 <https://orthanc.uclouvain.be/hg/orthanc-databases/file/default/Resources/CMake/PostgreSQLConfiguration.cmake>`__ |
| 361 | 100 in the sources of the project. |
|
263
39609c9a2c41
note about includes for postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
181
diff
changeset
|
101 |
| 128 | 102 |
| 27 | 103 Usage |
| 104 ----- | |
| 105 | |
| 106 .. highlight:: json | |
| 107 | |
|
31
93bbfaf0e62c
worklist instructions migrated to a specific page, indexing of Osimis Web viewer
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
30
diff
changeset
|
108 You of course first have to :ref:`install Orthanc <binaries>`, with a |
| 154 | 109 version above 0.9.5. You then have to **create a database** dedicated |
| 28 | 110 to Orthanc on some PostgreSQL server. Please refer to the `PostgreSQL |
| 111 documentation | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
112 <https://www.postgresql.org/docs/current/tutorial-createdb.html>`__. |
| 27 | 113 |
| 114 Once Orthanc is installed and the database is created, you must add a | |
| 115 section in the :ref:`configuration file <configuration>` that | |
| 116 specifies the address of the **PostgreSQL server together with your | |
| 117 credentials**. You also have to tell Orthanc in which path it can find | |
| 118 the plugins: This is done by properly modifying the ``Plugins`` | |
| 119 option. You could for instance adapt the following configuration | |
| 120 file:: | |
| 121 | |
| 122 { | |
| 123 "Name" : "MyOrthanc", | |
| 124 "PostgreSQL" : { | |
| 125 "EnableIndex" : true, | |
| 126 "EnableStorage" : true, | |
| 127 "Host" : "localhost", | |
| 128 "Port" : 5432, | |
| 129 "Database" : "orthanc", | |
| 130 "Username" : "orthanc", | |
| 181 | 131 "Password" : "orthanc", |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
132 "EnableSsl" : false, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
133 "MaximumConnectionRetries" : 10, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
134 "ConnectionRetryInterval" : 5, // New in release 3.0 |
| 1016 | 135 "IndexConnectionsCount" : 1, // New in release 4.0 |
| 1034 | 136 "TransactionMode": "Serializable", // New in release 6.0 |
| 137 "EnableVerboseLogs": false // New in release 6.0 | |
| 27 | 138 }, |
| 139 "Plugins" : [ | |
| 154 | 140 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 141 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 142 ] |
| 143 } | |
| 144 | |
| 154 | 145 **Important:** The ``EnableIndex`` and ``EnableStorage`` options must |
| 146 be explicitly set to ``true``, otherwise Orthanc will continue to use | |
| 147 its default SQLite back-end and the filesystem storage area. | |
| 27 | 148 |
| 181 | 149 **Remark 1:** When using the ``Storage`` PostgreSQL plugin, the DICOM |
| 154 | 150 files are stored as large objects in the database. This might |
| 151 actually consume more space than the DICOM file itself. We have | |
| 152 observed overhead up to 40%. However, it seems this overhead is | |
| 153 temporary and comes from Write-Ahead Logging. Check this `discussion | |
| 154 <https://groups.google.com/d/msg/orthanc-users/pPzHOpb--iw/QkKZ808gIgAJ>`__ | |
| 155 on the Orthanc Users group for more info). | |
| 118 | 156 |
| 181 | 157 **Remark 2:** A typical usage of the PostgreSQL plugin is to enable |
| 158 only the ``Index``, and to use the default filesystem storage for | |
| 159 DICOM files (on a NAS with proper disaster recovery strategies). This | |
| 160 setup provides best performance for large-scale databases. | |
| 161 | |
| 162 **Remark 3:** Setting the ``EnableSsl`` to ``true`` forces the use of | |
| 163 `SSL connections | |
| 164 <https://www.postgresql.org/docs/current/libpq-ssl.html>`__ between | |
| 165 Orthanc and the PostgreSQL server. It is a synonym for | |
| 166 ``sslmode=require`` in connections URI (see below). Setting | |
| 167 ``EnableSsl`` to ``false`` corresponds to ``sslmode=disable`` | |
| 168 (i.e. SSL is not used, even if it is both available in Orthanc and | |
| 169 PostgreSQL). To choose other values for the SSL mode (i.e. ``allow`` | |
| 170 and ``prefer``), please use connection URIs. | |
| 118 | 171 |
| 172 | |
|
94
b0a71b880ca0
fix link + remark about disk usage overhead
Alain Mazy <alain@mazy.be>
parents:
81
diff
changeset
|
173 |
| 27 | 174 .. highlight:: text |
| 175 | |
| 176 Orthanc must of course be **restarted** after the modification of its | |
| 177 configuration file. The log will contain an output similar to:: | |
| 178 | |
| 179 $ ./Orthanc Configuration.json | |
| 180 W0212 16:30:34.576972 11285 main.cpp:632] Orthanc version: 0.8.6 | |
| 181 W0212 16:30:34.577386 11285 OrthancInitialization.cpp:80] Using the configuration from: Configuration.json | |
| 182 [...] | |
| 154 | 183 W0212 16:30:34.598053 11285 main.cpp:379] Registering a plugin from: /home/jodogne/Subversion/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so |
| 27 | 184 W0212 16:30:34.598470 11285 PluginsManager.cpp:258] Registering plugin 'postgresql-index' (version 1.0) |
| 185 W0212 16:30:34.598491 11285 PluginsManager.cpp:148] Using PostgreSQL index | |
| 154 | 186 W0212 16:30:34.608289 11285 main.cpp:379] Registering a plugin from: /home/jodogne/Subversion/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so |
| 27 | 187 W0212 16:30:34.608916 11285 PluginsManager.cpp:258] Registering plugin 'postgresql-storage' (version 1.0) |
| 188 W0212 16:30:34.608947 11285 PluginsManager.cpp:148] Using PostgreSQL storage area | |
| 189 [...] | |
| 190 W0212 16:30:34.674648 11285 main.cpp:530] Orthanc has started | |
| 191 | |
| 192 | |
| 193 .. highlight:: json | |
| 194 | |
| 195 Instead of specifying explicit authentication parameters, you can also | |
| 196 use the `PostgreSQL connection URIs syntax | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
197 <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`__. For |
| 27 | 198 instance:: |
| 199 | |
| 200 { | |
| 201 "Name" : "MyOrthanc", | |
| 202 "PostgreSQL" : { | |
| 203 "EnableIndex" : true, | |
| 204 "EnableStorage" : true, | |
| 181 | 205 "ConnectionUri" : "postgresql://username:password@localhost:5432/database?sslmode=prefer" |
| 27 | 206 }, |
| 207 "Plugins" : [ | |
| 154 | 208 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 209 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 210 ] |
| 211 } | |
| 212 | |
| 213 | |
| 214 **Remark:** The Debian Med project maintains `another useful set of | |
| 215 instructions | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
216 <https://salsa.debian.org/med-team/orthanc-postgresql/-/blob/master/debian/README.Debian>`__. |
| 27 | 217 |
| 218 | |
| 219 Advanced options | |
| 220 ---------------- | |
| 221 | |
| 222 Several advanced options are available as well to fine-tune the | |
| 223 configuration of the PostgreSQL plugins. They are documented below. | |
| 224 | |
| 225 | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
226 .. _postgresql-multiple-writers: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
227 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
228 Multiple writers or connections |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
229 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
230 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
231 Starting with Orthanc 1.9.2 and PostgreSQL 4.0, it is possible to use |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
232 :ref:`multiple writers or connections in large-scale deployments |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
233 <multiple-writers>`. Here is the list of configuration that control |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
234 this behavior: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
235 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
236 * ``Lock`` must be set to ``false`` (cf. :ref:`below <postgresql-lock>`) |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
237 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
238 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
239 connect to the database, as well as how many times Orthanc replays |
| 1034 | 240 transactions to deal with collisions between multiple writers in |
| 241 ``Serializable`` transaction mode or with any transient transaction errors | |
| 242 in all transaction modes. | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
243 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
244 * ``IndexConnectionsCount`` controls the number of connections from |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
245 the index plugin to the PostgreSQL database. It is set to ``1`` by |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
246 default, which corresponds to the old behavior of Orthanc <= 1.9.1. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
247 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
248 * ``ConnectionRetryInterval`` is only used when opening one database |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
249 connection to PostgreSQL. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
250 |
| 1034 | 251 * ``TransactionMode`` has been added in the release 6.0. 2 values are |
| 252 allowed: ``Serializable`` (that has always been the default mode for Orthanc) | |
| 253 and ``ReadCommitted`` that is available only from release 6.0. See | |
| 1016 | 254 below. |
| 255 | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
256 * The PostgreSQL plugin supports the :ref:`revision mechanism |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
257 <revisions>` to protect metadata and attachments from concurrent |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
258 modifications. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
259 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
260 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
261 |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
262 .. _postgresql-lock: |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
263
diff
changeset
|
263 |
| 27 | 264 Locking |
| 265 ^^^^^^^ | |
| 266 | |
| 267 .. highlight:: json | |
| 268 | |
| 269 By default, the plugins lock the database (using `PostgreSQL advisory | |
| 270 locks | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
271 <https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS>`__) |
| 27 | 272 to prevent other instances of Orthanc from using the same PostgreSQL |
| 273 database. If you want several instances of Orthanc to share the same | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
560
diff
changeset
|
274 database or if you need multiple connections to the PostgreSQL |
| 27 | 275 database, set the ``Lock`` option to ``false`` in the configuration |
| 276 file:: | |
| 277 | |
| 278 { | |
| 279 "Name" : "MyOrthanc", | |
| 280 "PostgreSQL" : { | |
| 281 "EnableIndex" : true, | |
| 282 "EnableStorage" : true, | |
| 283 "Lock" : false, | |
| 284 "ConnectionUri" : "postgresql://username:password@localhost:5432/database" | |
| 285 }, | |
| 286 "Plugins" : [ | |
| 154 | 287 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", |
| 288 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" | |
| 27 | 289 ] |
| 290 } | |
| 291 | |
| 292 Obviously, one must be very cautious when sharing the same database | |
| 293 between instances of Orthanc. In particular, all these instances | |
| 294 should share the same configuration. | |
| 295 | |
| 296 | |
| 297 Keep-alive | |
| 298 ^^^^^^^^^^ | |
| 299 | |
| 300 .. highlight:: text | |
| 301 | |
| 302 After some period of inactivity (users have reported 10 hours), you | |
| 303 might `experience an error | |
|
991
1316bc62b5d5
migration to UCLouvain servers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
750
diff
changeset
|
304 <https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=15>`__ such as:: |
| 27 | 305 |
| 306 E0220 03:20:51.562601 PluginsManager.cpp:163] Exception in database back-end: Error in PostgreSQL: server closed the connection unexpectedly. | |
| 307 This probably means the server terminated abnormally before or while processing the request. | |
| 308 E0220 06:51:03.924868 PluginsManager.cpp:163] Exception in database back-end: Error in PostgreSQL: no connection to the server | |
| 309 | |
| 310 This is due to a timeout in the PostgreSQL server. Please make sure to | |
| 311 `enable keep-alive | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
336
diff
changeset
|
312 <https://dba.stackexchange.com/questions/97534/is-there-a-timeout-option-for-remote-access-to-postgresql-database>`__ |
| 27 | 313 in the configuration of your PostgreSQL server |
| 438 | 314 |
| 315 | |
| 316 Scalability | |
| 317 ^^^^^^^^^^^ | |
| 318 | |
| 319 When configuring your PostgreSQL plugin, ensure you've read the :ref:`scalability section | |
| 320 <scalability>` | |
| 531 | 321 |
| 1034 | 322 Transaction modes (new in version 6.0) |
| 323 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 1016 | 324 |
| 325 .. highlight:: json | |
| 326 | |
| 1034 | 327 Starting from the release 6.0 of the plugin and Orthanc 1.12.3, orthanc supports 2 transaction modes that |
| 1016 | 328 can be configured in the ``TransactionMode`` configuration of the ``PostgreSQL`` plugin: |
| 329 | |
| 1034 | 330 - ``Serializable`` in which all write transactions are serialized which might lead |
| 1016 | 331 to performance bottlenecks when lots of threads or Orthanc instances are trying |
| 332 to write to the same Database. | |
| 1034 | 333 - ``ReadCommitted`` that allows multiple threads or Orthanc instances to write at the |
| 1016 | 334 same time to the same Database. |
| 335 | |
| 336 | |
| 1034 | 337 Upgrades/Downgrades |
| 338 ^^^^^^^^^^^^^^^^^^^ | |
| 1016 | 339 |
| 340 New vesions of the PostgreSQL might modify the DB schema by adding new columns/tables/triggers. | |
| 341 Upgrades from one revision to the other is always automatic. | |
| 342 | |
| 343 However, if, for some reasons, you would like to reinstall a previous plugin version, the | |
| 344 older plugin might refuse to start because the revision is newer and unknown to it. | |
| 345 | |
| 1034 | 346 Starting from version 6.0 of the plugin, we are providing a downgrade script in case you want, e.g, |
| 347 to reinstall Orthanc 1.12.3 and PostgreSQL 5.1 (whose DB schema is at revision 1). | |
| 1016 | 348 |
| 1034 | 349 To downgrade from revision 2 to revision 1, one might run this procedure:: |
| 1016 | 350 |
| 1034 | 351 $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql |
| 352 $ psql -U postgres -f Rev2ToRev1.sql | |
| 1016 | 353 |
| 1036 | 354 Note for large databases and multiple Orthanc instances: |
| 355 """""""""""""""""""""""""""""""""""""""""""""""""""""""" | |
| 356 | |
| 357 When upgrading from revision 1 to revision 2, the upgrade might take quite some time. E.g, we have observed the upgrade | |
| 358 taking 17 minutes on a DB with 300.000 studies and 150 millions instances. Orthanc will not respond during the upgrade. Therefore, | |
| 359 if you have enabled autohealing (automatic restart in case Orthanc is not responsive), you should likely disable it | |
| 360 during the first start with the PostgreSQL plugin v6.0. | |
| 361 | |
| 362 Also note that, if you have multiple containers connected to the same DB, all containers will try to acquire an exclusive lock | |
| 363 to perform the upgrade of the DB. Only one of them will actually perform the upgrade. Also note that you should not perform a | |
| 364 rolling updates of the Orthanc containers when performing a DB upgrade. All Orthanc containers should use the same version of the | |
| 365 plugin, the one that is compatible with the current revision. | |
| 366 | |
| 367 Therefore, in complex setups, it might be simpler/safer to simply shut-down the Orthanc containers, perform the update | |
| 368 manually and then, restart the Orthanc containers with the newest version of the plugin. | |
| 369 | |
| 370 To upgrade manually from revision 1 to revision 2, one might run this procedure:: | |
| 371 | |
| 372 $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql | |
| 373 $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/PrepareIndex.sql | |
| 374 $ psql -U postgres -f Rev1ToRev2.sql | |
| 375 $ psql -U postgres -f PrepareIndex.sql | |
| 376 | |
| 531 | 377 |
| 378 Troubleshooting | |
| 379 --------------- | |
| 380 | |
| 381 SCRAM authentication | |
| 382 ^^^^^^^^^^^^^^^^^^^^ | |
| 383 | |
|
560
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
384 .. note:: This section only applies to releases <= 3.2 of the |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
385 PostgreSQL plugins. Starting with release 3.3, the plugins |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
386 use a version of libpq that should support SCRAM |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
387 authentication. |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
388 |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
389 In the releases 3.2 of the PostgreSQL plugins, the precompiled |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
390 binaries use an old, but stable version of the PostgreSQL client |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
391 (libpq 9.6.1). This makes these binaries very portable, however they |
|
5c0dfc863884
note for postgresql 3.3
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
531
diff
changeset
|
392 might not be compatible with more recent features of PostgreSQL. |
| 531 | 393 |
| 394 In particular, the precompiled binaries are not compatible with `SCRAM | |
| 395 authentication | |
| 396 <https://en.wikipedia.org/wiki/Salted_Challenge_Response_Authentication_Mechanism>`__ | |
| 397 that is available since PostgreSQL 10. If you get the error ``psql: | |
| 398 authentication method 10 not supported``, this indicates that the | |
| 399 PostgreSQL plugins cannot connect to a PostgreSQL server because SCRAM | |
| 400 is enabled. | |
| 401 | |
| 402 `Ian Smith | |
| 403 <https://groups.google.com/g/orthanc-users/c/4EH7HpcEnSA/m/a4x6oiucAgAJ>`__ | |
| 404 has reported the following method to disable SCRAM: | |
| 405 | |
| 406 1. Drop/delete the ``orthanc`` database and user in PostgreSQL. | |
| 407 2. Edit the files ``postgresql.conf`` and ``pg_hba.conf`` and change | |
| 408 ``scram-sha-256`` to ``md5`` in all cases. | |
| 409 3. Add the ``orthanc`` user and database in PostgreSQL again. | |
| 410 4. Restart Orthanc. |