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

On 20.11.2013 17:06, Kevin Grittner wrote:
> Andres Freund <andres@2ndquadrant.com> wrote:
>> On 2013-11-20 06:21:13 -0800, Kevin Grittner wrote:
>
>
>>>   So as long as there are no open transactions or prepared
>>>   transactions on the master which started before the release with
>>>   the fix is applied, VACUUM FREEZE would be guaranteed to work?
>>>   Since I don't see how a non-prepared transaction would be running
>>>   from before a minor release upgrade, that just means we have to
>>>   make sure there are no prepared transactions from before the
>>>   upgrade?
>>
>> That's not a bad point. So the way to fix it would be:
>>
>> 1) Restart the standby to the new minor release, wait for catchup
>> 2) Restart the primary (fast or smart) to the new minor release
>> 3) Acquire enough new xids to make sure we cross a clog page (?)
>> 4) Jot down a new xid:  SELECT txid_current()::bigint % (1::bigint<<33-1)
>> 5) vacuumdb -z -a
>> 6) Ensure that there are no prepared xacts older than 3) around
>> SELECT *
>> FROM pg_prepared_xacts
>> ORDER BY age(transaction) DESC LIMIT 1;
>> 7) Ensure the xmin horizon is above the one from: 3:
>> SELECT datname, datfrozenxid
>> FROM pg_database
>> WHERE datname != 'template0'
>> ORDER BY age(datfrozenxid) DESC LIMIT 1;
>> 8) Get the current lsn: SELECT pg_current_xlog_location();
>> 9) verify on each standby that SELECT pg_last_xlog_receive_location() is
>>     past 7)
>> 10) be happy
>>
>> I am not sure how we can easily compute that 6) and 7) are past 3) in
>> the presence of xid wraparounds.
>
>
> I may well be missing something here, but wouldn't it be sufficient to?:
> 1) Restart the standby to the new minor release, wait for catchup
> 2) Restart the primary (fast or smart) to the new minor release
> 3) Run VACUUM FREEZE (optionally with ANALYZE) in each database on primary
> 4) Run CHECKPOINT command on primary, or just wait for one to run
> 5) Wait for standby to process to the checkpoint
> 6) Be happy

Isn't it possible that the standby has already incorrectly set 
HEAP_XMIN_INVALID hint bit on a page? The full page images generated by 
VACUUM FREEZE will correct the damage, but if not, e.g. because 
full_page_writes=off, strange things will happen.

Personally, I wouldn't trust anything less than a new base backup.

- Heikki