could not connect to server connection refused

Подобная ошибка возникает, если доступ к серверу ограничен файерволом или PostgreSQL не принимает подключение по указанному адресу/порту (10.0.2.15). Настройка файервола выходит за рамки тематики данного сайта, поэтому ниже приводится только решение проблемы, связанной с настройкой PostgreSQL.

Решение¶

Необходимо зайти на сервер по ssh и проверить статус СУБД командой:

Pезультат выполнения команды означает, что PostgreSQL принимает подключения по адресу 127.0.0.1 и порту 5432. Чтобы изменить настройки, понадобится отредактировать файл postgresql.conf

Найти местонахождение файла можно командой:

Надо указать PostgreSQL, что необходимо принимать подключения по всем адресам:

и перезагрузить СУБД:

Теперь необходимо проверить настройки:

Теперь доступ к базе данных есть с любого адреса. Можно попытаться подключиться к базе данных:

Если сервер доступен, то будет получен доступ к базе данных postgres:

Linux and Unix tutorials for new and seasoned sysadmin

Linux and Unix tutorials for new and seasoned sysadmin

Q. When I try to connect remote PostgreSQL, I am reciving an error which read as follows:

Adblock detected ��

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.

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:

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

Your support makes a big difference:

I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft takes a lot of my time and hard work to produce. If everyone who reads nixCraft, who likes it, helps fund it, my future would be more secure. You can donate as little as $1 to support nixCraft:

20 comment

$ 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 🙂

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:

listen_addresses = ‘*’
port = 5432

Bounced the db and voila, I was able to connect and to run queries.

Hope this helps.

Forgot to mention the version of my postgresql db. It’s 8.1.4

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

please help me.i try to connect to Postgresql sever by LAN
i already configured following step but not success
here my config on flatform Window
pg_hba.conf:
host all all 192.168.1.37/32 md5
postgresql.conf:
i try all
#listen_addresses = ‘*’
port = 5432

it’s still not working
thanks anyway!

I Remote PostgreSQL Access On the web

how can i Connect to my postgresql database on my server remotely through the web/internet and manage it directly from your PC?and even connect some application so i run transaction?

I have the same problem as quocvietit. I’m also in Windows.

Can anybody help me with this…

I’ve postgre db on redhat linux. It’s been configured to a single network earlier viz., 192.168.0.1 but now I want to access the same db from different vlans(192.168.0.1 , 192.168.1.1……to 192.168.20.1). there are about 20 machines on each network. I’m able to access from the client machines which are on 192.168.0.1… network but where as when I try to access from others it says “I don’t permissions to access the db”. Pls can anybody help in this regard…

I opened this file but
$ vi /var/lib/pgsql/data/pg_hba.conf but couldnot find
host all… instead if find
host = ” ”

how can I solve this

user account postgres does exist but i can’t login to it hence postgres service does not start.how do i go about this?

hiii mohad mekhelfi ana algerie b/amer (algerie )

I have same problem.
I use Windows and PostgreSql 9.0.4
anybody help?
So thanks!

Hi everyone , i’m havin’ same problem , Postgres 8.4 server does start , but when i try to run OpenErp Client , an error appear in the db field : Server couldnt start !! Could Anybody help me ppl ….Configuration : win7 32bits ,

Could’not connection to the server

And how about this error in postgres 9.0?
FATAL: unrecognized configuration parameter “tcpip_socket”

while installing postgres sql 8.0 in windows an error occurs ‘port number already in use please specify another ‘ how can i solve this

meu banco de dados postgresql não esta abrindo ele manda msg dizendo que não consegue se comunicar com o servidor ,estou usando o servidor virtual apache ,o que faço para não perder os meus dados ?, já até reinstalei o apache mais não resolve,o sistema é windows 7

What a wonderful post and the tremendous response from Rajeev. It took me hardly 3 mins to configure this and access PG remotely.

I am running on Fedora23 with postgresql-9.4.6-1.fc23.x86_64

I have adjusted the postgresql.conf and pg_hba.conf and the firewall so the server listens on my local network.

When I reboot the server machine (sudo shutdown -r now), I am initially not able to connect to the postgresql server. However, if I restart the server:

systemctl restart postgresql.conf

Then I am able to connect to the server from a machine on the network.

The restart command in the previous comment is really:

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

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:

listen_addresses = ‘*’
port = 5432