Re: pg_upgrade and wraparound - Mailing list pgsql-general

On 06/11/2018 11:32 AM, Alexander Shutyaev wrote:

I'm back with more details.

First, I've deleted the smaller sslentry database, since I don't need it, just so that it doesn't somehow spoil the picture. Now there is only 1 user database - bof (OID=16400). After that I've ran the pg_upgrade on a clean 10.4 cluster and it failed in the same way.

Now, the answers to your queries.

 >> 2) The upgrade stops because of transaction ID wraparound, which is strange as that is not showing up in the 9.6 cluster I presume. You might want the queries found below on the 9.6 and 10 clusters to help figure this out:

 >> SELECT c.oid::regclass as table_name,
 >>        greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
 >> FROM pg_class c
 >> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 >> WHERE c.relkind IN ('r', 'm');

9.6 result - see attached 96-query1.txt
10.4 result - see attached 104-query1.txt

I should have been clearer in my previous post, the above query is per database. From the query below I am going to say the above query was done on the bof database. Is that correct?

Given the below from 96-query2.txt:

template0 | 110588398

Can you run the table_name query in template0 in the 9.6 cluster?

 >> SELECT datname, age(datfrozenxid) FROM pg_database;

9.6 result - see attached 96-query2.txt
10.4 result - see attached 104-query2.txt

 >>  It might be useful
 >> to see pg_controldata output for the old cluster, as well as
 >> "select datname, datfrozenxid from pg_database" output from the
 >> old cluster.

for the query - see above, for pg_controldata:

9.6 - see attached 96-pg_controldata.txt
10.4 - see attached 104-pg_controldata.txt

 >> Alexander, could you hack things up so autovacuum logging is enabled
 >> (log_autovacuum_min_duration=0), and see whether it's triggered?

I'll be happy to, but that will require to run pg_upgrade once more and that takes more that half a day and during this time clusters are not available to me. Given the data I'm attaching it may happen that the colleagues will want to see something else from my clusters or maybe change some settings before running the pg_upgrade again. Therefore, I'll wait 12 hours after this message in case there will be any more requests and the I'll run the pg_upgrade again.

Thank you all for trying to solve this matter, this is much appreciated! :)

2018-06-11 20:29 GMT+03:00 Andres Freund <andres@anarazel.de <mailto:andres@anarazel.de>>:

    On 2018-06-11 13:14:12 -0400, Tom Lane wrote:

    > Andres Freund <andres@anarazel.de <mailto:andres@anarazel.de>> writes:
    > > I suspect the issue is that pg_resetwal does:
    > >     if (set_xid != 0)
    > >     {
    > >             ControlFile.checkPointCopy.nextXid = set_xid;
    >     > >             /*
    > >              * For the moment, just set oldestXid to a value that will force
    > >              * immediate autovacuum-for-wraparound.  It's not clear whether adding
    > >              * user control of this is useful, so let's just do something that's
    > >              * reasonably safe.  The magic constant here corresponds to the
    > >              * maximum allowed value of autovacuum_freeze_max_age.
    > >              */
    > >             ControlFile.checkPointCopy.oldestXid = set_xid - 2000000000;
    > >             if (ControlFile.checkPointCopy.oldestXid < FirstNormalTransactionId)
    > >                     ControlFile.checkPointCopy.oldestXid += FirstNormalTransactionId;
    > >             ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
    > >     }
    >     > > but we have codepath that doesn't check for oldestXidDB being
    > > InvalidOid.  Not great.
    >     > Hm, I think I'd define the problem as "pg_resetwal is violating the
    > expectation that oldestXidDB be valid".

    Well, what could it do otherwise?  ForceTransactionIdLimitUpdate()
    currently does a syscache check for database existence. That'll just
    return a lookup failure for InvalidOid, so we're reasonably good on that
    front.

    Using a hardcoded 2000000000 seems worse, will have funny results if
    running with a smaller autovacuum_freeze_max_age...


    > However, this just explains the basically-cosmetic issue that the
    > complaint message mentions OID 0.  It doesn't really get us to the
    > answer to why Alexander is seeing a failure.  It might be useful
    > to see pg_controldata output for the old cluster, as well as
    > "select datname, datfrozenxid from pg_database" output from the
    > old cluster.

    pg_upgrade starts the server with autovacuum disabled, I suspect
    restoring all the large objects ends up using a lot of transaction
    ids. GetNewTransactionId() should start autovacuum, but I'd guess that's
    where things are going wrong for some reason.

    Alexander, could you hack things up so autovacuum logging is enabled
    (log_autovacuum_min_duration=0), and see whether it's triggered?

    I'm not entirely clear why pg_restore appears to use a separate
    transaction for each large object, surely exascerbating the problem.

    Greetings,

    Andres Freund

--
Adrian Klaver
adrian.klaver@aklaver.com