Psql ошибка не удалось подключиться к серверу connection refused

An incorrect entry in the configuration file or firewall restriction generally triggers PostgreSQL connection refused error.

As a part of our PostgreSQL Management Services, we help webmasters and web hosts resolve PostgreSQL such as this every day.

Today let’s discuss the top reasons for connection refused error in PostgreSQL and how to resolve the error.

What triggers PostgreSQL connection refused error?

PostgreSQL is a highly extensible and handy database management system. And thus it is one of the popular database systems.

But many times, when users try to connect to psql it ends up in connection errors. psql is the interactive terminal-based program, that enables PostgreSQL queries.

A typical error message looks like

Error: psql: could not connect to server: Connection refused Is the server running on host “xxx.xxx.xxx.xxx” and accepting TCP/IP connections on port 5432?

When the PostgreSQL’s server daemon is not running in the server, it can trigger connection refused error. At the same time, several other reasons can also trigger this error.

Let’s discuss a few reasons why the psql service fails. We will also discuss how our Support Engineers fix this error.

PostgreSQL service status

First parameter to cross-check during a psql connection refused error is the status of the PostgreSQL service.

We can confirm this by cross-checking if a Postgres process in running in the server. In a Linux like environment, we can use

ps -ef | grep postgres

service postgresql status

If the service is not running in the server, then we restart it using the command.

service postgresql restart

At times, this failure could be a result of a corrupted Postgresql service. A reinstall of the service with the apt-get install or yum install would be required to fix the issue.

Enable TCP/IP connections

If the error is not related to the failed status of the service, it is more likely to be related to TCP/IP connections disabled in the Postgresql configuration file.

To correct this, we need to edit posgresql.conf file. The file location in Ubuntu and Centos is :

In Ubuntu: vi /etc/postgresql/9.5/main/posgresql.conf

in CentOS 7: vi /usr/pgsql-10/share/postgresql.conf

The listen_address parameter in the configuration file determines the restrictions on TCP/IP connections. To allow TCP/IP connections, from all IP addresses it should be set to 0.0.0.0.  Alternatively, to allow connections to the specific IP address of the server, listen_address parameter need to be edited with only those IP addresses.

If this configuration is left blank or set to localhost, PostgreSQL will not allow external TCP/IP connections.

Finally, we restart the service to save the changes.

Allow remote host

If the users are not able to connect to psql from a remote IP, then also we get the same error.

In this case, the Support Team edits the postgresql.conf file and allow the IP from which the customer is trying to access.

Finally, we restart the service to save the changes.

Firewall restrictions

At times, the restrictions placed by the server firewall can also trigger connection refused error.

If the psql port 5432 is not open for connection or if there is any restriction on the IP address from which the TCP/IP connection occurs, it may trigger connection refused error.

Our Support engineers cross-check the firewall for any restrictions and then remove them to fix the issue.

[Still, having trouble connecting PostgreSQL? – We will fix it for you.]

Conclusion

In short, several reasons can trigger the PostgreSQL connection refused error. This includes corrupted Postgresql service, firewall restrictions, incorrect entries in the configuration file etc. We saw the top 4 reasons for this error and how our Support Engineers fix them.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

In this document I am going to discuss about some of the most frequent errors faced by users while connecting to PostgreSQL. This is document is to help the beginners who started working on PostgreSQL. PostgreSQL has a process called postmaster which acts as listener process, by default the process listens on 5432.

There are different tools available to connect to PostgreSQL, here I am going to use the psql command line tool but there is no difference in error messages.

ERROR – 1

[root@localhost ~]# psql
psql: error: FATAL: role “root” does not exist

By default “psql” command line tool use the operating system user as the database user and localhost the hostname if the user name is not passed as argument, here I logged in as the root user and tried to login to PostgreSQL without username which caused the error.

Solution

give the username when logging into the database, by default the psql command line take the username as the database name, so no need to pass the database as I am trying to connect to PostgreSQL.

