X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C0A9C563AC for ; Fri, 25 Feb 2005 08:42:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78858-09 for ; Fri, 25 Feb 2005 08:42:52 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 8838E56254 for ; Fri, 25 Feb 2005 08:42:50 +0000 (GMT) Received: (qmail 31821 invoked from network); 25 Feb 2005 08:43:07 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 25 Feb 2005 08:43:07 -0000 Mime-Version: 1.0 (Apple Message framework v619.2) Content-Transfer-Encoding: 7bit Message-Id: <75eea8d3cae3015c5750ec89737b73fa@sitening.com> Content-Type: text/plain; charset=US-ASCII; format=flowed To: PgSQL Admin From: Thomas F.O'Connell Subject: Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Date: Fri, 25 Feb 2005 02:43:05 -0600 X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/299 X-Sequence-Number: 16467 Here is something I just developed to solve the problem of killing connections prior to dropping: kill `ps auxww | grep 'postgres: postgres ' | 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 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