pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is there a way to kill a connection from the pg_stat_activitly list?
13+ messages / 6 participants
[nested] [flat]

* Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 12:56  Jessica Richard <rjessil@yahoo.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Jessica Richard @ 2007-10-15 12:56 UTC (permalink / raw)
  To: pgsql-admin

When you see a hanging Postgres connection (or a job running so long and you don't want to continue any more) from 

select * from pg_stat_activity

and you want to disconnect it,

how do you do it?

thanks,
Jessica

       
---------------------------------
 Check out  the hottest 2008 models today at Yahoo! Autos.

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 13:23  Tommy Gildseth <tommy.gildseth@usit.uio.no>
  parent: Jessica Richard <rjessil@yahoo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Tommy Gildseth @ 2007-10-15 13:23 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin

Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long 
> and you don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?

|pg_cancel_backend()


|http://www.postgresql.org/docs/8.1/interactive/functions-admin.html

-- 
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 13:50  Jonah H. Harris <jonah.harris@gmail.com>
  parent: Jessica Richard <rjessil@yahoo.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Jonah H. Harris @ 2007-10-15 13:50 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin

On 10/15/07, Jessica Richard <rjessil@yahoo.com> wrote:
> When you see a hanging Postgres connection (or a job running so long and you
> don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?

See pg_cancel_backend

You should also look at using statement_timeout if this is a regular occurrence.


-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 17:18  Jessica Richard <rjessil@yahoo.com>
  parent: Tommy Gildseth <tommy.gildseth@usit.uio.no>
  0 siblings, 2 replies; 13+ messages in thread

From: Jessica Richard @ 2007-10-15 17:18 UTC (permalink / raw)
  To: Tommy Gildseth <tommy.gildseth@usit.uio.no>; +Cc: pgsql-admin

Thanks a lot!

"select pg_cancel_backend(procpid) " can end the current query for that user, but then this connection becomes IDLE, still connected.

Is there a command for me to totally disconnect a user by procpid? Some times, I need to kick out a particular Postgres user completely.

thanks


Tommy Gildseth <tommy.gildseth@usit.uio.no> wrote: Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long 
> and you don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?

|pg_cancel_backend()


|http://www.postgresql.org/docs/8.1/interactive/functions-admin.html

-- 
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


       
---------------------------------
Need a vacation? Get great deals to amazing places on Yahoo! Travel. 

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 17:34  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Jessica Richard <rjessil@yahoo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Scott Marlowe @ 2007-10-15 17:34 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

On 10/15/07, Jessica Richard <rjessil@yahoo.com> wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 17:34  Jonah H. Harris <jonah.harris@gmail.com>
  parent: Jessica Richard <rjessil@yahoo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Jonah H. Harris @ 2007-10-15 17:34 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

On 10/15/07, Jessica Richard <rjessil@yahoo.com> wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.

There used to be a pg_terminate_backend, but it was #ifdef'd out due
to corruption concerns.  Basically, all it did was:

kill -TERM pid

I'm not sure whether anyone has completed the research required to
know if anything remains corrupted, but it is used occasionally.  Best
to do pg_cancel_backend and then kill -TERM.


-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-15 18:13  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: Jonah H. Harris <jonah.harris@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Kevin Grittner @ 2007-10-15 18:13 UTC (permalink / raw)
  To: Jonah H. Harris <jonah.harris@gmail.com>; Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin; Tommy Gildseth <tommy.gildseth@usit.uio.no>

>>> On Mon, Oct 15, 2007 at 12:34 PM, in message
<36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.
Harris" <jonah.harris@gmail.com> wrote: 
> 
> There used to be a pg_terminate_backend, but it was #ifdef'd out due
> to corruption concerns.  Basically, all it did was:
> 
> kill -TERM pid
> 
> I'm not sure whether anyone has completed the research required to
> know if anything remains corrupted, but it is used occasionally.  Best
> to do pg_cancel_backend and then kill -TERM.
 
Where does pg_ctl kill fit in?
 
Is TERM the normal signal to use there, too?
 
Should the pg_ctl docs give some guidelines on the signals?
 
-Kevin
 





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-16 11:56  Jessica Richard <rjessil@yahoo.com>
  parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
  0 siblings, 1 reply; 13+ messages in thread

From: Jessica Richard @ 2007-10-16 11:56 UTC (permalink / raw)
  To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-admin@postgresql.org, Tommy Gildseth <tommy.gildseth@usit.uio.no>

If the connection is from the local machine, I can find it with "ps -ef | grep procpid", then kill it with Unix command "kill" outside Postgres...

But I have many remote connections coming from different machines...it is hard to kill on the OS level outside Postgres on the postgres host...

I am looking for something to kill a Postgres user connection within Postgres...
Some thing like, you find the user connection with select * from pg_stat_activity...then you pick a procpid and kill right there...

Thanks,


Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >>> On Mon, Oct 15, 2007 at 12:34 PM, in message
<36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.
Harris"  wrote: 
> 
> There used to be a pg_terminate_backend, but it was #ifdef'd out due
> to corruption concerns.  Basically, all it did was:
> 
> kill -TERM pid
> 
> I'm not sure whether anyone has completed the research required to
> know if anything remains corrupted, but it is used occasionally.  Best
> to do pg_cancel_backend and then kill -TERM.
 
Where does pg_ctl kill fit in?
 
Is TERM the normal signal to use there, too?
 
Should the pg_ctl docs give some guidelines on the signals?
 
-Kevin
 



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


       
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-16 12:00  Jonah H. Harris <jonah.harris@gmail.com>
  parent: Jessica Richard <rjessil@yahoo.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Jonah H. Harris @ 2007-10-16 12:00 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin@postgresql.org, "Tommy Gildseth" <tommy.gildseth@usit.uio.no>

On 10/16/07, Jessica Richard <rjessil@yahoo.com> wrote:
> If the connection is from the local machine, I can find it with "ps -ef |
> grep procpid", then kill it with Unix command "kill" outside Postgres...
>
> But I have many remote connections coming from different machines...it is
> hard to kill on the OS level outside Postgres on the postgres host...
>
> I am looking for something to kill a Postgres user connection within
> Postgres...
> Some thing like, you find the user connection with select * from
> pg_stat_activity...then you pick a procpid and kill right there...

Write a C stored procedure that takes a pid and calls kill(2), install
it on the server, and call it from SQL.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-16 12:03  Jessica Richard <rjessil@yahoo.com>
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Jessica Richard @ 2007-10-16 12:03 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

select pg_cancel_backend(procpid) solved half of my problem...at least it terminated the query for that user... but it is still holding a user connection in IDLE state....If I have too many of those, Postgres may run of out of user connections....

I already knew how to kill a connection if the connection is from the local host. But I have many remote connections coming from different machines... hard to kill with unix command "kill"...  One time, I was testing to kill a particular connection on a testing machine, the entrie Postgres was brought down....

I need to find a safer, cleaner way to disconnect a user from Postgres when needed.

Thanks a lot,


Scott Marlowe <scott.marlowe@gmail.com> wrote: On 10/15/07, Jessica Richard  wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-16 13:05  Jonah H. Harris <jonah.harris@gmail.com>
  parent: Jessica Richard <rjessil@yahoo.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Jonah H. Harris @ 2007-10-16 13:05 UTC (permalink / raw)
  To: Jessica Richard <rjessil@yahoo.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

On 10/16/07, Jessica Richard <rjessil@yahoo.com> wrote:
> select pg_cancel_backend(procpid) solved half of my problem...at least it
> terminated the query for that user... but it is still holding a user
> connection in IDLE state....If I have too many of those, Postgres may run of
> out of user connections....
>
> I already knew how to kill a connection if the connection is from the local
> host. But I have many remote connections coming from different machines...
> hard to kill with unix command "kill"...  One time, I was testing to kill a
> particular connection on a testing machine, the entrie Postgres was brought
> down....

That's why I said to write a C stored procedure to do it and install
it on the server.  That way you could call it the same way as
pg_cancel_backend.

> I need to find a safer, cleaner way to disconnect a user from Postgres when
> needed.

At this point in time, there isn't one.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-17 11:16  Moiz Kothari <moizpostgres@gmail.com>
  parent: Jonah H. Harris <jonah.harris@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Moiz Kothari @ 2007-10-17 11:16 UTC (permalink / raw)
  To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; Scott Marlowe <scott.marlowe@gmail.com>; Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

Hi,

Please do not use kill, it sometimes resets all the connection on your db,
you might want to use

pkill -f <string in ps -ef> to kill the connection you want to.

Regards,
Moiz Kothari

On 10/16/07, Jonah H. Harris <jonah.harris@gmail.com> wrote:
>
> On 10/16/07, Jessica Richard <rjessil@yahoo.com> wrote:
> > select pg_cancel_backend(procpid) solved half of my problem...at least
> it
> > terminated the query for that user... but it is still holding a user
> > connection in IDLE state....If I have too many of those, Postgres may
> run of
> > out of user connections....
> >
> > I already knew how to kill a connection if the connection is from the
> local
> > host. But I have many remote connections coming from different
> machines...
> > hard to kill with unix command "kill"...  One time, I was testing to
> kill a
> > particular connection on a testing machine, the entrie Postgres was
> brought
> > down....
>
> That's why I said to write a C stored procedure to do it and install
> it on the server.  That way you could call it the same way as
> pg_cancel_backend.
>
> > I need to find a safer, cleaner way to disconnect a user from Postgres
> when
> > needed.
>
> At this point in time, there isn't one.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation                | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
> Edison, NJ 08837                        | http://www.enterprisedb.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>



-- 
Hobby Site : http://dailyhealthtips.blogspot.com

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Is there a way to kill a connection from the pg_stat_activitly list?
@ 2007-10-17 12:00  Jonah H. Harris <jonah.harris@gmail.com>
  parent: Moiz Kothari <moizpostgres@gmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Jonah H. Harris @ 2007-10-17 12:00 UTC (permalink / raw)
  To: Moiz Kothari <moizpostgres@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; Scott Marlowe <scott.marlowe@gmail.com>; Tommy Gildseth <tommy.gildseth@usit.uio.no>; pgsql-admin

On 10/17/07, Moiz Kothari <moizpostgres@gmail.com> wrote:
> Please do not use kill

More like, please do not take advice from people who don't know what
they're talking about.

> it sometimes resets all the connection on your db,
> you might want to use

Honestly, was this supposed to be a joke?  Have you RTFM?  Have you
looked at the code to pkill?  pkill calls kill and frankly, pkill is
more dangerous if you only want to kill a single session.

Grr, I'm surprised to see such an uninformed statement on this list.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/



^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2007-10-17 12:00 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-15 12:56 Is there a way to kill a connection from the pg_stat_activitly list? Jessica Richard <rjessil@yahoo.com>
2007-10-15 13:23 ` Tommy Gildseth <tommy.gildseth@usit.uio.no>
2007-10-15 17:18   ` Jessica Richard <rjessil@yahoo.com>
2007-10-15 17:34     ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-16 12:03       ` Jessica Richard <rjessil@yahoo.com>
2007-10-16 13:05         ` Jonah H. Harris <jonah.harris@gmail.com>
2007-10-17 11:16           ` Moiz Kothari <moizpostgres@gmail.com>
2007-10-17 12:00             ` Jonah H. Harris <jonah.harris@gmail.com>
2007-10-15 17:34     ` Jonah H. Harris <jonah.harris@gmail.com>
2007-10-15 18:13       ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-10-16 11:56         ` Jessica Richard <rjessil@yahoo.com>
2007-10-16 12:00           ` Jonah H. Harris <jonah.harris@gmail.com>
2007-10-15 13:50 ` Jonah H. Harris <jonah.harris@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox