I have a PostgreSQL 12.1 database system (I'll refer to it as PGSQL) running on a remotely hosted VM server (Windows Server 2019). We upgraded the server OS and PGSQL probably about a month-and-a-half ago. Everything has been running more-or-less normally since then, until this morning when I started receiving the above-mentioned database error in pretty much every one of our in-house applications that connect to this PGSQL instance.

To check the connections, I ran SELECT * FROM pg_stat_activity;, which returned 103 rows. My postgresql.conf file has max_connections = 100, so that makes sense, but what doesn't make sense is that, of those 103 connections, 90+ of them are listed as idle with query text of DISCARD ALL. All of these show as being executed by the same, non-superuser account from the server's own internal address. However, several of the connections show a query_start date value from a month or more ago.

Now, many of the applications we have in place are unfortunately built with hard-coded credentials (I have a lot of "clean-up" work to do on the code for these applications that I inherited) and are generally being executed from shortcuts pointing to an "Application" share on the server that's hosting the PGSQL database, so none of this looks particularly "suspicious". I tried to simply kill the processes using SELECT pg_cancel_backend(<pid>); on one of the pid values from the previous query, but requerying pg_stat_activity still shows the same record in the result set (all of the values appear to be exactly the same, from what I can tell).

Perhaps I'm not using the correct function to terminate these "hung" processes or something, but I could not figure out how to clear out these connections individually. Because I needed to get our production environment back to a usable state, I ended up just stopping and restarting the PGSQL service on the server which did clear out all of those old DISCARD ALL statements, but I'm curious if there's something I could do to prevent this backlog of "hung" statements in the future.

My question here is, how can I prevent this from happening in the future? One thing to note is that, prior to upgrading our PGSQL server to v12.1, we ran v9.4 for a number of years and never once encountered this issue. I'm wondering if there might be something inherent to the newer version of PGSQL, or perhaps even something about running PGSQL in the Windows Server 2019 environment that might be causing this behavior.