On Mon, 16 Jan 2023 09:16:27 +1100 Gavan Schneider wrote: >On 16 Jan 2023, at 8:59, pf@xxxxxxxxxxx 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@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 > > >