Thread: Forcing connections closed

Forcing connections closed

From
Hadley Willan
Date:
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

Re: Forcing connections closed

From
"Gregory S. Williamson"
Date:
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




Re: Forcing connections closed

From
Hadley Willan
Date:
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

Re: Forcing connections closed

From
Ryan Chambers
Date:
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 » +64(21) 28 41 463 Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com

Re: Forcing connections closed

From
Juan Miguel
Date:
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
>>
>>
>
>
>



Re: Forcing connections closed

From
"scott.marlowe"
Date:
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.


Re: Forcing connections closed

From
Andrew Rawnsley
Date:
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


Re: Forcing connections closed

From
Juan Miguel
Date:
>'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 -).



Re: Forcing connections closed

From
"Spiegelberg, Greg"
Date:
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