pg_terminate_backend not working

From: bhargav kamineni <bhargavpostgres(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: bhargav kamineni <bhargavpostgres(at)gmail(dot)com>
Subject: pg_terminate_backend not working
Date: 2019-09-24 11:14:01
Message-ID: CADCf-WMVkECbgr9kxC+VTdT_DE0LEJNP01BzBAWD1nT8fop+Ng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Team ,

We are observing long running process hung in active state on one of our db
from last two days with usename 'xyz', We have tried killing the respective
pid with pg_terminate_backned(pid), it's returning true but the quires are
not actually being terminated. Based on the client address and port number
from pg_stat_activity we found the connection origin and killed that
connection initiation process from that host , even after that the
connections on db from 'xyz' are not getting terminated.

and these connections are not making network calls a self dblink connection
is being established in the query.

as a workaround we have disabled the connections initiation script for xyz
user and restarted the database right after restart we observed the new
connections from xyz user to database which is not expected because as we
have already disables the connections initiation script.

select pg_postmaster_start_time();
pg_postmaster_start_time
------------------------------
2019-09-24 06:44:07.41594+00

select usename,backend_start from pg_stat_activity where now()-xact_start >
'3 hours' and usename ='xyz';
usename | backend_start

-----------------+----------------------------
xyz | 2019-09-24 06:44:38.879047+00 |
xyz | 2019-09-24 06:44:38.880501+00 |
xyz | 2019-09-24 06:44:38.881326+00 |
xyz | 2019-09-24 06:44:38.877561+00 |
xyz | 2019-09-24 06:44:38.878336+00 |

what could be the possible reason for these connections and
pg_termiante_backend(pid) not working ?

Thanks,
Bhargav K

Responses

Browse pgsql-general by date

  From Date Subject
Next Message PegoraroF10 2019-09-24 13:35:01 Operator is not unique
Previous Message Daulat Ram 2019-09-24 10:05:25 RE: Monitor Postgres database status on Docker