PostgreSQL psql: could not connect to server: Connection refused
Q. When I try to connect remote PostgreSQL, I am reciving an error which read as follows:
psql: could not connect to server: Connection refused
Is the server running on host host.domain.com and accepting
TCP/IP connections on port 5432?
How do I fix this problem? I am using CentOS 4.x version.
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.1.0/24 network:
host all all 192.168.1.0 255.255.255.0 trust
Please replace 192.168.1.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.1.5 and login using vivek user to connect to sales database, use:
$ psql -h 192.168.1.5 -U vivek -d sales
Where,
- -h 192.168.1.5 : Specifies the host name of the machine or IP address (192.168.1.5) on which the server is running.
- -U vivek : Connect to the database as the vivek username instead of the default. You must have account and permission to connect as vivek user.
- -d sales : Specifies the name of the database (sales) to connect to.
See also:
Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
Related Other Helpful FAQs:
- Sorry too many clients when trying to connect to PostgreSQL database server - solution
- CentOS / RHEL Linux Install Postgresql Database Server
- Howto: Debian / Ubuntu Linux Install PostgreSQL Database Server
- PostgreSQL add or create a user account and grant permission for database
- Linux Virtual Private Network setup
Discussion on This FAQ
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
March 23rd, 2007 at 10:48 am
$ su - postgres
$ vi /var/lib/pgsql/data/pg_hba.conf
You can also use 192.168.1.31/24 instead of 192.168.1.31 255.255.255.0 (CIDR-address), kinda config file will look “cleaner”, and I would use “md5″ instead of “trust” for further peace-of-mind
January 9th, 2008 at 8:14 am
Thanks for the fantastic FAQ. Just what I needed as a newbie with pgsql.
Just wanted to mention here that was trying to connect a remote pgsql db on a Suse Linux 9. 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
Bounced the db and voila, I was able to connect and to run queries.
Hope this helps.
Thanks
Rajeev
January 9th, 2008 at 8:16 am
Forgot to mention the version of my postgresql db. It’s 8.1.4
January 31st, 2008 at 5:51 am
Hi,
this guide was a good help for me.
only thing as rajeev said their is no parameter with the name tcpip_socket in Ubuntu also.
so used following things in the postgresql.conf config file
listen_addresses = ‘*’
port = 5432