Thread: How to disconnect a single user in Postgresql 8.2.5 from a database
How to disconnect a single user in Postgresql 8.2.5 from a database ? |
On 19 April 2010 15:27, Steph L <engel306sl@yahoo.fr> wrote: > > How to disconnect a single user in Postgresql 8.2.5 from a database ? AFAIK the only way is to upgrade to 8.4 and use pg_terminate_backend(): http://www.postgresql.org/docs/8.4/interactive/functions-admin.html -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
On Mon, Apr 19, 2010 at 11:52 AM, Sergey Konoplev <gray.ru@gmail.com> wrote: > On 19 April 2010 15:27, Steph L <engel306sl@yahoo.fr> wrote: >> How to disconnect a single user in Postgresql 8.2.5 from a database ? > AFAIK the only way is to upgrade to 8.4 and use pg_terminate_backend(): > http://www.postgresql.org/docs/8.4/interactive/functions-admin.html pg_terminate_backend() is just a SQL wrapper around: kill -SIGTERM [backend PID] For versions before 8.4: if you can SSH in to the server, run the above on the PID of the backend your user is connected to, and that should terminate their connection. Josh
Josh Kupershmidt <schmiddy@gmail.com> writes: > pg_terminate_backend() is just a SQL wrapper around: > kill -SIGTERM [backend PID] > For versions before 8.4: if you can SSH in to the server, run the > above on the PID of the backend your user is connected to, and that > should terminate their connection. The reason the function isn't there before 8.4 is that that's not promised to work before 8.4 ... most of the time it will work, but once in awhile you could get nasty side-effects. regards, tom lane
On Mon, Apr 19, 2010 at 8:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Kupershmidt <schmiddy@gmail.com> writes: >> pg_terminate_backend() is just a SQL wrapper around: >> kill -SIGTERM [backend PID] > >> For versions before 8.4: if you can SSH in to the server, run the >> above on the PID of the backend your user is connected to, and that >> should terminate their connection. > > The reason the function isn't there before 8.4 is that that's not > promised to work before 8.4 ... most of the time it will work, but > once in awhile you could get nasty side-effects. So, pg_cancel_backend(pid) is the 8.3 version, that may or may not always work then, right?
Scott Marlowe <scott.marlowe@gmail.com> writes: > On Mon, Apr 19, 2010 at 8:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> The reason the function isn't there before 8.4 is that that's not >> promised to work before 8.4 ... most of the time it will work, but >> once in awhile you could get nasty side-effects. > So, pg_cancel_backend(pid) is the 8.3 version, that may or may not > always work then, right? No, I didn't say that. regards, tom lane
pg_cancel_backend() and pg_terminate_backend() do different things. From the documentation: pg_cancel_backend(pid int) Cancel a backend's current query pg_terminate_backend(pid int) Terminate a backend On 4/19/2010 11:07 PM, Scott Marlowe wrote: > On Mon, Apr 19, 2010 at 8:10 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> Josh Kupershmidt<schmiddy@gmail.com> writes: >>> pg_terminate_backend() is just a SQL wrapper around: >>> kill -SIGTERM [backend PID] >> >>> For versions before 8.4: if you can SSH in to the server, run the >>> above on the PID of the backend your user is connected to, and that >>> should terminate their connection. >> >> The reason the function isn't there before 8.4 is that that's not >> promised to work before 8.4 ... most of the time it will work, but >> once in awhile you could get nasty side-effects. > > So, pg_cancel_backend(pid) is the 8.3 version, that may or may not > always work then, right? >
On Tue, Apr 20, 2010 at 7:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> On Mon, Apr 19, 2010 at 8:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> The reason the function isn't there before 8.4 is that that's not >>> promised to work before 8.4 ... most of the time it will work, but >>> once in awhile you could get nasty side-effects. > >> So, pg_cancel_backend(pid) is the 8.3 version, that may or may not >> always work then, right? > > No, I didn't say that. Well, my experience with it has been that it's similar and it doesn't always work.
Ahhh, ok. On Tue, Apr 20, 2010 at 7:52 AM, Lewis Kapell <lkapell@setonhome.org> wrote: > pg_cancel_backend() and pg_terminate_backend() do different things. From the > documentation: > > pg_cancel_backend(pid int) Cancel a backend's current query > pg_terminate_backend(pid int) Terminate a backend > > > > On 4/19/2010 11:07 PM, Scott Marlowe wrote: >> >> On Mon, Apr 19, 2010 at 8:10 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> >>> Josh Kupershmidt<schmiddy@gmail.com> writes: >>>> >>>> pg_terminate_backend() is just a SQL wrapper around: >>>> kill -SIGTERM [backend PID] >>> >>>> For versions before 8.4: if you can SSH in to the server, run the >>>> above on the PID of the backend your user is connected to, and that >>>> should terminate their connection. >>> >>> The reason the function isn't there before 8.4 is that that's not >>> promised to work before 8.4 ... most of the time it will work, but >>> once in awhile you could get nasty side-effects. >> >> So, pg_cancel_backend(pid) is the 8.3 version, that may or may not >> always work then, right? >> > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- When fascism comes to America, it will be intolerance sold as diversity.
Scott Marlowe <scott.marlowe@gmail.com> writes: > On Tue, Apr 20, 2010 at 7:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Scott Marlowe <scott.marlowe@gmail.com> writes: >>> So, pg_cancel_backend(pid) is the 8.3 version, that may or may not >>> always work then, right? >> >> No, I didn't say that. > Well, my experience with it has been that it's similar and it doesn't > always work. pg_cancel_backend might or might not always succeed in canceling a query, if that's what you mean. What I'm worried about with SIGTERM'ing an individual backend is negative side-effects, like hanging the entire database. Before 8.4 there was never any attempt to ensure that that would work nicely, and if memory serves we did find some problems when we tested it. regards, tom lane