OMERO.server and PostgreSQL
===========================

In order to be installed, OMERO.server requires a running PostgreSQL
instance that is configured to accept connections over TCP. This
section explains how to ensure that you have the correct PostgreSQL
version and that it is installed and configured correctly.

Ensuring you have a valid PostgreSQL version
--------------------------------------------

For OMERO |version|, PostgreSQL 8.4 or higher is required.

You can check which version of PostgreSQL you have installed with any of
the following commands:

::

       $ createuser -V
       createuser (PostgreSQL) 9.1.4
       $ psql -V
       psql (PostgreSQL) 9.1.4
       $ createdb -V
       createdb (PostgreSQL) 9.1.4
       
If your default PostgreSQL installation is version 8.3 or earlier, you
will need to upgrade to a more up-to-date version. We suggest the
installer from `EnterpriseDB <http://www.enterprisedb.com/>`_. Versions
8.4, 9.0 and 9.1 are known to work with OMERO |version|; 9.1 is recommended.


Compatibility matrix
~~~~~~~~~~~~~~~~~~~~

Versions of PostgreSQL which are compatible with OMERO are shown in
the table below.

.. tabularcolumns:: |l|l|l|l|l|

========== =============== =============== =============== ==============
PostgreSQL OMERO 4.1       OMERO 4.2       OMERO 4.3       OMERO 4.4
========== =============== =============== =============== ==============
7.4        YES             NO :term:`[1]`  NO :term:`[1]`  NO :term:`[4]`
8.1        YES             NO :term:`[3]`  NO :term:`[1]`  NO :term:`[4]`
8.2        YES             YES             NO :term:`[3]`  NO :term:`[4]`
8.3        YES             YES             YES             NO :term:`[4]`
8.4        YES             YES             YES             YES
9.x        YES :term:`[2]` YES :term:`[2]` YES :term:`[2]` YES
========== =============== =============== =============== ==============

.. glossary::

    [1]
        Not suggested; see :ticket:`4902`

    [2]
        Configuration may be necessary; see :ticket:`5662`

    [3]
        Not suggested; see :ticket:`5861`

    [4]
        Unsupported; see :ticket:`7813`


Checking PostgreSQL port listening status
-----------------------------------------

You can check if PostgreSQL is listening on the default port
(``TCP/5432``) by running the following command:

::

    $ netstat -an | egrep '5432.*LISTEN'
    tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
    tcp        0      0 :::5432                 :::*                    LISTEN


.. note::  
    The exact output of this command will vary. The important
    thing to recognize is whether or not a process is listening on
    ``TCP/5432``.

If you cannot find a process listening on ``TCP/5432`` you will need to
find your ``postgresql.conf`` file and enable PostgreSQL's TCP listening
mode. The exact location of the ``postgresql.conf`` file varies between
installations.

It may be helpful to locate it using the package manager (``rpm``
or ``dpkg``) or by utilizing the ``find`` command. Usually, the
PostgreSQL data directory (which houses the ``postgresql.conf``
file, is located under ``/var`` or ``/usr``:

::

    $ sudo find /etc -name 'postgresql.conf'
    $ sudo find /usr -name 'postgresql.conf'
    $ sudo find /var -name 'postgresql.conf'
    /var/lib/postgresql/data/postgresql.conf

.. note:: 
    The PostgreSQL data directory is usually only readable by the
    user ``postgres`` so you will likely have to be ``root`` in order to
    find it.

Once you have found the location of the ``postgresql.conf`` file on
your particular installation, you will need to enable TCP listening.
For PostgreSQL 8.4 and 9.x, the area of the configuration file you are
concerned about should look similar to this:

::

    #listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
    #port = 5432
    max_connections = 100
    # note: increasing max_connections costs ~400 bytes of shared memory per
    # connection slot, plus lock space (see max_locks_per_transaction).  You
    # might also need to raise shared_buffers to support more connections.
    #superuser_reserved_connections = 2
    #unix_socket_directory = *
    #unix_socket_group = *
    #unix_socket_permissions = 0777         # octal
    #bonjour_name = *                      # defaults to the computer name


PostgreSQL HBA (host based authentication)
------------------------------------------

OMERO.server must have permission to connect to the database that has
been created in your PostgreSQL instance.  This is configured in the
*host based authentication* file, ``pg_hba.conf``.  Check the
configuration by examining the contents of ``pg_hba.conf``. It's
important that at least one line allows connections from the loopback
address (``127.0.0.1``) as follows:

::

    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
    # IPv4 local connections:
    host    all         all         127.0.0.1/32          md5

.. note:: 
    The other lines that are in your ``pg_hba.conf`` are important
    either for PostgreSQL internal commands to work or for existing
    applications you may have. **Do not delete them**.

.. seealso::

    `PostgreSQL <http://www.postgresql.org/docs/current/interactive/index.html>`_ 
        Interactive documentation for the current release of PostgreSQL.

    `Connections and Authentication <http://www.postgresql.org/docs/current/interactive/runtime-config-connection.html>`_
        Section of the PostgreSQL documentation about configuring the server using `postgresql.conf`.

    `Client Authentication <http://www.postgresql.org/docs/current/interactive/client-authentication.html>`_
        Chapter of the PostgreSQL documentation about configuring client authentication with `pg_hba.conf`.