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@pfortin.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@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
datacorruption) 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.
Ican see no advantage in pure ASCII when there is the potential for the real world to be contributing text. And there
couldwell 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
>
>
>