Thread: Forcing connections closed
Hello,
How do I force postmaster to terminate active connections on other databases?
Thanks
How do I force postmaster to terminate active connections on other databases?
Thanks
--
|
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
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:
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
--
|
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:
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
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 >> >> > > >
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.
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
>'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 -).
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