Re: data loss with pg_standby when doing a controlled failover

Hi,

On Mon, Apr 6, 2009 at 11:13 PM, Andreas Pflug
<pgadmin(at)pse-consulting(dot)de> wrote:
> Scott Mead wrote:
>>
>>
>>
>> On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug
>> <pgadmin(at)pse-consulting(dot)de <mailto:pgadmin(at)pse-consulting(dot)de>> wrote:
>>
>>     Running 8.3.7, I have a warm standby configuration with a
>>     archive_timeout of 10min.
>>
>>     It's obvious that there's a 10min period where data could be lost
>>     if the
>>     master fails and the warm standby server has to take over. What's not
>>     obvious is that this is true even if the master server is shut down
>>     regularly, because it will not write out a last log segment to the
>>     archive. As a consequence, when doing a controlled failover (for
>>     maintenance purposes or so) all data changed after the last
>>     archive copy
>>     will be lost.
>>     IMHO this should be mentioned in the docs explicitly (I find it quite
>>     surprising that data can be lost even if the system is shutdown
>>     correctly), or better when shutting down the postmaster should
>>     spit all
>>     log segments containing all changes when archiving is on so the warm
>>     standby server can catch up.
>>
>>
>>
>> You make an excellent point.  If you're looking for a way to mitigate
>> this risk, run:
>>
>>     select pg_switch_xlog() ;
>>
>>    Before shutting down.
> Sort of, unless some other user succeeds to commit a transaction after
> pg_switch_xlog, and before the database ceases operation.
>
> My "graceful failover" procedure now includes this workaround:
> - shutdown server
> - restart server with --listen_addresses='' to prevent other users to
> connect (there are no local users on the server machine)
> - pg_switch_xlog()
> - shutdown finally
> - let the warm server continue

What if new xlogs are generated by autovacuum or bgwriter
between pg_switch_xlog and final shutdown? Those xlogs
can be ignored?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center