Solving common connection problems

There are a few common problems when dealing with database connections, and this section explains them in order to ease your task of getting connected to your cluster.

Please note that the solutions provided here are just for testing purposes and not for production usage. All of the security settings will be explained in later chapters, so the aim of the following subsection is just to help you get your test environment usable.

Database “foo” does not exist

This means either you misspelled the name of the database in the connection string or you are trying to connect without specifying the database name.

For instance, the following connection fails when executed by an operating system user named luca because, by default, it is assuming that the user luca is trying to connect to a database with the same name (meaning luca) since none has been explicitly set:

$ psql
psql: error: could not connect to server: FATAL:  database "luca" does not exist

The solution is to provide an existing database name via the -d option or to create a database with the same name as the user.

Connection refused

This usually means there is a network connection problem, so either the host you are trying to connect to is not reachable or the cluster is not listening on the network.

As an example, imagine PostgreSQL is running on a machine named venkman and we are trying to connect from another host on the same network:

$ psql -h venkman -U luca template1
psql: error: could not connect to server: could not connect to server: Connection refused
        Is the server running on host "venkman" (192.168.222.123) and accepting
        TCP/IP connections on port 5432?

In this case, the database cluster is running on the remote host but is not accepting connections from the outside. Usually, you have to fix the server configuration or connect to the remote machine (via SSH, for instance) and open a local connection from there.

In order to quickly solve the problem, you have to edit the postgresql.conf file (usually located under the PGDATA directory) and ensure the listen_address option has an asterisk (or the name of your external network card) so that the server will listen on any available network address:

listen_addresses = '*'

After a restart of the service, by means of the restart command issued to pg_ctl, the client will be able to connect. Please note that enabling the server to listen on any available network address might not be the optimal solution and can expose the server to risks in a production environment. Later in the book, you will learn how to specifically configure the connection properties for your server.

No pg_hba.conf entry

This error means the server is up and running and able to accept your request, but the PostgreSQL built-in Host-Based Access (HBA) control does not permit you to enter.

As an example, the following connection is refused:

$  psql -h localhost -U luca template1
psql: error: could not connect to server: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "luca", database "template1", SSL off

The reason for this is that, inspecting the pg_hba.conf file, there is no rule to let the user luca in on the localhost interface. So, for instance, adding a single line such as the following to the pg_hba.conf file can fix the problem:

host all luca 127.0.0.1/32 trust

You need to reload the configuration in order to apply changes. The format of every line in the pg_hba.conf file will be discussed later, but for now, please assume that the preceding line instruments the cluster to accept any connection incoming from localhost by means of the user luca.