General
=======

Orthanc PostgreSQL is a set of 2 plugins to Orthanc, a lightweight,
RESTful Vendor Neutral Archive for medical imaging. These plugins
override the default SQLite engine of Orthanc with a PostgreSQL
back-end. They bring scalability to Orthanc, making it
enterprise-ready.

Homepage: http://www.orthanc-server.com/static.php?page=postgresql



Configuration
=============

To enable the plugin, you must specify the parameters of your
PostgreSQL database by adapting the configuration file
"/etc/orthanc/postgresql.json". Then, restart the Orthanc service:

	$> sudo systemctl restart orthanc.service



Upgrade
=======

The schema of the Orthanc database evolves across versions. If you use
a PostgreSQL database that was created for an older version of the
schema, Orthanc will fail to start and you will have to manually
upgrade the database.

You can read the status of the Orthanc service in the log:

	$> cat /var/log/orthanc/Orthanc.log

If, after upgrading the orthanc-postgresql package, Orthanc complains
that the database schema must be upgraded, type the following commands
(reconstructing the database may take some time):

	$> sudo systemctl stop orthanc.service
	$> sudo /usr/sbin/Orthanc /etc/orthanc --verbose --upgrade
	$> sudo systemctl start orthanc.service


NOTE: As an alternative to the automated upgrade (e.g. if you face
problems or if you wish to keep the older version of the database),
you can have a look at the instructions for replication in the Orthanc
Book: https://orthanc.chu.ulg.ac.be/book/users/replication.html



Step-by-step tutorial
=====================

(1) Install the database server:

	$> sudo apt-get install postgresql


(2) Create a database account a database to store the
    content of Orthanc:

	$> sudo -u postgres createuser -e orthanc
	$> sudo -u postgres createdb -e -E utf8 -O orthanc orthanc_db


(3) Restrict access to the Orthanc database:

	Edit /etc/postgresql/VERSION/main/pg_hba.conf to contain

		local   orthanc_db     orthanc                                  ident

	somewhere AFTER the lines

		# Database administrative login by Unix domain socket
		local   all             postgres                                ident

	Reload PostgreSQL configuration

		$> sudo systemctl reload postgresql.service


(3) Edit the configuration file "/etc/orthanc/postgresql.json"
    to make Orthanc connect to PostgreSQL:

	"PostgreSQL" : {
		"EnableIndex" : true,
		"EnableStorage": true,
		"ConnectionUri" : "postgresql:///orthanc_db?user=orthanc",
	}

	Make sure to comment out all individual authentication
	parameters such as Host, Port, ...


(4) Restart the Orthanc service and check the logs:

	$> sudo apt-get install postgresql
	$> sudo less /var/log/orthanc/Orthanc.WARNING
	$> sudo less /var/log/postgresql/postgresl-VERSION-main.log



Further notes
=============

Setup

One can use any name for the database, as long as reality and
configuration match.

The database account should be "orthanc" in order to enable intuitive use
of PEER authentication because Orthanc is running as the "orthanc" system
user. If you need to use another database account adjust pg_hba.conf
accordingly or use a user name mapping (see PostgreSQL documentation).


Data integrity

If you intend to set "StoreMD5ForAttachments" to "true" in orthanc.json
you should strongly consider making sure the PostgreSQL cluster is set to
provide on-disk data page checksumming instead. Check with the following
SQL query:

	psql> select * from pg_settings where name = 'data_checksums';

There is no need to make Orthanc calculate MD5 sums when PostgreSQL is
already doing it for on-disk storage.


Data compression

Also, strongly consider to NOT set "StorageCompression" to "true" when
using PostgreSQL because the database will already compress data (TOAST,
EXTENDED storage, pg_largeobject)


Data migration