Thread: SIGKILL and force disconnecting user sessions

This one really isn't in the docs, so we'll add it to the Wiki
afterwords, but:

We've got some shared PG instances with more than one production
application (yes, I know this is a bad practice, but virtualization
options are hit and miss).

We need to kill apache P-conn pools from a an application/web server
cluster for a specific database.  We use a load-balancer, so we shut
down the HA L4 Service VIP that the web servers pconnect() to, to
prevent new incoming connections.

The problem is then net.inet.tcp.* timeouts.  We need to cane existing
connections with a quickness, and pg_stat_activity is a read-only view.

We need to kill the stale pcons, so just:
 $ pkill -a [database string]

 The connection goes away as "Disconnected per admin command"

 I just want to confirm:

 1) There are no data consistency threats to SIGKIL a child thread
    process?  Transactions and locks will be handled gracefully?

 2) There are no pgsql/admin level CLI builtin commands to do this in a
    more graceful manor?

On Mon, Sep 22, 2008 at 09:43:29AM -0400, Brian A. Seklecki wrote:

BTW: What version of PG are you using?

> We need to kill apache P-conn pools from a an application/web server
> cluster for a specific database.  We use a load-balancer, so we shut
> down the HA L4 Service VIP that the web servers pconnect() to, to
> prevent new incoming connections.
>
> The problem is then net.inet.tcp.* timeouts.  We need to cane existing
> connections with a quickness, and pg_stat_activity is a read-only view.
>
> We need to kill the stale pcons, so just:
>  $ pkill -a [database string]
>
>  The connection goes away as "Disconnected per admin command"
>
>  I just want to confirm:
>
>  1) There are no data consistency threats to SIGKIL a child thread
>     process?  Transactions and locks will be handled gracefully?

As far, as you do not really use SIGKILL (9), but SIGTERM (15),
everything should be fine.

>  2) There are no pgsql/admin level CLI builtin commands to do this in a
>     more graceful manor?

You might want to try pg_cancel_backend(pid) to cancel backends,
but I suppose, the connection will stay alive.
- see http://www.postgresql.org/docs/8.2/static/functions-admin.html

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

On Mon, 2008-09-22 at 17:06 +0200, Tino Schwarze wrote:
>
> As far, as you do not really use SIGKILL (9), but SIGTERM (15),
> everything should be fine.

Yea my bad -- SIGTERM not SIGKILL.

Thanks for confirming.

A nice little SQL macro would be awesome: DISCONNECT or TERMINATE

But handling it via the POSIX layer is much more pragmatic.

~BAS