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

Andres Freund <andres(at)2ndquadrant(dot)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

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company