pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Forcing connections closed
9+ messages / 7 participants
[nested] [flat]

* Forcing connections closed
@ 2004-01-29 21:03 Hadley Willan <hadley.willan@deeperdesign.co.nz>
  0 siblings, 0 replies; 9+ messages in thread

From: Hadley Willan @ 2004-01-29 21:03 UTC (permalink / raw)
  To: pgsql-admin

Hello,
    How do I force postmaster to terminate active connections on other
databases?

Thanks
-- 
Hadley Willan » Director » hadley.willan@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com

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

* Re: Forcing connections closed
@ 2004-01-29 21:21 Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
  0 siblings, 1 reply; 9+ messages in thread

From: Gregory S. Williamson @ 2004-01-29 21:21 UTC (permalink / raw)
  To: Hadley Willan <hadley.willan@deeperdesign.co.nz>; pgsql-admin


pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]

e.g. 

pg_ctl stop -D /data/postgres/gex_runtime -m fast

will shut down all connections and stop the postgres instance:
Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown
  immediate   quit without complete shutdown; will lead to recovery on restart

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:	Hadley Willan [mailto:hadley.willan@deeperdesign.co.nz]
Sent:	Thu 1/29/2004 1:03 PM
To:	PGSQL Admin
Cc:	
Subject:	[ADMIN] Forcing connections closed

Hello,
    How do I force postmaster to terminate active connections on other
databases?

Thanks
-- 
Hadley Willan » Director » hadley.willan@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com






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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
@ 2004-01-29 21:25 ` Hadley Willan <hadley.willan@deeperdesign.co.nz>
  2004-01-29 21:48   ` Re: Forcing connections closed Ryan Chambers <ryan@squaretrade.com>
  2004-01-29 22:36   ` Re: Forcing connections closed Juan Miguel <juanmime@ono.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Hadley Willan @ 2004-01-29 21:25 UTC (permalink / raw)
  To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-admin

Thanks for that, however that's quite heavy handed in that it will stop
the postgres instance.

Is there any way to close connections to a database without stopping
postgres itself? 

E.G I have three databases, A,B and C, and only want to close C.

Thanks

On Fri, 2004-01-30 at 10:21, Gregory S. Williamson wrote:

> pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
> 
> e.g. 
> 
> pg_ctl stop -D /data/postgres/gex_runtime -m fast
> 
> will shut down all connections and stop the postgres instance:
> Shutdown modes are:
>   smart       quit after all clients have disconnected
>   fast        quit directly, with proper shutdown
>   immediate   quit without complete shutdown; will lead to recovery on restart
> 
> HTH,
> 
> Greg Williamson
> DBA
> GlobeXplorer LLC
> 
> -----Original Message-----
> From:	Hadley Willan [mailto:hadley.willan@deeperdesign.co.nz]
> Sent:	Thu 1/29/2004 1:03 PM
> To:	PGSQL Admin
> Cc:	
> Subject:	[ADMIN] Forcing connections closed
> 
> Hello,
>     How do I force postmaster to terminate active connections on other
> databases?
> 
> Thanks

-- 
Hadley Willan » Director » hadley.willan@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com

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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
@ 2004-01-29 21:48   ` Ryan Chambers <ryan@squaretrade.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Ryan Chambers @ 2004-01-29 21:48 UTC (permalink / raw)
  To: pgsql-admin

It's possible to get process ids for clients using something like ps -ef 
| grep postgres. I wonder, is it safe to use kill to terminate those 
threads? If this doesn't mess up the database, it might work.

Hadley Willan wrote:

