Re: pg_upgrade and wraparound

On 06/10/2018 11:46 PM, Alexander Shutyaev wrote:
> >> Is this the regular Postgres log or the pg_upgrade log which should
> be something like pg_upgrade_server.log?
>
> This is the pg_upgrade_dump_16400.log.
>
>>> How did you get into the 10 cluster to report on the database OID's and
> names?
>
> After the pg_upgrade failed I was able to start both clusters, so I
> connected to the new 10.4 cluster and ran the query.

I am at a loss for an explanation. My thoughts:

1) The database with an OID of 0 is a mystery, though it does not seem
to be stopping the upgrade by itself.

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:

https://www.postgresql.org/docs/10/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

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');

SELECT datname, age(datfrozenxid) FROM pg_database;

>
>>> Which database has the large objects?
>
> bof (OID=16400). It is also effectively the only database that matters
> here. The other one - sslentry only contains a couple of tables and a
> dozen of records.
>
>>> Did you check this view to confirm?
>
> Yes, I did:
>
> select * from pg_prepared_xacts;
>  transaction | gid | prepared | owner | database
> -------------+-----+----------+-------+----------
> (0 rows)
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com