Re: pg_upgrade can result in early wraparound on databases with hightransaction load

On Mon, May 20, 2019 at 3:10 AM Jason Harvey <jason@reddit.com> wrote:
> This week I upgraded one of my large(2.8TB), high-volume databases from 9 to 11. The upgrade itself went fine. About
twodays later, we unexpectedly hit transaction ID wraparound. What was perplexing about this was that the age of our
oldest`datfrozenxid` was only 1.2 billion - far away from where I'd expect a wraparound. Curiously, the wraparound
errorreferred to a mysterious database of `OID 0`: 
>
> UPDATE ERROR:  database is not accepting commands to avoid wraparound data loss in database with OID 0
>
> We were able to recover after a few hours by greatly speeding up our vacuum on our largest table.
>
> In a followup investigation I uncovered the reason we hit the wraparound so early, and also the cause of the
mysteriousOID 0 message. When pg_upgrade executes, it calls pg_resetwal to set the next transaction ID. Within
pg_resetwalis the following code:
https://github.com/postgres/postgres/blob/6cd404b344f7e27f4d64555bb133f18a758fe851/src/bin/pg_resetwal/pg_resetwal.c#L440-L450
>
> This sets the controldata to have a fake database (OID 0) on the brink of transaction wraparound. Specifically, after
pg_upgradeis ran, wraparound will occur within around 140 million transactions (provided the autovacuum doesn't finish
first).I confirmed by analyzing our controldata before and after the upgrade that this was the cause of our early
wraparound.
>
> Given the size and heavy volume of our database, we tend to complete a vacuum in the time it takes around 250 million
transactionsto execute. With our tunings this tends to be rather safe and we stay well away from the wraparound point
undernormal circumstances. 

This does seem like an unfriendly behavior. Moving the thread over to
the -hackers list for further discussion...

--
Peter Geoghegan