On 11/30/2012 11:04 AM, Steve Singer wrote:
> On 12-11-27 11:59 AM, Frank McGeough wrote:
>> I'm writing to ask about using Slony switchover capability. I've never
>> actually been able to make this work. I'm curious about whether this is
>> a common experience or if, perhaps, there is something that I don't
>> understand about how this should function. The environment that I have
>> is a database that is in constant use. That is, there is always at least
>> a low level of DML (inserts for the most part) occurring 24 x 7, 365
>> days a year. I've found that Slony will never be able to get a lock in
>> order to perform the switchover and thus I'm left with tearing
>
> The slony MOVE SET operation needs to have an exclusive lock on all
> tables in the set at the same time.   The recommended way of doing this
> is to have your application stop traffic to the database for long enough
> so that this can be done, and then wait for the event to propogate to
> the new master and then have your application start queries against the
> new master.
>
> If slony gets its locks on a move set in this order
> Table1
> Table2
> Table3
>
> but your application access tables in this order
> Table3
> Table2
> Table1
>
> Your application and slony will deadlock each other.

What Steve said.

Also, if your application is compatible with pgbouncer's transaction 
mode (i.e. not using server side prepared statements), then you can use 
that to PAUSE and RESUME the real database connections without even 
stopping the application. This can be completely scripted and pgbouncer 
can even do the redirection of traffic on RESUME.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin