pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
5+ messages / 2 participants
[nested] [flat]

* dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
@ 2005-02-21 14:21 Gourish Singbal <gourish@gmail.com>
  2005-02-23 05:41 ` Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Gourish Singbal <gourish@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Gourish Singbal @ 2005-02-21 14:21 UTC (permalink / raw)
  To: pgsql-admin

This Error message was given when i was dropping the database.
There are idle connections to the database as seen from ps -ef | grep postgres

How can i drop the database with out restarting the postgres server.?.

FYI: i am dropping the database to perform restoration of this backup
database using psql  from production.

-- 
Best,
Gourish Singbal



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
  2005-02-21 14:21 dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Gourish Singbal <gourish@gmail.com>
@ 2005-02-23 05:41 ` Gourish Singbal <gourish@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Gourish Singbal @ 2005-02-23 05:41 UTC (permalink / raw)
  To: pgsql-admin

Guys,

postgres :  7.4.5
Os: linux

I need to restore my back up database with production data.
the steps i follow on backup database are:-
1) dropdb
2) createdb
3) psql or pg_restore to import data into the backup database.

Am i doing the correct steps?.

Problems:-
If some clients are connected to the backup database during the 1st
Step (dropdb)
the postmaster throws an error saying clients connected cannot drop database.

Any solution to the above problem would be great...

regards
gourish


On Mon, 21 Feb 2005 19:51:10 +0530, Gourish Singbal <gourish@gmail.com> wrote:
> This Error message was given when i was dropping the database.
> There are idle connections to the database as seen from ps -ef | grep postgres
> 
> How can i drop the database with out restarting the postgres server.?.
> 
> FYI: i am dropping the database to perform restoration of this backup
> database using psql  from production.
> 
> --
> Best,
> Gourish Singbal
> 


-- 
Best,
Gourish Singbal



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
@ 2005-02-25 08:43 Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Thomas F.O'Connell @ 2005-02-25 08:43 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
@ 2005-02-25 16:30 Thomas F.O'Connell <tfo@sitening.com>
  2005-02-28 05:42 ` Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Gourish Singbal <gourish@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Thomas F.O'Connell @ 2005-02-25 16:30 UTC (permalink / raw)
  To: pgsql-admin

pkill -f 'postgres: postgres <database>'

Much cleaner than the previous recipe for those with pkill.

-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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users
  2005-02-25 16:30 Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Thomas F.O'Connell <tfo@sitening.com>
@ 2005-02-28 05:42 ` Gourish Singbal <gourish@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Gourish Singbal @ 2005-02-28 05:42 UTC (permalink / raw)
  To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-admin

Thanks a million,

pkill -f 'postgres: postgres <database>'

really helped. 

regards
gourish




On Fri, 25 Feb 2005 10:30:31 -0600, Thomas F. O'Connell
<tfo@sitening.com> wrote:
> pkill -f 'postgres: postgres <database>'
> 
> Much cleaner than the previous recipe for those with pkill.
> 
> -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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 


-- 
Best,
Gourish Singbal



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-02-28 05:42 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-21 14:21 dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Gourish Singbal <gourish@gmail.com>
2005-02-23 05:41 ` Gourish Singbal <gourish@gmail.com>
2005-02-25 08:43 Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Thomas F.O'Connell <tfo@sitening.com>
2005-02-25 16:30 Re: dropdb: database removal failed: ERROR: database "database_name" is being accessed by other users Thomas F.O'Connell <tfo@sitening.com>
2005-02-28 05:42 ` Gourish Singbal <gourish@gmail.com>

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