On 20.11.2013 17:06, Kevin Grittner wrote:
Andres Freund <and...@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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to