Re: postgres hot-standby questions.

On 26 Mar 2015, at 16:07, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

> On Mar 26, 2015, at 8:17 AM, Graeme B. Bell <grb@skogoglandskap.no> wrote:
>>
>> shutdown A (Master)
>> shutdown B (Standby)
>> (A and B  should be identical in terms of WAL and logical data at this point).
>> swap M/S configurations around
>> start B (Master)
>
> First, note that you don't actually have to shut down B and swap master/slave configs. You can use trigger_file in
recovery.conf.

Thanks Simon, that's a good suggestion.

Do you know if using the trigger file makes any change to the DB or WAL which couldn't be replicated on the master when
itcomes back up?  



Actually, it's probably worth mentioning a bit more information about my use case, for anyone reading.

I am concerned about being able to do a clean, safe, quick swap *and soon after, a quick/clean/safe swap back again*
withouthaving to checksum the entire DB (potentially TBs of data) to be certain I'm not breaking something or losing
somethingon the return trip. 

Typically we have these big DBs but we want to make some little change which only takes 10-30 minutes.

This is something that pg_rewind may help to address in 9.5, but I'd like to be able to do it now and we can't risk
usingpg_rewind in our production environment at present.  

Actually even with pg_rewind we have the tricky problem that a user could have a transaction accepted on the master,
whichnever reaches the slave if you failover without a synchronous shutdown process. Such transactions could be rewound
onthe master DB with pg_rewind to allow it to become a slave, but we can't exactly email the users to let them know
theircompleted transaction wasn't durable after all. 

> In general, with async replication, you don't know that every last transaction is replicated at any moment. (And of
coursewith synch replication, you add points of failure.) 

With fully synchronous replication the performance issue is a real killer for us.
I suppose what I'm wondering is about async normal operation, but sync operation during shutdown.

It might seem a strange compromise but in practice something like 95% of the time when the server goes down it's a
controlleddowntime not a failure. So we would benefit from sync during controlled shutdown often but almost never
duringordinary operation. So the cost/benefit for fully sync operation is terrible for us but the cost/benefit of
sync-on-shutdownis huge. 

I could try to script it using e.g. pg_last_xlog_receive_location,  pg_last_xlog_replay_location, but that doesn't help
somuch when the DB decides to complete shutdown while the packet is still on the wire, and the packet goes missing.  


> I don't actually know the answer to your questions, because in my use, the connection between server & replica is
high-bandwidthlow-latency. My routine is to shut down all services that access the db, then shut down the db. The
progressionof service shutdown pretty much guarantees that if the connection is up, the replica is up to date well
beforethe master is shut down. So all I have to do is, after access is shut down use one of the many methods to check
replicationlag, just as a double-check that replication was working. 
>
> It does seem to me it would be a good idea to 1) document explicitly in 17.5 "Shutting Down the Server" what happens
withasync replication (I suspect all your guesses are correct) and 2) provide some option to wait for replication to
finish.
>
> The thing about 2 is that we probably don't want it to be automatic or default, because the nature of asynch replicas
isthat it's not uncommon to have ones far away, over slower less-reliable links, which may take an unknown amount of
timeto come up to date. It's not uncommon to want to bring the master down for a point update or some config change,
andimmediately bring it back up, in which case we don't want the shutdown delayed. It's also not uncommon to have
multiplereplicas, some close by that should probably be always up-to-date, and some far away for protection against big
disasters,which may lag. So the "wait for replica" option to shut down would need a way to specify *which* replica/s we
wantedto wait for. 

I agree, a non-default option to do sync-shutdown sounds like what I'm thinking of. Good points about the multi-standby
problem.

Graeme Bell