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!

How to Monitor PostgreSQL Connections

 How to identify and terminate connections that are lying idle and consuming resources.

1. States of a connection
2. Identifying the connection states and duration
3. Identifying the connections that are not required
4. Terminating a connection when necessary

 

In order to make modifications or read data from a PostgreSQL database, the first thing we need to do is to create connections. However, each connection comes with overhead in terms of both process and memory; hence a system with limited resources (read, hardware)  can only handle a certain number of connections. Once it goes beyond that, it will start throwing errors or refusing connections. PostgreSQL does a good job restricting the connections in postgresql.conf.

In this post we will look at the types of states that exist for connections in PostgreSQL. We will show how to find out if that connection is doing work or has been lying idle for a period of time, in which case it should be terminated to recover the connection and resources. We will go through the following steps:

1. Understanding the states of a connection
2. Identifying the states and duration of the current connections
3. Identifying the connections that are not required
4. Terminating a connection when necessary

 

1. States of a connection

Once a connection in PostgreSQL is created, it can perform various operations that lead to changes in states. Based on the state and the time the connection has been in that state, an informed decision can be made as to whether the connection is active or has been left idle/abandoned. 

It is worth mentioning that if the connection is not explicitly closed by the application, it will remain available, thereby consuming resources—even when the client has disconnected.

These are the four states a connection can have:

1. active: This indicates that the connection is working. 
2. idle: This indicates that the connection is idle and we need to track these connections based on the time that they have been idle.
3. idle in transaction: This indicates the backend is in a transaction, but it is currently not doing anything and could be waiting for an input from the end user.
4. idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. This also needs to be monitored based on the time since it has been idle.

 

2. Identifying the connection states and duration

The pg_stat_activity view in the PostgreSQL catalog tables gives you information regarding what a connection is doing and how long it has been in that state. If you run the query to get the values from the view, you get the following output for the state of each connection:

 

From the above output the one value which we are looking for is the “state”. We can use that to find out which queries are in which state and then we can dig further. So, we can modify the query to show specific records related to just the queries which are idle:

Select * from pg_stat_activity where state=’idle’;

 

3. Identifying the connections that are not required

We can modify the query a bit more to narrow down the information we are looking for so that we can plan an action on that particular connection. We can do this by selecting just the PIDs and the query states for the PIDs that are idle. We also need to monitor the time since the connection has been idle to check that we do not have any abandoned connections wasting our resources as well. So this would mean:

Select pid, usename, application_name, backend_start, state_change, state from pg_stat_activity where state=’idle’;

 

4. Terminating a connection when necessary

Once we have narrowed down the query that is either in a hang state or has been idle for a long time, we can use this query to simply kill the backend process without affecting the operations of the server:

SELECT pg_terminate_backend(PID);

 

database sessions query

Query

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

Columns

• process_id - process ID of this backend
• username - name of the user logged into this backend
• database_name - name of the database this backend is connected to
• client_address - IP address of the client connected to this backend
• application_name - name of the application that is connected to this backend
• backend_start - time when this process was started. For client backends, this is the time the client connected to the server.
• state - current overall state of this backend. Possible values are:
  • active
  • idle
  • idle in transaction
  • idle in transaction (aborted)
  • fastpath function call
  • disabled
• state_change - time when the state was last changed

Rows

• One row: represents one active connection
• Scope of rows: all active connections

Sample results

You can see session list on our test server.

CREATE CLUSTER

[root@postgres Desktop]# cd /data1/

[root@postgres data1]# mkdir prod

[root@postgres data1]# chown postgres:postgres prod/ -R

[root@postgres data1]# su - postgres

-bash-4.1$ initdb -D /data1/prod/ ---------------> starting new cluster instance 

-bash-4.1$ cd /data1/prod/

-bash-4.1$ vi postgresql.conf

port=5433

:wq

-bash-4.1$ pg_ctl -D /data1/prod/ start ---------------------> starting database

-bash-4.1$ ps -ef | grep postgres

root     17715 17705  0 13:51 pts/0    00:00:00 su - postgres

postgres 17716 17715  0 13:51 pts/0    00:00:00 -bash

postgres 17783     1  0 13:53 pts/1    00:00:00 /opt/PostgreSQL/10/bin/postgres -D ../data

postgres 17784 17783  0 13:53 ?        00:00:00 postgres: logger process                  

postgres 17786 17783  0 13:53 ?        00:00:00 postgres: checkpointer process            

postgres 17787 17783  0 13:53 ?        00:00:00 postgres: writer process                  

