comparison Sphinx/source/plugins/postgresql.rst @ 1034:bbcec5f44c34

PG 6.0
author Alain Mazy <am@osimis.io>
date Thu, 08 Feb 2024 16:46:35 +0100
parents 351af8edb2e8
children b5f57cec7190
comparison
equal deleted inserted replaced
1033:f3f320632887 1034:bbcec5f44c34
131 "Password" : "orthanc", 131 "Password" : "orthanc",
132 "EnableSsl" : false, // New in release 3.0 132 "EnableSsl" : false, // New in release 3.0
133 "MaximumConnectionRetries" : 10, // New in release 3.0 133 "MaximumConnectionRetries" : 10, // New in release 3.0
134 "ConnectionRetryInterval" : 5, // New in release 3.0 134 "ConnectionRetryInterval" : 5, // New in release 3.0
135 "IndexConnectionsCount" : 1, // New in release 4.0 135 "IndexConnectionsCount" : 1, // New in release 4.0
136 "TransactionMode": "SERIALIZABLE" // New in beta version (not released yet) 136 "TransactionMode": "Serializable", // New in release 6.0
137 "EnableVerboseLogs": false // New in release 6.0
137 }, 138 },
138 "Plugins" : [ 139 "Plugins" : [
139 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so", 140 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so",
140 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so" 141 "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLStorage.so"
141 ] 142 ]
234 235
235 * ``Lock`` must be set to ``false`` (cf. :ref:`below <postgresql-lock>`) 236 * ``Lock`` must be set to ``false`` (cf. :ref:`below <postgresql-lock>`)
236 237
237 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to 238 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to
238 connect to the database, as well as how many times Orthanc replays 239 connect to the database, as well as how many times Orthanc replays
239 transactions to deal with collisions between multiple writers. 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.
240 243
241 * ``IndexConnectionsCount`` controls the number of connections from 244 * ``IndexConnectionsCount`` controls the number of connections from
242 the index plugin to the PostgreSQL database. It is set to ``1`` by 245 the index plugin to the PostgreSQL database. It is set to ``1`` by
243 default, which corresponds to the old behavior of Orthanc <= 1.9.1. 246 default, which corresponds to the old behavior of Orthanc <= 1.9.1.
244 247
245 * ``ConnectionRetryInterval`` is only used when opening one database 248 * ``ConnectionRetryInterval`` is only used when opening one database
246 connection to PostgreSQL. 249 connection to PostgreSQL.
247 250
248 * ``TransactionMode`` has been added in the ``pg-transactions`` beta version only. 2 values are 251 * ``TransactionMode`` has been added in the release 6.0. 2 values are
249 allowed: ``SERIALIZABLE`` (that has always been the default mode for Orthanc) 252 allowed: ``Serializable`` (that has always been the default mode for Orthanc)
250 and ``READ COMMITTED`` that is available only from this beta version. See 253 and ``ReadCommitted`` that is available only from release 6.0. See
251 below. 254 below.
252 255
253 * The PostgreSQL plugin supports the :ref:`revision mechanism 256 * The PostgreSQL plugin supports the :ref:`revision mechanism
254 <revisions>` to protect metadata and attachments from concurrent 257 <revisions>` to protect metadata and attachments from concurrent
255 modifications. 258 modifications.
314 ^^^^^^^^^^^ 317 ^^^^^^^^^^^
315 318
316 When configuring your PostgreSQL plugin, ensure you've read the :ref:`scalability section 319 When configuring your PostgreSQL plugin, ensure you've read the :ref:`scalability section
317 <scalability>` 320 <scalability>`
318 321
319 Transaction modes (``pg-transactions`` beta version only) 322 Transaction modes (new in version 6.0)
320 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 323 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
321 324
322 .. highlight:: json 325 .. highlight:: json
323 326
324 Starting from the current beta version, orthanc supports 2 transaction modes that 327 Starting from the release 6.0 of the plugin and Orthanc 1.12.3, orthanc supports 2 transaction modes that
325 can be configured in the ``TransactionMode`` configuration of the ``PostgreSQL`` plugin: 328 can be configured in the ``TransactionMode`` configuration of the ``PostgreSQL`` plugin:
326 329
327 - ``SERIALIZABLE`` in which all write transactions are serialized which might lead 330 - ``Serializable`` in which all write transactions are serialized which might lead
328 to performance bottlenecks when lots of threads or Orthanc instances are trying 331 to performance bottlenecks when lots of threads or Orthanc instances are trying
329 to write to the same Database. 332 to write to the same Database.
330 - ``READ COMMITTED`` that allows multiple threads or Orthanc instances to write at the 333 - ``ReadCommitted`` that allows multiple threads or Orthanc instances to write at the
331 same time to the same Database. 334 same time to the same Database.
332 335
333 *Remark:* This feature is only available in a beta version of both Orthanc and the 336
334 PostgreSQL plugin (``pg-transactions`` branches in the code). This beta version is 337 Upgrades/Downgrades
335 only available in the ``orthancteam/orthanc-pre-release:pg-transactions-unstable`` Docker image. 338 ^^^^^^^^^^^^^^^^^^^
336
337 *Remark:* This beta version is really a beta version and **should not be used on a
338 production database**. It shall only be used on a DB that you can delete or recover.
339 The reason why it shall not be used on a production database is because this revision
340 modifies the DB schema to a :ref:`version <db-versioning>`/revision ``6.2`` that might be different from the
341 final schema of the future release although it will share the same version/revision.
342 We can not guarantee to maintain migration scripts from this temporary schema to the final
343 one.
344
345 Upgrades/Downgrades (``pg-transactions`` beta version only)
346 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
347 339
348 New vesions of the PostgreSQL might modify the DB schema by adding new columns/tables/triggers. 340 New vesions of the PostgreSQL might modify the DB schema by adding new columns/tables/triggers.
349 Upgrades from one revision to the other is always automatic. 341 Upgrades from one revision to the other is always automatic.
350 342
351 However, if, for some reasons, you would like to reinstall a previous plugin version, the 343 However, if, for some reasons, you would like to reinstall a previous plugin version, the
352 older plugin might refuse to start because the revision is newer and unknown to it. 344 older plugin might refuse to start because the revision is newer and unknown to it.
353 345
354 Starting from this beta version, we are providing a downgrade script in case you want, e.g, 346 Starting from version 6.0 of the plugin, we are providing a downgrade script in case you want, e.g,
355 to reinstall Orthanc 1.12.3 and PostgreSQL 5.1 (DB schema revision 6.1). 347 to reinstall Orthanc 1.12.3 and PostgreSQL 5.1 (whose DB schema is at revision 1).
356 348
357 To downgrade from the beta to the PostgreSQL 5.1, one might run this procedure:: 349 To downgrade from revision 2 to revision 1, one might run this procedure::
358 350
359 $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/pg-transactions/PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql 351 $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql
360 $ psql -U postgres -f V6.2ToV6.1.sql 352 $ psql -U postgres -f Rev2ToRev1.sql
361 353
362 354
363 Troubleshooting 355 Troubleshooting
364 --------------- 356 ---------------
365 357