Re: [GENERAL] pg_upgrade problem

Alvaro Herrera wrote:
> > > I don't understand the pg_upgrade code here. It is setting the
> > > datfrozenxid and relfrozenxid values to the latest checkpoint's NextXID,
> > >
> > > /* set pg_class.relfrozenxid */
> > > PQclear(executeQueryOrDie(conn,
> > > "UPDATE pg_catalog.pg_class "
> > > "SET relfrozenxid = '%u' "
> > > /* only heap and TOAST are vacuumed */
> > > "WHERE relkind IN ('r', 't')",
> > > old_cluster.controldata.chkpnt_nxtxid));
> > >
> > > but I don't see why this is safe. I mean, surely the previous
> > > vacuum might have been a lot earlier than that. Are these values reset
> > > to more correct values (i.e. older ones) later somehow? My question is,
> > > why isn't the new cluster completely screwed?
> >
> > Have you looked at my pg_upgrade presentation?
> >
> > http://momjian.us/main/presentations/features.html#pg_upgrade
>
> I just did, but it doesn't explain this in much detail. (In any case I
> don't think we should be relying in a PDF presentation to explain the
> inner pg_upgrade details. I think we should rely more on the
> IMPLEMENTATION file rather than your PDF ... amusingly that file doesn't
> mention the frozenxids.)
>
> > This query happens after we have done a VACUUM FREEEZE on an empty
> > cluster.
>
> Oh, so it only affects the databases that initdb created, right?
> The other ones are not even created yet.

Right.

> > pg_dump --binary-upgrade will dump out the proper relfrozen xids for
> > every object that gets its file system files copied or linked.
>
> Okay. I assume that between the moment you copy the pg_clog files from
> the old server, and the moment you do the UPDATEs on pg_class and
> pg_database, there is no chance for vacuum to run and remove clog
> segments.

Right, we disable it, and had a long discussion about it. We actually
start the server with:

"-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",

> Still, it seems to me that this coding makes Min(datfrozenxid) to go
> backwards, and that's bad news.

Yes, it is odd, but I don't see another option. Remember the problem
with xid wrap-around --- we really are defining two different xid eras,
and have to freeze to make that possible.

> > > I wonder if pg_upgrade shouldn't be doing the conservative thing here,
> > > which AFAICT would be to set all frozenxid values as furthest in the
> > > past as possible (without causing a shutdown-due-to-wraparound, and
> > > maybe without causing autovacuum to enter emergency mode either).
> >
> > I already get complaints about requiring an "analyze" run after the
> > upgrade --- this would make it much worse. In fact I have to look into
> > upgrading optimizer statistics someday.
>
> Why would it make it worse at all? It doesn't look to me like it
> wouldn't affect in any way. The only thing it does, is tell the system
> to keep clog segments around.

It will cause excessive vacuum freezing to happen on startup, I assume.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +