Tag: centos

PostgreSQL psql: could not connect to server: Connection refused

First thing to be checked is to see if postgresql service is running on the server.
# /etc/init.d/postgresql status

If it is running and you get the error, you need to add enable TCP/IP support. By default, the PostgreSQL server only allows connections to the database from the local machine or localhost. This is a security feature of PostgreSQL.

To allow remote IP addresses/servers to access postgresql server we need configure it accordingly. For this we need to edit the config file /var/lib/pgsql/data/pg_hba.conf.

# vi /var/lib/pgsql/data/pg_hba.conf

You will find

host    all         all         127.0.0.1          255.255.255.255   md5

Now add a new line as below

host    all         all     123.237.1.158      255.255.255.0      trust

where 123.237.1.158 is the IP address from which you are trying to access the postgresql server.

Save and close the file.

Also you may need to enable TCP/IP communication, which can be done in the configuration file /var/lib/pgsql/data/postgresql.conf. Ensure that the setting tcpip_socket is set to true.

# vi /var/lib/pgsql/data/postgresql.conf

tcpip_socket = true

Save and close the file.

Now restart PostgreSQL server, so that the config changes are updated.

# /etc/init.d/postgresql restart

This will open default port 5432.

You may test the connectivity using 3rd party application like pgadmin or using psql client. The psql command from would be as follows.

# psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME

Notes : This documentation is with regard to Centos. However same holds good for debian and also ubuntu. The configuration files paths on debian/ubuntu is as below

/etc/postgresql/7.4/main/pg_hba.conf

/etc/postgresql/7.4/main/postgresql.conf

where 7.4 is the postgresql version installed on the server.