Re: [GENERAL] pg_upgrade problem

Excerpts from Bruce Momjian's message of mié ago 31 13:23:07 -0300 2011:
> Alvaro Herrera wrote:
> > Excerpts from hubert depesz lubaczewski's message of lun ago 29 14:49:24 -0300 2011:
> > > On Mon, Aug 29, 2011 at 06:54:41PM +0200, hubert depesz lubaczewski wrote:
> > > > On Fri, Aug 26, 2011 at 05:28:35PM +0200, hubert depesz lubaczewski wrote:
> > > > > On Fri, Aug 26, 2011 at 12:18:55AM -0400, Bruce Momjian wrote:
> > > > > >
> > > > > > OK, this was very helpful. I found out that there is a bug in current
> > > > > > 9.0.X, 9.1.X, and HEAD that I introduced recently when I excluded temp
> > > > > > tables. (The bug is not in any released version of pg_upgrade.) The
> > > > > > attached, applied patches should fix it for you. I assume you are
> > > > > > running 9.0.X, and not 9.0.4.
> > > > >
> > > > > pg_upgrade worked. Now I'm doing reindex and later on vacuumdb -az.
> > > >
> > > > vacuumdb failed. The fail looks very similar to the one I had on 9.0.4.
> > > >
> > > > After long vacuum I got:
> > > > INFO: vacuuming "pg_toast.pg_toast_106668498"
> > > > vacuumdb: vacuuming of database "etsy_v2" failed: ERROR: could not access status of transaction 3429738606
> > > > DETAIL: Could not open file "pg_clog/0CC6": No such file or directory.
> >
> > 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.

> 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.

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

> > 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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support