Here is something I just developed to solve the problem of killing
connections prior to dropping:
kill `ps auxww | grep 'postgres: postgres <database>' | grep -v 'grep'
| perl -F"\s+" -ane 'print "$F[1] ";'`
Basically, this takes a process listing, finds all entries
corresponding to connections to the target database, excludes the
calling command, then isolates the pids using Perl.
If you replace <database> with the name of the database you're trying
to drop, this should kill all backends connected to that database. This
seems to work under Debian, but it's certainly not terribly portable or
secure. Also, I'd like to avoid relying on Perl, but I couldn't figure
out how to use cut since pids are variable length. Finally, this
doesn't prevent new connections from cropping up in a high-traffic
environment.
Ideally, such a process would do this in a portable fashion:
1. Disable incoming connections.
2. Kill current connections
The way I know of to do this would be to generate a temporary
pg_hba.conf file specifically to switch to single-user mode. Move that
into place. Then kill the connections. Then perform whatever action is
necessary (in this case, dropping the database). Then restore the
original pg_hba.conf file.
I'm curious, though, too, to know whether anyone has anything more
sophisticated.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
