Thread: Problem Dropping a Database with users connected to it
Hi, I'm basically trying to do what the subject says, through various means with no success. The basic situation is that every night we recreate our development database with a complete copy of our live data. The problem is some of the developers (well me especially) leave open connections to the DB at night, so the database drop fails. Now that's ok, but I need some sort of alternative... I thought of: * Disconnecting all other users before dropping the db, but that doesn't seem possible (I could start and stop the db, but that doesn't stop any clients from just reconnecting right away). * Some sort of idle time disconnection. Postgres doesn't seem to support this. * Just drop all the tables, etc. instead of dropping the db. There doesn't seem to be a good way to do this except doing an explicit DROP TABLE foo on all 200 tables. Is there a good recipe for this, an easy way to get a listing of all the tables in a db? Can anyone suggest an approach I missed or enlighten me on how to accomplish one of the above. Thanks very much. -- Eric Dorland eric.dorland@mcgill.ca WCG 514.398-5023 ext. 09562
Just kill the processes. You can grep for postgres AND idle. It doesn't prevent new connections, but doesn't look like an issue in your scenario. If you need that, you can restart with a copy of pg_hba.conf that only allows localhost, do your drop & recreate, and then restart again. Regards. En un mensaje anterior, Eric Dorland escribió: > Hi, > > I'm basically trying to do what the subject says, through various means > with no success. The basic situation is that every night we recreate our > development database with a complete copy of our live data. The problem > is some of the developers (well me especially) leave open connections to > the DB at night, so the database drop fails. Now that's ok, but I need > some sort of alternative... I thought of: > > * Disconnecting all other users before dropping the db, but that doesn't > seem possible (I could start and stop the db, but that doesn't stop any > clients from just reconnecting right away).
On Fri, Jan 14, 2005 at 11:16:16 -0500, Eric Dorland <eric.dorland@mcgill.ca> wrote: > > * Disconnecting all other users before dropping the db, but that doesn't > seem possible (I could start and stop the db, but that doesn't stop any > clients from just reconnecting right away). You could use an alter pg_hba.conf file while doing the drop. > * Just drop all the tables, etc. instead of dropping the db. There > doesn't seem to be a good way to do this except doing an explicit DROP > TABLE foo on all 200 tables. Is there a good recipe for this, an easy > way to get a listing of all the tables in a db? If all of the tables are in the public schema, dropping that schema should cascade to dropping all of the tables.
Eric Dorland <eric.dorland@mcgill.ca> writes:
> I'm basically trying to do what the subject says, through various means
> with no success. The basic situation is that every night we recreate our
> development database with a complete copy of our live data. The problem
> is some of the developers (well me especially) leave open connections to
> the DB at night, so the database drop fails. Now that's ok, but I need
> some sort of alternative... I thought of:
> * Disconnecting all other users before dropping the db, but that doesn't
> seem possible (I could start and stop the db, but that doesn't stop any
> clients from just reconnecting right away).
If the problem is people leaving idle connections when they go home,
that doesn't seem like a big issue. Besides, you could temporarily
add a line to pg_hba.conf to reject new connections.
> * Just drop all the tables, etc. instead of dropping the db. There
> doesn't seem to be a good way to do this except doing an explicit DROP
> TABLE foo on all 200 tables. Is there a good recipe for this, an easy
> way to get a listing of all the tables in a db?
Maybe you could drop and recreate the containing schema as a substitute
for retail drop operations. Note however that if people leave open
transactions then you could still have issues with tables being locked.
On the whole, forcibly killing all the connections seems like a better
idea.
regards, tom lane
On Fri, 2005-01-14 at 10:58 -0600, Bruno Wolff III wrote: > On Fri, Jan 14, 2005 at 11:16:16 -0500, > Eric Dorland <eric.dorland@mcgill.ca> wrote: > > > > * Disconnecting all other users before dropping the db, but that doesn't > > seem possible (I could start and stop the db, but that doesn't stop any > > clients from just reconnecting right away). > > You could use an alter pg_hba.conf file while doing the drop. > > > * Just drop all the tables, etc. instead of dropping the db. There > > doesn't seem to be a good way to do this except doing an explicit DROP > > TABLE foo on all 200 tables. Is there a good recipe for this, an easy > > way to get a listing of all the tables in a db? > > If all of the tables are in the public schema, dropping that schema should > cascade to dropping all of the tables. I had not considered dropping the schema, but that sounds like a good solution. I'll give it a shot. -- Eric Dorland eric.dorland@mcgill.ca WCG 514.398-5023 ext. 09562