Re: optimize file transfer in pg_upgrade

On Wed, Nov 6, 2024 at 5:07 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> these user relation files will have the same name. Therefore, it can be
> much faster to instead move the entire data directory from the old cluster
> to the new cluster and to then swap the catalog relation files.

This is a cool idea.

> Another interesting problem is that pg_upgrade currently doesn't transfer
> the sequence data files. Since v10, we've restored these via pg_restore.
> I believe this was originally done for the introduction of the pg_sequence
> catalog, which changed the format of sequence tuples. In the new
> catalog-swap mode I am proposing, this means we need to transfer all the
> pg_restore-generated sequence data files. If there are many sequences, it
> can be difficult to determine which transfer mode and synchronization
> method will be faster. Since sequence tuple modifications are very rare, I
> think the new catalog-swap mode should just use the sequence data files
> from the old cluster whenever possible.

Maybe we should rethink the decision not to transfer relfilenodes for
sequences. Or have more than one way to do it. pg_upgrade
--binary-upgrade --binary-upgrade-even-for-sequences, or whatever.

--
Robert Haas
EDB: http://www.enterprisedb.com