Troubleshoot a PostgreSQL "Could not connect to server" Error

Table of Contents

Introduction

Learn how to troubleshoot one of the most common PostgreSQL errors, "Could not connect to server." There are several different variations of this error.

Requirements

  • 1&1 Cloud Server running Linux (CentOS 7 or Ubuntu 16.04)
  • PostgreSQL installed and running.

For more information on setting up and using PostgreSQL, see our article Install and Use PostgreSQL.

"Could not connect to server: No such file or directory"

The PostgreSQL error "No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.xxxx"?" usually means that PostgreSQL is not running.

Use the systemctl status postgresql command to check PostgreSQL's status:

user@localhost:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: **active** (exited) since Thu 2017-03-23 21:34:03 UTC; 14s ago
 Main PID: 24289 (code=exited, status=0/SUCCESS)

CGroup: /system.slice/postgresql.service

Mar 23 21:34:03 localhost.localdomain systemd[1]: Starting PostgreSQL RDBMS...
Mar 23 21:34:03 localhost.localdomain systemd[1]: Started PostgreSQL RDBMS.
Mar 23 21:34:08 localhost.localdomain systemd[1]: Started PostgreSQL RDBMS.

If the status is shown as active, restart PostgreSQL with the systemctl restart postgresql command. If the status is shown as inactive, start PostgreSQL with the systemctl start posgresql command.

"Could not connect to server: Connection refused"

First, use systemctl status posgresql to verify that PostgreSQL is running. You may want to restart it with systemctl restart postgresql for good measure.

If this does not fix the problem, the most likely cause of this error is that PostgreSQL is not configured to allow TCP/IP connections.

To correct this, edit your posgresql.conf file:

  • Ubuntu 16.04: sudo nano /etc/postgresql/9.5/main/posgresql.conf
  • CentOS 7: sudo nano /usr/pgsql-10/share/postgresql.conf

Check the listen_address configuration. To allow TCP/IP connections, it should be set to 0.0.0.0 (to allow connections from all IP addresses) or to the specific IP address of the server it will allow to connect.

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

This error can also be generated when the connection is blocked by a firewall. Note that all 1&1 Cloud Servers are affected by the default Firewall Policy which is controlled from the Cloud Panel. For more information on changing the Firewall Policy, consult our documentation on the topic.

Content provided by 1&1

Comments

Tags: PostgreSQL / Database