Re: LOCK DATABASE - Mailing list pgsql-hackers

Excerpts from Christopher Browne's message of mié may 18 18:33:14 -0400 2011:
> On Wed, May 18, 2011 at 1:02 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> > So we the lock will be released at end of the session or when the
> > UNLOCK DATABASE command is invoked, right?
> > A question: why will we beign so rude by killing other sessions
> > instead of avoid new connections and wait until the current sessions
> > disconnect?
> 
> There were multiple alternatives suggested, which is probably useful to outline.
> 
> 1.  I suggested that this looks a lot like the controls of pg_hba.conf
> 
> When our DBAs are doing major management of replication, they are
> known to reconfigure pg_hba.conf to lock out all users save for the
> one used by Slony.

Yeah, I mentioned this but I think it actually sucks.

> And I'm not sure it'll be the right thing to lock out "everything
> except the present connection."
> 
> When doing Slony "cluster surgery," it would NOT be acceptable to
> restrict DB activity to a single connection - multiple slon processes
> legitimately need to get in.

Well, I don't intend to lock "everything except the present connection".
The only thing this LOCK DATABASE does is prevent the establishment of
new connections.  Existing connections can continue to exist and work.
So you do the LOCK DATABASE, then boot whoever shouldn't be allowed
(which is a separate step that needs to be taken), then do your deed.
If you want the slon connections to persist, just don't terminate them.

> And that sure does look a lot more like something that's appropriate
> to manage via fiddling with pg_hba.conf than via something smelling
> like "restrict to something looking a lot like single user mode."

I really dislike the idea of having to edit config files for this kind
of thing.  I mean, sure it can be made to work, but it seems the wrong
tool for the job.

> 2.  Jan Wieck pointed out that shutting off access to users would be
> very well accomplished via allowing their access through a connection
> pool (e.g. - like pgbouncer, pgpool), and cutting them off there.
> 
> That seems like a better mechanism to me, too, though we've not been
> able to use any of those pools with our apps thus far.

Yeah, it would work except when it doesn't; it would force you to use a
tool that you may not otherwise need.  So this also seems to me the
wrong solution.

> 3.  There's some analogy to be drawn against pg_ctl...  It has 3 modes
> for shutdown:
> 
> smart - quits once all clients have disconnected
> fast - quit, with proper shutdown of all clients
> immediate - quit without complete shutdown
> 
> Those are all potentially legitimate "modes" for this lockdown, as
> there will be cases where you want to be friendly to applications, as
> well as cases where "We right well announced this 3pm outage to
> everyone, and, it being 3:00:01, I'm booting you off Right Now.

Well, that's a policy decision.  I mean you, as the DBA or as the tool
writer can decide to boot everyone immediately or wait until they are
done with the current transaction or whatever.  I'm just offering the
mechanism to lock out new connections until you're finished with what
you want to do.

> If the mechanism:
> - Doesn't offer the ability to leave Needful Processes (notably,
> replication processes) running
> - Doesn't allow [friendly/unfriendly] operation modes (as in #3)
> then I think the use cases will be a bit thin.

I think we're clear in both fronts :-)

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support