psql -U postgres -h localhost

ERROR – 2

[root@localhost ~]# psql -U postgres
psql: error: FATAL: Peer authentication failed for user “postgres”

Solution

By default PostgreSQL installation using yum repository configure the pg_hba.conf with peer, ident authentication. So change the peer and ident authentication methods to md5 and reload the configuration.

[root@localhost data]# psql -U postgres
Password for user postgres: 
psql (12.5)
Type "help" for help.

postgres=# 

ERROR – 3

[root@localhost data]# psql -h 192.168.159.151 -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “192.168.159.151” and accepting
TCP/IP connections on port 5432?

This is the common error users get when they connect to PostgreSQL. The error represents there is no listener for host and port combination.

Solution

Most common issue is listener_address parameter is set to ‘localhost’ or port set to a non default value.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 localhost
(1 row)

postgres=# show port;
 port 
------
 5432
(1 row)

Here the issue is listener_address is localhost, so changed it to ‘*’ where it listens on all ip’s in my host. I have only one ip configured, if your server has multiple ip’s then set the ip address on which the PostgreSQL connections should happen.

Configure the listener_address or change the port and restart the PostgreSQL cluster.

postgres=# alter system set listen_addresses to '*';
ALTER SYSTEM
postgres=# q
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 07:12:20.317 PST [3051] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv6 address "::", port 5432
2020-12-20 07:12:20.319 PST [3051] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-20 07:12:20.320 PST [3051] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 07:12:20.332 PST [3051] LOG:  redirecting log output to logging collector process
2020-12-20 07:12:20.332 PST [3051] HINT:  Future log output will appear in directory "log".
 done
server started

ERROR – 4

-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “192.168.159.151”, user “postgres”, database “postgres”, SSL off

This is another common error which users will face due to missing entry for the user host address (ip address) in pg_hba.conf file. host based authentication file presents in data directory which has a list of ip addresses or host addresses allowed to connect to PostgreSQL.

Solution

Make an entry like below for the user host or subnet in pg_hba.conf file. Any ip address with 192.168.X.X will be able to connect to the host.

host	all		all		192.168.0.0/16		md5

ERROR – 5

-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: sorry, too many clients already

-bash-4.2$ psql -h 192.168.159.151 -U viswamitra -d postgres
psql: error: FATAL: remaining connection slots are reserved for non-replication superuser connections

-bash-4.2$ psql -U viswamitra -d postgres -h 192.168.159.151
psql: error: FATAL: too many connections for role “viswamitra”

This is also a common error users face after started using the database in application. This errors will come after the connection limit is reached to the maximum configured value.

Connection limit can be set at different levels

1. cluster – which is applicable for all users and databases
2. user – which is applicable for the user
3. database – which is applicable for the given database

Solution

Login to the database as a super user, if there are any idle connections from longer time close them and check the application connection pool settings to close the idle connections after some time interval.

postgres=# select pid,datname,usename,application_name,state,now()-state_change as idle_duration from pg_stat_activity where state = 'idle';
 pid  | datname  |  usename   | application_name | state |  idle_duration  
------+----------+------------+------------------+-------+-----------------
 3656 | alex     | postgres   | psql             | idle  | 00:14:06.647055
 3652 | alex     | postgres   | psql             | idle  | 00:14:11.718486
 3623 | postgres | viswamitra | psql             | idle  | 00:15:47.530912
(3 rows)

postgres=# select pg_terminate_backend(3656);
 pg_terminate_backend 
----------------------
 t
(1 row)

If there are no idle sessions to cancel and the error is first and second one in the list increase the “max_connections” in postgresql.conf file. This requires a reboot of the PostgreSQL cluster to make it effect.

postgres=# alter system set max_connections to '1000';
ALTER SYSTEM
postgres=# 

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart

For third error (5.3) , the connection limit is configured at the user level, it can be changed using the alter user statement.

postgres=# alter user viswamitra connection limit 100;
ALTER ROLE