Re: Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

On 21.11.2013 22:53, Andres Freund wrote:
> On 2013-11-21 12:51:17 -0800, Josh Berkus wrote:
>> On 11/21/2013 12:46 PM, Andres Freund wrote:
>>> The problem is starting with hot_standby=on on a system with
>>> recovery.conf present. It is independent of whether you use streaming
>>> replication, archive based recovery, or just shutdown the server and
>>> manually copy xlog segments there.
>>> As long as hot_standby=on, and recovery.conf is present you can hit the
>>> bug.
>>
>> Oh, aha.  There have to be some transactions which are awaiting
>> checkpoint, though, correct?  As in, if there's no activity on the
>> master, you can't trigger the bug?
>
> Correct. Also, if you *start* at such a checkpoint you're not vulnerable
> until the standby is restarted.

Keep in mind that autovacuum counts as "activity" in this case. If 
you're unlucky, that is. It's next to impossible to determine 
after-the-fact if there has been activity in the master that might've 
caused problems.

If you have ever set hot_standby=on in your standby server, running one 
of the affected versions, you're at risk. The standby might be corrupt, 
and should be rebuild from a base backup. The higher the transaction 
rate in the master, the higher the risk.

I wouldn't try to narrow it down any further than that, it gets too 
complicated.

- Heikki