My mistakes

PostgreSQL psql: could not connect to server: Connection refused

Posted on: October 28, 2008

A. First make sure PostgreSQL server has been started to remote server.

# /etc/init.d/postgresql start

If it is running and you get above 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.

Step # 1: Allow remote IP address to access PostgreSQL

You need to open file called /var/lib/pgsql/data/pg_hba.conf. Login as postgres user using su command:
$ su - postgres
$ vi /var/lib/pgsql/data/pg_hba.conf

Now append following line. Let us say you would like to give access to 192.168.0.0/24 network:
host all all 192.168.0.0/24 trust
Please replace 192.168.0.0 and 255.255.255.0 to reflect the actual network IP address range of the clients system in your own network.

Save close the file.

Step # 2: Allow communication over TCP/IP

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf
$ vi /var/lib/pgsql/data/postgresql.conf
Now bind and open TCP/IP port by setting tcpip_socket to true:
tcpip_socket = true

Save and close the file.

Step # 3: Restart PostgreSQL server

Restart the PostgreSQL server with the following command
# /etc/init.d/postgresql restart

This will open default port 5432.

Step # 4: Test your setup

Use psql command from client system as follows:
psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME

Connect to remote server by IP address 192.168.0.3 and login using testuser to connect to testdb database, use:
$ psql -h 192.168.0.3 -U testuser -d testdb
Where,

  • -h 192.168.0.3 : Specifies the host name of the machine or IP address (192.168.0.3) on which the server is running.
  • -U testuser : Connect to the database as the testuser username instead of the default. You must have account and permission to connect as testuser.
  • -d testdb : Specifies the name of the database (testdb) to connect to.

When your are trying to connect a remote pgsql db on a Suse Linux 9 or others, It did not have the tcpip_socket parameter in postgresql.conf

To overcome this, I had to uncomment the following settings:

#—————————————————————————
# CONNECTIONS AND AUTHENTICATION
#—————————————————————————

# – Connection Settings –

listen_addresses = ‘*’
port = 5432

Advertisements

1 Response to "PostgreSQL psql: could not connect to server: Connection refused"

Explained in nice way 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: