Thread: 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@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
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 -------
On 5/3/06, Jim Buttafuoco <jim@contactbda.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@gmail.com> To: jim@contactbda.com Cc: pgsql-hackers <pgsql-hackers@postgresql.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@contactbda.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 -------