Personal tools
  • We're Hiring!

You are here: Home Documentation OMERO v4.2.2 OMERO.server OMERO and PostgreSQL

OMERO and PostgreSQL

OMERO and PostgreSQL

In order to be installed, OMERO.server requires a running PostgreSQL instance that is configured to accept connections over TCP. The following explains how to ensure that you have the correct PostgreSQL version and that it is installed and configured correctly. For Windows-specific installation instructions, first see the Windows install page.

Ensuring you have a valid PostgreSQL version

Note that for OMERO 4.3 PostgreSQL 8.3 or higher is strongly recommended (see known-limitations). If you are using OMERO 4.2 PostgreSQL 8.1 or higher is required. OMERO v4.1 (or lower) then PostgreSQL 7.4 or higher will be needed.

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

# Unix
$ createuser -V
createuser (PostgreSQL) 8.1.8
$ psql -V
psql (PostgreSQL) 8.1.8
$ createdb -V
createdb (PostgreSQL) 8.1.8
PostgreSQL 8.1 is missing the functions pg_advisory_lock and pg_advisory_unlock which may lead to infrequent exceptions on insert. If 8.1 is your default PostgreSQL installation, as is the case on CentOS 5, you may consider using a more up-to-date version. We suggest the installer from EnterpriseDB

Checking PostgreSQL port listening status

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

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

# Windows
C:\> netstat -an | find "5432"

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:

# Unix
$ sudo find /usr -name 'postgresql.conf'
$ sudo find /var -name '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:

PostgreSQL 8.x (listen_address)

For PostgreSQL 8.x (8.0.x, 8.1.x, 8.2.x, 8.3.x, etc.) the area of the configuration file you're concerned about should look like 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 7.4 (tcpip_socket)

For PostgreSQL 7.4 the area of the configuration file you're concerned about should look like this:

# - Connection Settings -

#tcpip_socket = false
tcpip_socket = true
max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.

You can find out more about PostgreSQL client configuration on the [ PostgreSQL website].

PostgreSQL HBA (host based authentication)

The final piece of the PostgreSQL authentication and authorization puzzle is the so called host based authentication file, pg_hba.conf. OMERO.server must have permissions to connect to databases that have been created in your PostgreSQL instance. You can make sure that it does by examining the contents of the pg_hba.conf file itself. It's important that you have at least one line allowing connections from the loopback address ( as follows:

PostgreSQL 8.x (CIDR notation)

# IPv4 local connections:
host    all         all          md5

PostgreSQL 7.4 (netmask notation)

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
# IPv4-style local connections:
host    all         all   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 unless you know what you are doing.

Further Reading

Document Actions