Re: sequences and pg_upgrade

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> I was toying with a couple of ideas that would involve changing the
> storage of sequences. (Say, for the sake of discussion, removing the
> problematic/useless sequence_name field.) This would cause problems for
> pg_upgrade, because pg_upgrade copies the "heap" storage of sequences
> like it does for normal tables, and we have no facilities for effecting
> any changes during that.

> There was a previous discussion in the early days of pg_migrator, which
> resulted in the current behavior:
> https://www.postgresql.org/message-id/flat/20090713220112.GF7933%40klana.box

> This also alluded to what I think was the last change in the sequence
> storage format (10a3471bed7b57fb986a5be8afdee5f0dda419de) between
> versions 8.3 and 8.4. How did pg_upgrade handle that?

I think it probably never did handle that. pg_upgrade doesn't currently
claim to support migrating from 8.3, and the thread you mention shows that
the original attempt at 8.3->8.4 migration crashed-and-burned for numerous
unrelated reasons. We may not have ever got to the point of noticing that
10a3471be also created a problem.

> I think the other solution mentioned in that thread would also work:
> Have pg_upgrade treat sequences more like system catalogs, whose format
> changes between major releases, and transferred them via the
> dump/restore route. So instead of copying the disk files, issue a
> setval call, and the sequence should be all set up.

Seems reasonable.

If you're proposing to expose --sequence-data as a user-visible option,
the patch set lacks documentation. But I wonder whether it shouldn't
simply be a side-effect of --binary-upgrade. It seems a tad
non-orthogonal for a user switch.

regards, tom lane