drop database command blocking other connections

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.

thanks
Jim

"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

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 -------

On 5/3/06, Jim Buttafuoco <jim(at)contactbda(dot)com> wrote:
> 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.

I don't have a "fix", but I can offer a workaround.

When we need to drop large DBs we drop them a schema at a time. DROP
SCHEMA does *not* block new connections into the server. Once the data
it out of the schema(s), a DROP DATABASE on a nearly empty database
does not block new connections for more than a moment.

nice workaround, I am going to modify my procedure to drop the "public" schema first (it is the largest one).

---------- Original Message -----------
From: "Tony Wasson" <ajwasson(at)gmail(dot)com>
To: jim(at)contactbda(dot)com
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Sent: Wed, 3 May 2006 14:09:05 -0700
Subject: Re: [HACKERS] drop database command blocking other connections

> On 5/3/06, Jim Buttafuoco <jim(at)contactbda(dot)com> wrote:
> > 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.
>
> I don't have a "fix", but I can offer a workaround.
>
> When we need to drop large DBs we drop them a schema at a time. DROP
> SCHEMA does *not* block new connections into the server. Once the data
> it out of the schema(s), a DROP DATABASE on a nearly empty database
> does not block new connections for more than a moment.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------