> Thanks for that, however that's quite heavy handed in that it will 
> stop the postgres instance.
>
> Is there any way to close connections to a database without stopping 
> postgres itself?
>
> E.G I have three databases, A,B and C, and only want to close C.
>
> Thanks
>
> On Fri, 2004-01-30 at 10:21, Gregory S. Williamson wrote:
>
>>/pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
>>
>>e.g. 
>>
>>pg_ctl stop -D /data/postgres/gex_runtime -m fast
>>
>>will shut down all connections and stop the postgres instance:
>>Shutdown modes are:
>>  smart       quit after all clients have disconnected
>>  fast        quit directly, with proper shutdown
>>  immediate   quit without complete shutdown; will lead to recovery on restart
>>
>>HTH,
>>
>>Greg Williamson
>>DBA
>>GlobeXplorer LLC
>>
>>-----Original Message-----
>>From:	Hadley Willan [mailto:hadley.willan@deeperdesign.co.nz]
>>Sent:	Thu 1/29/2004 1:03 PM
>>To:	PGSQL Admin
>>Cc:	
>>Subject:	[ADMIN] Forcing connections closed
>>
>>Hello,
>>    How do I force postmaster to terminate active connections on other
>>databases?
>>
>>Thanks/
>>
> -- 
> *Hadley Willan* *»* Director *»* _hadley.willan@deeperdesign.com_ 
> <mailto:hadley.willan@deeperdesign.com> *»* +64(21) 28 41 463 Deeper 
> Design Limited *»* +64(7) 377 3328 *»* _www.deeperdesign.com_ 
> <http://www.deeperdesign.com;
>
>
>  
>

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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
@ 2004-01-29 22:36   ` Juan Miguel <juanmime@ono.com>
  2004-01-29 23:52     ` Re: Forcing connections closed scott.marlowe <scott.marlowe@ihs.com>
  2004-01-30 02:19     ` Re: Forcing connections closed Andrew Rawnsley <ronz@ravensfield.com>
  1 sibling, 2 replies; 9+ messages in thread

From: Juan Miguel @ 2004-01-29 22:36 UTC (permalink / raw)
  To: Hadley Willan <hadley.willan@deeperdesign.co.nz>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-admin

You can kill the procs (connections) using kill -9 PID

For example,

ps aux | grep post

gives you a list of the postgres processes (all the active connections, 
and main postmaster). Here you can see the IP of the manchines connected 
from.
Therefore, its easy to create an shell script for killing the connection 
of a client machine.

>Thanks for that, however that's quite heavy handed in that it will stop
>the postgres instance.
>
>Is there any way to close connections to a database without stopping
>postgres itself? 
>
>E.G I have three databases, A,B and C, and only want to close C.
>
>Thanks
>
>On Fri, 2004-01-30 at 10:21, Gregory S. Williamson wrote:
>
>  
>
>>pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
>>
>>e.g. 
>>
>>pg_ctl stop -D /data/postgres/gex_runtime -m fast
>>
>>will shut down all connections and stop the postgres instance:
>>Shutdown modes are:
>>  smart       quit after all clients have disconnected
>>  fast        quit directly, with proper shutdown
>>  immediate   quit without complete shutdown; will lead to recovery on restart
>>
>>HTH,
>>
>>Greg Williamson
>>DBA
>>GlobeXplorer LLC
>>
>>-----Original Message-----
>>From:	Hadley Willan [mailto:hadley.willan@deeperdesign.co.nz]
>>Sent:	Thu 1/29/2004 1:03 PM
>>To:	PGSQL Admin
>>Cc:	
>>Subject:	[ADMIN] Forcing connections closed
>>
>>Hello,
>>    How do I force postmaster to terminate active connections on other
>>databases?
>>
>>Thanks
>>    
>>
>
>  
>





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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
  2004-01-29 22:36   ` Re: Forcing connections closed Juan Miguel <juanmime@ono.com>
@ 2004-01-29 23:52     ` scott.marlowe <scott.marlowe@ihs.com>
  2004-02-01 21:47       ` Re: Forcing connections closed Juan Miguel <juanmime@ono.com>
  1 sibling, 1 reply; 9+ messages in thread

From: scott.marlowe @ 2004-01-29 23:52 UTC (permalink / raw)
  To: Juan Miguel <juanmime@ono.com>; +Cc: Hadley Willan <hadley.willan@deeperdesign.co.nz>; Gregory S. Williamson <gsw@globexplorer.com>; pgsql-admin

On Thu, 29 Jan 2004, Juan Miguel wrote:

> You can kill the procs (connections) using kill -9 PID

Just use kill PID first!

'kill -9' PID will force all backends to flush cache for no good reason.

Note that on most unixes, a plain kill PID will send the term signal, 
which tells the process to poltely shutdown and release resources.  kill 
-9 is like using a sledge hammer to swat a fly for this.




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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
  2004-01-29 22:36   ` Re: Forcing connections closed Juan Miguel <juanmime@ono.com>
  2004-01-29 23:52     ` Re: Forcing connections closed scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-01 21:47       ` Juan Miguel <juanmime@ono.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Juan Miguel @ 2004-02-01 21:47 UTC (permalink / raw)
  To: pgsql-admin


>'kill -9' PID will force all backends to flush cache for no good reason.
>
>Note that on most unixes, a plain kill PID will send the term signal, 
>which tells the process to poltely shutdown and release resources.  kill 
>-9 is like using a sledge hammer to swat a fly for this.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>  
>
;-) Yes, you are on the right. "kill -9", is the last try, but often you 
have to do "kill -9" for killing a procces, even sometimes, 'kill -9' 
does not work (often when the process is acceding to a hardware device - 
a CD for example -).





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

* Re: Forcing connections closed
  2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
  2004-01-29 21:25 ` Re: Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
  2004-01-29 22:36   ` Re: Forcing connections closed Juan Miguel <juanmime@ono.com>
@ 2004-01-30 02:19     ` Andrew Rawnsley <ronz@ravensfield.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Andrew Rawnsley @ 2004-01-30 02:19 UTC (permalink / raw)
  To: Juan Miguel <juanmime@ono.com>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; Hadley Willan <hadley.willan@deeperdesign.co.nz>; pgsql-admin


