Troubleshooting PostgreSQL Database Connection Issues

Installing a new PostgreSQL database AND being able to connect to it can be a daunting task at times.  When you use an installer (Windows) or a package manager (Linux), the installation usually comes with all of the sensible defaults turned on and ready to go.  But, if you are dealing with a “special” custom installation, you may run into some connection issues.  Ain’t that special?

In this blog post, I will assume that you have already installed a PostgreSQL database instance on Linux.  I am working with PostgreSQL 10.4 and CentOS 7, but these tips should be general enough to help you troubleshoot on other platforms.  I am also assuming, if you’re reading this, that you have a client program like DBeaver (https://dbeaver.io/) on Windows, or command line psql on Linux running in a terminal, AND that your client cannot make a connection to your brand spanking new PostgreSQL database instance.  Without going into thousands of pages of reference material, here are four of the most common things to check up on when troubleshooting PostgreSQL connection issues.

1. Is there network connectivity between client and server?
2. Is a firewall on the server blocking your connections?
3. Is PostgreSQL listening to the correct network interface?
4. Is the client allowed by pg_hba.conf?

Network Connectivity

It sounds dumb, but the first thing to check is the network connection between the client and server machines.  Everything is plugged in, right?  Then try to ping the server from the client and vice versa.  If the machines are not ping-able, then you most likely have a network connectivity problem.  Here are some general troubleshooting ideas for network connectivity.

• Check that you can ping other sites, like http://www.google.com. If you can, that points to a specific problem between your client and server.
• You just installed PostgreSQL on the server; is the database server also freshly installed?  Make sure it has the correct networking parameters, and make sure the interface is configured to come up at boot time.
  • On CentOS, the network configuration is kept in a file in /etc/sysconfig/network-scripts, named something like ifcfg-ifacename.  Check that the contents of this file are correct for your network interface, and ONBOOT=yes and run systemctl restart network
• If your server is running in a Virtual Machine guest OS and the database client is on running the host, check the virtual machine manager and make sure that networking is enabled from host to guest.
• If you work in a secure corporate “double-secret probation” network environment, it may also be that ICMP is blocked and that ping won’t work.  Submit a ticket to your networking team to find out if that is an issue and for help troubleshooting connectivity.

Firewall Issues

A firewall on the server may be blocking communication to the port that PostgreSQL is listening on.  On the server machine, you can create custom firewall rules to allow network traffic to that port.  Unless you changed it in the postgresql.conf configuration file, the default port that PostgreSQL uses is 5432.  On CentOS, the firewall service is firewalld.  You can check to see if it is running by issuing the command

#] systemctl status firewalld

It will tell you if the service is disabled/inactive, stopped or running.  As a matter of fixing firewall issues, I don’t recommend stopping/disabling the firewall entirely. Certainly do not do this in production, although it can be OK on an isolated testbench network segment, like a private Hyper-V virtual network.  It is much better to add a custom firewall rule to the CentOS firewalld service for PostgreSQL

• Look up the port number in postgresql.conf file in the PostgreSQL data directory for your database instance.  (The default value for PostgreSQL is 5432.)
• For each host or network segment you want to allow through the firewall, run the following command
  firewall-cmd --add-rich-rule='rule family=ipv4 source address=192.168.10.100/32 port port=5432 protocol=tcp accept' --permanent
  This will allow connections from the host with the exact IP address 192.168.10.100 to make connections to the server machine on port 5432. Your parameters will vary of course.
• Yes, the word “port” appears twice in the above command.  For historical reasons, with firewall rules on CentOS, the port is actually called the “portport”.  (Just kidding, but not about the double occurrence of the word “port” – you really do need it there twice.)
• Don’t forget to reload the firewall after creating new rules. Run the command
  firewall-cmd --reload
  to do this.

Listener Network Interface

In the PostgreSQL postgresql.conf configuration file there are many settings, including the listen_addresses setting.

#listen_addresses = 'localhost'

PostgreSQL provides configuration files with many settings like this one:  the setting is commented out, but it is set to the default value. This particular setting means that PostgreSQL is by default only listening for connections that come over the localhost network interface; eg local connections. If your client is trying to connect from another machine, then it won’t be able to connect to PostgreSQL. To fix this, uncomment the setting and change it to

listen_addresses = '*'

With this value, PostgreSQL will listen for connections on any interface.  Like with the firewall, before the setting takes effect you need to reload the server configuration.  You can reload the server configuration without restarting the server.  If the data folder for your instance is in the location /usr/data/pgdata, you can reload configuration with the command

pg_ctl -D /usr/data/pgdata reload

(Remember to substitute the folder location of the PostgreSQL on your machine.)  If you have only one network card in your machine, it makes sense to just listen on all interfaces. If you have multiple network cards, it is best practice to listen only on the interfaces over which you expect PostgreSQL clients to connect to prevent spoofed connection attempts.  More details about this setting can be found in the manual.

pg_hba.conf

This file provides another layer of connection security based on the database user that you specified in the client.  

Fortunately, if this is the problem, the client will have received and displayed an error message to the effect that the user does not exist in the pg_hba.conf file.  Pretty.  Stinking.  Obvious.

So to fix this issue, it is sufficient to add an entry to the end of this file.  The file is pretty well commented, but briefly, each entry consists of a line

host database_name user_name ip_address authentication_method

So if I had a client connecting with user name “tom_bombadil” to the database “old_forest” from a single computer with IP address 192.168.10.100 and we want to require a password, the line would be

host old_forest tom_bombadil 192.168.10.100/32 md5

There is a complete discussion of these parameters at the PostgreSQL site: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Conclusion

Connecting to a brand new PostgreSQL instance can be mildly challenging if you’re installing from source or if you have non-standard configuration parameters or went around the installer in some way.  Not all of these issues necessarily involve PostgreSQL either.  Hopefully this list of common issues helped get you up and running!  If you had any other experiences that you think could be helpful, feel free to relate those in the comments!