Thread: pg_terminate_backend not working

pg_terminate_backend not working

From
bhargav kamineni
Date:
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  


Re: pg_terminate_backend not working

From
bhargav kamineni
Date:
Any thoughts on this ?

On Tue, 24 Sep 2019 at 16:44, bhargav kamineni <bhargavpostgres@gmail.com> wrote:
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  


Re: pg_terminate_backend not working

From
Jerry Sievers
Date:
bhargav kamineni <bhargavpostgres@gmail.com> writes:

> 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.

Your client backend is most likely sitting in a blocked sys call such as
network send, etc.

Not sure though what is relaunching them after you say they were
disabled some{where,how}.

HTH

>
> 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  
>
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net



Re: pg_terminate_backend not working

From
bhargav kamineni
Date:
> 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.

>Your client backend is most likely sitting in a blocked sys call such as
network send, etc.

these queries are not doing network calls,they have loopback dblink connection with in.
>Not sure though what is relaunching them after you say they were
disabled some{where,how}.

Is there any  workaround  to  terminate those stuck process apart from restarting the database ?

On Wed, 25 Sep 2019 at 00:05, Jerry Sievers <gsievers19@comcast.net> wrote:
bhargav kamineni <bhargavpostgres@gmail.com> writes:

> 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.

Your client backend is most likely sitting in a blocked sys call such as
network send, etc.

Not sure though what is relaunching them after you say they were
disabled some{where,how}.

HTH

>
> 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  
>
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net