You can also get pid information from the system view pg_stat_activity, 
which also tells you who your killing and what database they're 
connected to.

On Jan 29, 2004, at 5:36 PM, Juan Miguel wrote:

> You can kill the procs (connections) using kill -9 PID
>
> For example,
>
> ps aux | grep post
>
> gives you a list of the postgres processes (all the active 
> connections, and main postmaster). Here you can see the IP of the 
> manchines connected from.
> Therefore, its easy to create an shell script for killing the 
> connection of a client machine.
>
>> Thanks for that, however that's quite heavy handed in that it will 
>> stop
>> the postgres instance.
>>
>> Is there any way to close connections to a database without stopping
>> postgres itself?
>> E.G I have three databases, A,B and C, and only want to close C.
>>
>> Thanks
>>
>> On Fri, 2004-01-30 at 10:21, Gregory S. Williamson wrote:
>>
>>
>>> pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
>>>
>>> e.g.
>>> pg_ctl stop -D /data/postgres/gex_runtime -m fast
>>>
>>> will shut down all connections and stop the postgres instance:
>>> Shutdown modes are:
>>>  smart       quit after all clients have disconnected
>>>  fast        quit directly, with proper shutdown
>>>  immediate   quit without complete shutdown; will lead to recovery 
>>> on restart
>>>
>>> HTH,
>>>
>>> Greg Williamson
>>> DBA
>>> GlobeXplorer LLC
>>>
>>> -----Original Message-----
>>> From:	Hadley Willan [mailto:hadley.willan@deeperdesign.co.nz]
>>> Sent:	Thu 1/29/2004 1:03 PM
>>> To:	PGSQL Admin
>>> Cc:	
>>> Subject:	[ADMIN] Forcing connections closed
>>>
>>> Hello,
>>>    How do I force postmaster to terminate active connections on other
>>> databases?
>>>
>>> Thanks
>>>
>>
>>
>
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if 
> your
>      joining column's datatypes do not match
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com




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

* Re: Forcing connections closed
@ 2004-02-02 12:53 Spiegelberg, Greg <gspiegelberg@cranel.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Spiegelberg, Greg @ 2004-02-02 12:53 UTC (permalink / raw)
  To: pgsql-admin

If the system in question is ...

1. a Linux or *BSD AND
2. has ipchains/iptables/whatever firewall software installed AND
3. the connections in question to be killed are coming in via
   port 5432/tcp

then I would think you could temporarily change the firewall configuration
on the system such that all traffic from the remote IP, or even localhost,
on 5432/tcp gets dropped and possibly the connection should drop as well.
If it doesn't then you may additionally tweak your tcp_keepalive_time to
0 before changing the firewall rule.  Just remember to drop that firewall
rule and set the tcp_keepalive_time back when you're done.

Just random thoughts.

Greg


-----Original Message-----
From: Juan Miguel
To: PGSQL Admin
Sent: 2/1/04 4:47 PM
Subject: Re: [ADMIN] Forcing connections closed


>'kill -9' PID will force all backends to flush cache for no good
reason.
>
>Note that on most unixes, a plain kill PID will send the term signal, 
>which tells the process to poltely shutdown and release resources.
kill 
>-9 is like using a sledge hammer to swat a fly for this.
>
>
>---------------------------(end of
broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>  
>
;-) Yes, you are on the right. "kill -9", is the last try, but often you

have to do "kill -9" for killing a procces, even sometimes, 'kill -9' 
does not work (often when the process is acceding to a hardware device -

a CD for example -).



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html



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


end of thread, other threads:[~2004-02-02 12:53 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-29 21:03 Forcing connections closed Hadley Willan <hadley.willan@deeperdesign.co.nz>
2004-01-29 21:21 Re: Forcing connections closed Gregory S. Williamson <gsw@globexplorer.com>
2004-01-29 21:25 ` Hadley Willan <hadley.willan@deeperdesign.co.nz>
2004-01-29 21:48   ` Ryan Chambers <ryan@squaretrade.com>
2004-01-29 22:36   ` Juan Miguel <juanmime@ono.com>
2004-01-29 23:52     ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-01 21:47       ` Juan Miguel <juanmime@ono.com>
2004-01-30 02:19     ` Andrew Rawnsley <ronz@ravensfield.com>
2004-02-02 12:53 Re: Forcing connections closed Spiegelberg, Greg <gspiegelberg@cranel.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