Обсуждение: Is there a way to kill a connection from the pg_stat_activitly list?
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.
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.
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/
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
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:
Need a vacation? Get great deals to amazing places on Yahoo! Travel.
"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.
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. From the command line on the server you can issue a kill <pid> to do that. From within pgsql you'd need to write a function in an untrusted language to pull it off.
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/
>>> 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
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:
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.
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.
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/
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:
Don't let your dream ride pass you by. Make it a reality with Yahoo! Autos.
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.
From the command line on the server you can issue a kill to do
that. From within pgsql you'd need to write a function in an
untrusted language to pull it off.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Don't let your dream ride pass you by. Make it a reality with Yahoo! Autos.
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/
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/
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
--
Hobby Site : http://dailyhealthtips.blogspot.com
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