Return to Question

As you can see, this should look for any idle connections older than 2 weeks and terminate them. Since this application is run daily, I believe this will help to keep the pool as clear as possible. Of course, if there's a better way - one that does not require the implementation of some new 3rd-party solution (at least, at this time) - I'm all ears, but I believe this will "solve" my problem for the time being.

As you can see, this should look for any idle connections older than 2 weeks and terminate them. Since this application is run daily, I believe this will help to keep the pool as clear as possible. Of course, if there's a better way - one that does not require the implementation of some new 3rd-party solution (at least, at this time) - I'm all ears, but, for the time being, I believe this will "solve" my problem.

ADDITIONAL REFERENCE / POSSIBLE ALTERNATE SOLUTION

As I was looking around a bit more, I also came across this answer on StackOverflow:

How to close idle connections in PostgreSQL automatically?

The linked answer refers to using the idle_in_transaction_session_timeout setting introduced in PostgreSQL 9.6. As stated in fresko's answer, I should be able to set this with a single SQL command sent to the server either as a superuser for the entirety of the server:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

or for individual user connections on a per-session basis (if needed)

SET SESSION idle_in_transaction_session_timeout = '5min';

I checked this setting on my server and it appears to be disabled (0), so I may look into configuring this setting as well. I'll be referring to the v12 documentation page for Client Connection Defaults in case anyone wants to "follow along".

UPDATE/EDIT

Since posting this question 4 months ago, I, unfortunately, haven't been actively checking the status of the idle connections. This morning, however, the error popped back up and I was reminded of this post. Again, executing SELECT * FROM pg_stat_activity; showed over 100 connections (the max defined by my server configuration), of which over 70 were idle / DISCARD ALL with "older" (several weeks/months) values for the backend_start / query_start date.

Understanding that the database itself doesn't "manage" these, I still find it odd that I never encountered this error in more than a decade of running previous versions of PostgreSQL. It wasn't until after we upgraded to v12, even though the environment has remained basically the same.

Regardless, because I have so many "legacy" applications that potentially could be leaving these connections open, I've decided to implement a check in one of my daily routines that should prevent this issue from reoccurring. I have an application that is run every day to replicate and clean up certain information in the database. In that application, I am adding a small function that will execute the following SQL command to keep the backend clear of these "leftover" idle connections:

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    pid <> pg_backend_pid()
    AND backend_start < (SELECT (NOW() - INTERVAL '2 WEEK')::DATE)
    AND state = 'idle';

As you can see, this should look for any idle connections older than 2 weeks and terminate them. Since this application is run daily, I believe this will help to keep the pool as clear as possible. Of course, if there's a better way - one that does not require the implementation of some new 3rd-party solution (at least, at this time) - I'm all ears, but I believe this will "solve" my problem for the time being.

##EDIT

EDIT