Re: drop database command blocking other connections

Tom,

I am trying to migrate all of my database from 7.4 to 8.1, It takes alot of disk space to have both online at the same
time.  I have done around 2TB of actual disk space to date and have another 6TB to do over the next month or so.  I
have
been moving (with pg_dump 7.4db | pg_dump 8.1db) each database to 8.1 and then dropping the 7.4 one (after some
testing).  I would be nice if this is fixed so when I have to move from 8.1 to 8.2 it will not be an issue.

Thanks for your time
Jim



---------- Original Message -----------
From: Tom Lane <tgl@sss.pgh.pa.us>
To: jim@contactbda.com
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>
Sent: Wed, 03 May 2006 14:23:08 -0400
Subject: Re: [HACKERS] drop database command blocking other connections 

> "Jim Buttafuoco" <jim@contactbda.com> writes:
> > from time to time I have to drop a very large database (1TB+).  The drop database command takes a long time to
complete
> > while its deleting the files.  During this time, no one can connect to the database server, ps displays "startup
> > waiting".  This is with Postgresql 7.4.  Has this been addressed in 8.1, if not, does anyone have some ideas on how
to
> > speed this up.
> 
> No, it'll probably behave the same in CVS HEAD.  The problem is that
> DROP DATABASE has to lock out new connections to the victim database,
> and the mechanism it's using for that is a table-level lock on
> pg_database, rather than something specific to one database.  So
> new connections to *all* DBs in the cluster will be blocked while
> DROP DATABASE runs.
> 
> It strikes me that we could fix this by taking out special locks on the
> database as an object (using LockSharedObject) instead of relying on
> locking pg_database.  There wasn't any locktag convention that'd work
> for that back in 7.4, but it surely seems doable now.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------