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 5.0, 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. Versions 8.4, 9.0 and 9.1 are known to work with OMERO 5.0; 9.1 is recommended.

Compatibility matrix

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

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

[1]

Not suggested; see #4902

[2]

Configuration may be necessary; see #5662

[3]

Not suggested; see #5861

[4]

Unsupported; see #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.

See also

PostgreSQL

Interactive documentation for the current release of PostgreSQL.

Connections and Authentication

Section of the PostgreSQL documentation about configuring the server using postgresql.conf.

Client Authentication

Chapter of the PostgreSQL documentation about configuring client authentication with pg_hba.conf.