pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas F.O'Connell <tfo@sitening.com>
To: PgSQL Admin <pgsql-admin@postgresql.org>
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
Message-ID: <75eea8d3cae3015c5750ec89737b73fa@sitening.com> (raw)

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




view thread (5+ messages)  latest in thread

Message-ID: <75eea8d3cae3015c5750ec89737b73fa@sitening.com>
Permalink:  ../75eea8d3cae3015c5750ec89737b73fa@sitening.com/
Also on:    postgresql.org/message-id/75eea8d3cae3015c5750ec89737b73fa@sitening.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tfo@sitening.com
  Subject: Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
  In-Reply-To: <75eea8d3cae3015c5750ec89737b73fa@sitening.com>
  References: <75eea8d3cae3015c5750ec89737b73fa@sitening.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox