Re: pg_upgrade 13.6 to 15.1? [Solved: what can go wrong, will...]

On Mon, 16 Jan 2023 09:16:27 +1100 Gavan Schneider wrote:

>On 16 Jan 2023, at 8:59, pf(at)pfortin(dot)com wrote:
>
>> encodings for database "template1" do not match: old "UTF8", new
>> "SQL_ASCII" Failure, exiting
>>
>Suggest the old dB using UTF8 is the better practice, and the new dB should do likewise

I was surprised to see pg_upgrade even suggest that...

>> "template1" is not a DB I've ever messed with; so this will require that
>> I fire up the old version and change the encoding somehow?
>>
>This is created at initdb and mostly you don’t need/want to mess with it

I should have checked "initdb --help" first; but now I've used:
initdb -E UTF8 /mnt/work/var/lib/pgsql/data

and got burned (2nd time) by one file with root:root ownership; fixed
with:
chown postgres:postgres /mnt/work/var/lib/pgsql/data13/base/24597/35874

I have no clue how a single file would have root ownership; but found
this a few days ago, and forgot to fix it in both copies of the DB. Of
course, this put me on a new path of [recoverable] disaster... ;/

pg_upgrade aborted on it; but only after getting to a point of "no
return". The documentation alludes to checking everything before
proceeding; but it's the story of my life to find the unexpected...

[postgres(at)pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin
-d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link
-U postgres
Performing Consistency Checks -----------------------------
[snip]
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
[snip]
Adding ".old" suffix to old global/pg_control ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from
/mnt/work/var/lib/pgsql/data13/global/pg_control.old. Because "link" mode
was used, the old cluster cannot be safely started once the new cluster
has been started.

Linking user relation files
/mnt/work/var/lib/pgsql/data13/base/24597/35874
error while creating link for relation "public.vr_snapshot_2022_01_01"
("/mnt/work/var/lib/pgsql/data13/base/24597/35874" to
"/mnt/work/var/lib/pgsql/data/base/24597/35874"): Operation not permitted
Failure, exiting

OK... starting over...
rm -rf data
initdb -E UTF8 /mnt/work/var/lib/pgsql/data
/usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin -d
/mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link -U
postgres
Performing Consistency Checks -----------------------------
Checking cluster versions ok
pg_controldata: fatal: could not open file
"/mnt/work/var/lib/pgsql/data13/global/pg_control" for reading: No such
file or directory

The source cluster lacks cluster state information:
Failure, exiting

Sigh... this should "fix" it:
mv /mnt/work/var/lib/pgsql/data13/global/pg_control.old
/mnt/work/var/lib/pgsql/data13/global/pg_control

Yup... Success!! Upgrade done. Again, sorry for the noise; but hope
the above helps with other issues that can go wrong during an upgrade...

Thanks Tom, Gavan, et al!!

Pierre

>> Is this likely to repeat for my actual databases?
>>
>AFAICT the least work option is to redo the initdb for the new v15.1 database. There is a lot of pain (and potential data corruption) to be had trying to reconfigure the old one before it can be moved.
>
>Personally, UTF8 is the way to go. It will handle everything in the old database and the future brings to the new one. I can see no advantage in pure ASCII when there is the potential for the real world to be contributing text. And there could well be non-ASCII characters lurking in the old dB, especially since someone set it up to receive them.
>
>Regards
>
>Gavan Schneider
>——
>Gavan Schneider, Sodwalls, NSW, Australia
>Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
>— H. L. Mencken, 1920
>
>
>