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