postgres 17788 17783  0 13:53 ?        00:00:00 postgres: wal writer process              

postgres 17789 17783  0 13:53 ?        00:00:00 postgres: autovacuum launcher process     

postgres 17790 17783  0 13:53 ?        00:00:00 postgres: stats collector process         

postgres 17791 17783  0 13:53 ?        00:00:00 postgres: bgworker: logical replication launcher   

root     17832 17741  0 13:59 pts/1    00:00:00 su - postgres

postgres 17833 17832  0 13:59 pts/1    00:00:00 -bash

postgres 17890     1  0 14:01 pts/1    00:00:00 /opt/PostgreSQL/10/bin/postgres -D /data1/prod

postgres 17892 17890  0 14:01 ?        00:00:00 postgres: checkpointer process                     

postgres 17893 17890  0 14:01 ?        00:00:00 postgres: writer process                           

postgres 17894 17890  0 14:01 ?        00:00:00 postgres: wal writer process                       

postgres 17895 17890  0 14:01 ?        00:00:00 postgres: autovacuum launcher process              

postgres 17896 17890  0 14:01 ?        00:00:00 postgres: stats collector process                  

postgres 17897 17890  0 14:01 ?        00:00:00 postgres: bgworker: logical replication launcher   

-bash-4.1$ psql -p 5433 -U postgres -d postgres

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

  data_directory  

------------------

 /data1/prod

(1 row)

postgres=# show port;

 port 

------

 5433

(1 row)

postgres=# select datname from pg_database;

  datname  

-----------

 postgres

 template1

 template0

(3 rows)

postgres=# 

postgres=# create table emp(name char);

CREATE TABLE

postgres=# insert into emp values('s');

INSERT 0 1

postgres=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | emp  | table | postgres

(1 row)

postgres=# \dt+

                      List of relations

 Schema | Name | Type  |  Owner   |    Size    | Description 

--------+------+-------+----------+------------+-------------

 public | emp  | table | postgres | 8192 bytes | 

(1 row)

PostgreSQL Basic Query1

-bash-4.1$ ./psql -p 5435 -U postgres -d postgres ---------------------

psql.bin (9.6.8)

Type "help" for help.

postgres=# show data_directory;

         data_directory          

---------------------------------

 /opt/PostgreSQL/9.6/bin/../data

(1 row)

postgres=# select current_user;

 current_user 

--------------

 postgres

(1 row)

postgres=# select version();

                                                 version                                                  

----------------------------------------------------------------------------------------------------------

 PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

(1 row)

postgres=# select datname from pg_database;

      datname       

--------------------

 postgres

 template1

 template0

 contrib_regression

 db96

(5 rows)

postgres=# select pg_backend_pid();

 pg_backend_pid 

----------------

          15013 

Switchover and Switchback in PostgreSQL10

 

postgres=# select application_name, state, sync_priority, sync_state from pg_stat_replication;

 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async

If Return  false so the server is running in Primary mode or Master

/u02/PostgreSQL/10/data

cat recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=replicator password=replicator host=192.168.0.106 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

recovery_target_timeline = 'latest'

trigger_file = '/u02/PostgreSQL/10/data/recovery.stop'

Now run the touch the file  (2nd step main)

touch /u02/PostgreSQL/10/data/recovery.stop

Standby has been promoted as master and a new timeline followed which you can notice in logs.

 2020-03-26 20:46:34 IST    LOG:  trigger file found: /u02/PostgreSQL/10/data/recovery.stop

 2020-03-26 20:46:34 IST    LOG:  redo done at 0/2B000028

 2020-03-26 20:46:34 IST    LOG:  last completed transaction was at log time 2020-03-25 19:45:14.642094+05:30

 2020-03-26 20:46:34 IST    LOG:  selected new timeline ID: 2

 2020-03-26 20:46:34 IST    LOG:  archive recovery complete

 2020-03-26 20:46:34 IST    LOG:  checkpoint starting: force

 2020-03-26 20:46:34 IST    LOG:  database system is ready to accept connections

 2020-03-26 20:46:34 IST    LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.028 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=14702 kB

cat recovery.conf

recovery_target_timeline = 'latest'

standby_mode = 'on'

primary_conninfo = 'user=replicator password=replicator host=192.168.0.107 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

trigger_file = '/u02/PostgreSQL/10/data/recovery.stop'

Check the data in primary and standby:-

Setup Replication (Master — Slave Setup) on PostgreSQL10

Below are step by step implementation 

Step 1 - Install PostgreSQL 10

Step 2 - Start and configure PostgreSQL 10

Step 3 - Configure Firewalld

