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

On 2013-11-20 07:06:04 -0800, Kevin Grittner wrote:
> > 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

Well, in some cases it might. But what if there's a prepared xact
around? Or a transaction started directly after 2) preventing
FreezeLimit to go up? Or vacuum_defer_cleanup_age is set? Or there's
another bug like 4c697d8f4845823a8af67788b219ffa4516ad14c?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services