Forcing connections closed

[Hadley Willan]

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

Thanks

[Gregory S. Williamson]

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:***@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 » ***@deeperdesign.com » +64(21) 28
41 463
Deeper Design Limited » [phone] » www.deeperdesign.com




---------------------------(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

[Hadley Willan]

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

Post by Gregory S. Williamson
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
e.g.
pg_ctl stop -D /data/postgres/gex_runtime -m fast
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-----
Sent: Thu 1/29/2004 1:03 PM
To: PGSQL Admin
Subject: [ADMIN] Forcing connections closed
Hello,
How do I force postmaster to terminate active connections on other
databases?
Thanks

[Ryan Chambers]

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.

Post by Hadley Willan
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

/pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
e.g.
pg_ctl stop -D /data/postgres/gex_runtime -m fast
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-----
Sent: Thu 1/29/2004 1:03 PM
To: PGSQL Admin
Subject: [ADMIN] Forcing connections closed
Hello,
How do I force postmaster to terminate active connections on other
databases?
Thanks/

--
Design Limited *»* [phone] *»* _www.deeperdesign.com_
<http://www.deeperdesign.com>

[Juan Miguel]

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.

Post by Hadley Willan
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

Post by Gregory S. Williamson
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
e.g.
pg_ctl stop -D /data/postgres/gex_runtime -m fast
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-----
Sent: Thu 1/29/2004 1:03 PM
To: PGSQL Admin
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

[scott.marlowe]

Post by Juan Miguel
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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Juan Miguel]

Post by scott.marlowe
'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

[Andrew Rawnsley]

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.

Post by Juan Miguel
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.

Post by Hadley Willan
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

Post by Gregory S. Williamson
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
e.g.
pg_ctl stop -D /data/postgres/gex_runtime -m fast
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-----
Sent: Thu 1/29/2004 1:03 PM
To: PGSQL Admin
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.
[phone]
www.ravensfield.com


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Spiegelberg, Greg]

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

Post by scott.marlowe
'kill -9' PID will force all backends to flush cache for no good

reason.

Post by scott.marlowe
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

Post by scott.marlowe
-9 is like using a sledge hammer to swat a fly for this.
---------------------------(end of

broadcast)---------------------------

Post by scott.marlowe
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster