Date: Wed, 9 Nov 2022 14:25:10 +0000
From: Sad Clouds <cryintothebluesky@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: pg_upgrade questions
Message-Id: <20221109142510.2b528bd331c5d9482c59291d@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/20221109142510.2b528bd331c5d9482c59291d%40gmail.com>
Precedence: bulk

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?

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?

3. I'm a bit confused about the correct procedure when it comes to
upgrading primary and standby clusters. Looking at
https://www.postgresql.org/docs/current/pgupgrade.html it mentions the
following:

"If you are upgrading standby servers using methods outlined in section
Step 11, verify that the old standby servers are caught up by running
pg_controldata against the old primary and standby clusters. Verify
that the “Latest checkpoint location” values match in all clusters.
(There will be a mismatch if old standby servers were shut down before
the old primary or if the old standby servers are still running.) Also,
make sure wal_level is not set to minimal in the postgresql.conf file
on the new primary cluster."

Does this mean pg_upgrade will not work on standby if it is out of sync
with the primary? 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.

Thanks.