Re: pg_upgrade questions

On Wed, Nov 9, 2022 at 7:25 AM Sad Clouds <cryintothebluesky(at)gmail(dot)com>
wrote:

> Hello, I would like to ask a few questions about pg_upgrade when using
> --link option to avoid copying data files.
>
> 1. After running pg_upgrade but before starting new cluster, is it 100%
> safe to abort and go back to using the old cluster? For example:
>
> /usr/PostgreSQL/15/bin/pg_upgrade --link \
> --old-datadir "/data/pgdb" \
> --new-datadir "/data/pgdb.new" \
> --old-bindir "/usr/PostgreSQL/9.6/bin" \
> --new-bindir "/usr/PostgreSQL/15/bin"
>
> Upgrade succeeds but before I start the new cluster, I change my mind
> and want to revert to using the old cluster. Can I simply revert to
> using PostgreSQL 9.6 with these commands:
>
> rm -rf /data/pgdb.new
> mv /data/pgdb/global/pg_control.old /data/pgdb/global/pg_control
>
> Am I correct in thinking that apart from pg_control file, "pg_upgrade
> --link" does not modify the old cluster files in any incompatible ways?
>

Read step 17.

https://www.postgresql.org/docs/current/pgupgrade.html

>
> 2. Older pg_upgrade binary did not support --jobs argument. When
> upgrading from such older clusters, running the latest "pg_upgrade
> --jobs=N" fails at the very start. Is this expected?
>

No, it is not expected. Whether parallelism is used or not should never
affect the correctness of an upgrade or the state of the resultant cluster.

>
> Does this mean pg_upgrade will not work on standby if it is out of sync
> with the primary?

It will not, because once the new cluster launches there is no WAL that can
fix any inconsistencies.

> The page also talks about using rsync to copy data
> from primary to standby server. I would like to avoid doing large data
> copy. Are these upgrade steps likely to work:
>
> a) Disable replication temporary.
> b) Stop standby server and run pg_upgrade.
> c) Stop primary server and run pg_upgrade.
> d) Enable replication and start primary and standby and allow them to
> synchronize their data.
>
>
I cannot tell if you are talking about physical or logical
replication...but only the former matters for this purpose. I don't know
why you'd want to disable replication, what you need to do is ensure that
there is zero activity on the primary and that the standby has caught up
applying all WAL that has already been generated (and no WAL is pending).
Hence the requirement to check pg_controldata.

Step 11 covers your options. I don't suggest you invent your own.

rsync is the means by which you avoid "large data transfers".

David J.