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 0.0.0.0:5432            0.0.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'
/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:

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 [http://www.postgresql.org 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 (127.0.0.1) as follows:

PostgreSQL 8.x (CIDR notation)

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

PostgreSQL 7.4 (netmask notation)

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
# IPv4-style local connections:
host    all         all         127.0.0.1         255.255.255.255   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

• PostgreSQL 8.2 Interactive Manual (HTML)
• PostgreSQL 8.2 Interactive Manual (Chapter 20: Client Authentication)