Re: data loss with pg_standby when doing a controlled failover

Hi,

On Mon, Apr 6, 2009 at 11:13 PM, Andreas Pflug
 wrote:
> Scott Mead wrote:
>>
>>
>>
>> On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug
>> > wrote:
>>
>> =A0 =A0 Running 8.3.7, I have a warm standby configuration with a
>> =A0 =A0 archive_timeout of 10min.
>>
>> =A0 =A0 It's obvious that there's a 10min period where data could be lost
>> =A0 =A0 if the
>> =A0 =A0 master fails and the warm standby server has to take over. What'=
s not
>> =A0 =A0 obvious is that this is true even if the master server is shut d=
own
>> =A0 =A0 regularly, because it will not write out a last log segment to t=
he
>> =A0 =A0 archive. As a consequence, when doing a controlled failover (for
>> =A0 =A0 maintenance purposes or so) all data changed after the last
>> =A0 =A0 archive copy
>> =A0 =A0 will be lost.
>> =A0 =A0 IMHO this should be mentioned in the docs explicitly (I find it =
quite
>> =A0 =A0 surprising that data can be lost even if the system is shutdown
>> =A0 =A0 correctly), or better when shutting down the postmaster should
>> =A0 =A0 spit all
>> =A0 =A0 log segments containing all changes when archiving is on so the =
warm
>> =A0 =A0 standby server can catch up.
>>
>>
>>
>> You make an excellent point. =A0If you're looking for a way to mitigate
>> this risk, run:
>>
>> =A0 =A0 select pg_switch_xlog() ;
>>
>> =A0 =A0Before 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=3D'' 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,

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