Step 4 - Configure Master server

Step 5 - Configure Slave server

NOTE: Run the Step 1, Step 2 and Step 3 on all Master and Slaves.

In this step, we will configure a master server for the replication. This is the main server, allowing read and write process from applications running on it. PostgreSQL on the master runs only on the '10.0.15.10' IP address, and performs streaming replication to the slave server.

1) => edit the configuration file 'postgresql.conf'

Uncomment the 'listen_addresses' line and change value of the server IP address to '192.168.0.108'.

listen_addresses = '*' or  '192.168.0.108' 

2) => Uncomment 'wal_level' line and change the value to 'hot_standby'.

wal_level = hot_standby

3) VIMP: For the synchronization level, we will use local sync. Uncomment and change value line as below.

synchronous_commit = local

4) Enable archiving mode and give the archive_command variable a command as value.

archive_mode = on

archive_command = 'cp %p /u02/PostgreSQL/archive/%f'

5) For the 'Replication' settings, uncomment the 'wal_sender' line and change value to 2 (in this tutorial,  we use only 2 servers master and slave), and for the 'wal_keep_segments' value is 10.

max_wal_senders = 2

wal_keep_segments = 10

hot_standby = on

6) For the application name, uncomment 'synchronous_standby_names' line and change value to 'pgslave01'.

synchronous_standby_names = 'pgslave01'

7) Moving on, in the postgresql.conf file, the archive mode is enabled, so we need to create a new directory for archiving purposes.

Create a new directory, change its permission, and change the owner to the postgres user.

mkdir -p /u02/PostgreSQL/archive

chmod 700 /u02/PostgreSQL/archive

chown -R postgres:postgres /u02/PostgreSQL/archive

8) Now edit the pg_hba.conf file.

vim pg_hba.conf

Paste configuration below to the end of the line.

# Localhost

 host    replication     replicator         127.0.0.1/32               md5

# PostgreSQL Master IP address

host    replication     replicator         192.168.0.108/32        md5

# PostgreSQL SLave IP address

 host    replication     replicator         192.168.0.107/32       md5

1) Note: Before we start to configure the slave server, stop the postgres service

2) Then go to the postgres directory, and backup data directory.

cd /u02/PostgreSQL/10

mv data data-backup

3) Create new data directory and change the ownership permissions of the directory to the postgres user.

mkdir -p data/

chmod 700 data/

chown -R postgres:postgres data/

4) Next, login as the postgres user and copy all data directory from the 'Master' server to the 'Slave' server as replica user.

pg_basebackup -h 192.168.0.106 -U replicator -p 5432 -D /u02/PostgreSQL/10/data -P -Xs -R

Please replace the IP address with your master’s IP address.

In the above command, you see an optional argument -R. When you pass -R, it automatically creates a recovery.conf  file that contains the role of the DB instance and the details of its master. 

It is mandatory to create the recovery.conf file on the slave in order to set up a streaming replication. 

If you are not using the backup type mentioned above, and choose to take a tar format backup on master that can be copied to slave, you must create this recovery.conf file manually. Here are the contents of the recovery.conf file:

Type your password and wait for data transfer from the master to the slave server.

32512/32512 kB (100%), 1/1 tablespace

5) After the transfer is complete, go to the postgres data directory and edit postgresql.conf file on the slave server.

Check and Change standby config file like below:

hot_standby = on

hot_standby_feedback=on

6) Then check and edit recovery.conf file and add lines below:

7) In the above file, the role of the server is defined by standby_mode. standby_mode  must be set to ON for slaves in postgres.

And to stream WAL data, details of the master server are configured using the parameter primary_conninfo .

The two parameters standby_mode  and primary_conninfo are automatically created when you use the optional argument -R while taking a pg_basebackup. This recovery.conf file must exist in the data directory($PGDATA) of Slave.

8) Now start the database server using pg_ctl command.

pg_ctl -D /u02/PostgreSQL/10/data start

Final Step 6- Validate that postgresql replication is setup

As discussed earlier, a wal sender  and a wal receiver  process are started on the master and the slave after setting up replication. Check for these processes on both master and slave using the following commands.

On Master

$ ps -eaf | grep sender

On Slave

$ ps -eaf | grep receiver

$ ps -eaf | grep startup

You can see more details by querying the master’s pg_stat_replication view.

Primary - Master :-

postgres=# select pg_is_in_recovery();

 pg_is_in_recovery

-------------------

 f

Standby - Slave :-

postgres=# select pg_is_in_recovery();

 pg_is_in_recovery

-------------------

 t