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(at)sss(dot)pgh(dot)pa(dot)us>
To: jim(at)contactbda(dot)com
Cc: "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org>
Sent: Wed, 03 May 2006 14:23:08 -0400
Subject: Re: [HACKERS] drop database command blocking other connections

> "Jim Buttafuoco" <jim(at)contactbda(dot)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 -------