Обсуждение: 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