Troubleshooting a failed connection

This recipe is all about what you should do when things go wrong.

Bear in mind that 90 percent of problems are just misunderstandings, and you'll quickly be on track again.

How to do it...

Here we've made a checklist to be followed if a connection attempt fails:

• Check whether the database name and the username are accurate. You may be requesting a service on one system when the database you require is on another system. Recheck your credentials; ensure that you haven't mixed things up and that you are not using the database name as the username, or vice versa. If you receive too many connections, then you may need to disconnect another session before you can connect, or wait for the administrator to re-enable the connections.
• Check for explicit rejections. If you receive the pg_hba.conf rejects connection for host... error message, it means your connection attempt has been explicitly rejected by the database administrator for that server. You will not be able to connect from the current client system using those credentials. There is little point in attempting to contact the administrator, as you are violating an explicit security policy with what you are attempting to do.
• Check for implicit rejections. If the error message you receive is no pg_hba.conf entry for..., it means there is no explicit rule that matches your credentials. This is likely an oversight on the part of the administrator and is common in very complex networks. Contact the administrator and request a ruling on whether your connection should be allowed (hopefully) or explicitly rejected in the future.
• Check whether the connection works with psql. If you're trying to connect to PostgreSQL from anything other than the psql command-line utility, switch to that now. If you can make psql connect successfully but cannot make your main connection work correctly, then the problem may be in the local interface you are using.
• PostgreSQL 9.3 and later versions ship the pg_isready utility, which checks the status of a database server, either local or remote, by establishing a minimal connection. Only the hostname and port are mandatory, which is great if you don't know the database name, username, or password. The following outcomes are possible:
  • The server is running and accepting connections.
  • The server is running but not accepting connections (because it is starting up, shutting down, or in recovery).
  • A connection attempt was made, but it failed.
  • No connection attempt was made because of a client problem (invalid parameters, out of memory, and so on).
  • Check whether the server is up. If a server is shut down, then you cannot connect. The typical problem here is simply mixing up the server to which you are connecting. You need to specify the hostname and port, so it's possible that you are mixing up those details.
  • Check whether the server is up and accepting new connections. A server that is shutting down will not accept new connections, apart from superusers. Also, a standby server may not have the hot_standby parameter enabled, preventing you from connecting.
  • Check whether the server is listening correctly, and check the port to which the server is actually listening. Confirm that the incoming request is arriving on the interface listed in the listen_addresses parameter. Check whether it is set to * for remote connections and localhost for local connections.
  • Check whether the database name and username exist. It's possible that the database or user no longer exists.
  • Check the connection request, that is, check whether the connection request was successful and was somehow dropped after the connection. You can confirm this by looking at the server log when the following parameters are enabled:

                  log_connections = on
                  log_disconnections = on

• Check for other reasons for disconnection. If you are connecting to a standby server, it is possible that you have been disconnected because of Hot Standby conflicts.
  See Chapter 12, Replication and Upgrades, for more information.

There's more...

Client authentication and security are the rapidly changing areas over subsequent major PostgreSQL releases. You will also find differences between maintenance release levels. The PostgreSQL documents on this topic can be viewed at http://www.postgresql.org/docs/current/interactive/client-authentication.html.

Always check which release level you are using before consulting the manual or asking for support. Many problems are caused simply by confusing the capabilities between release levels.