pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pf@pfortin.com
To: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade 13.6 to 15.1?  [Solved: what can go wrong, will...]
Date: Sun, 15 Jan 2023 17:56:41 -0500
Message-ID: <20230115175641.6c3134ff@pfortin.com> (raw)
In-Reply-To: <F54D3935-C4AF-4A76-8E0A-6B529ABCD35D@pendari.org>
References: <20230115142732.0ec38e2a.pfortin@pfortin.com>
	<1650559.1673812055@sss.pgh.pa.us>
	<20230115153919.6d21ea50@pfortin.com>
	<1693377.1673816360@sss.pgh.pa.us>
	<20230115163808.22072dfc@pfortin.com>
	<20230115165933.473a414f.pfortin@pfortin.com>
	<F54D3935-C4AF-4A76-8E0A-6B529ABCD35D@pendari.org>

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 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
>
>
>





Message-ID: <20230115175641.6c3134ff@pfortin.com>
Permalink:  ../20230115175641.6c3134ff@pfortin.com/
Also on:    postgresql.org/message-id/20230115175641.6c3134ff@pfortin.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: pf@pfortin.com
  Subject: Re: pg_upgrade 13.6 to 15.1?  [Solved: what can go wrong, will...]
  In-Reply-To: <20230115175641.6c3134ff@pfortin.com>
  References: <20230115142732.0ec38e2a.pfortin@pfortin.com> <1650559.1673812055@sss.pgh.pa.us> <20230115153919.6d21ea50@pfortin.com> <1693377.1673816360@sss.pgh.pa.us> <20230115163808.22072dfc@pfortin.com> <20230115165933.473a414f.pfortin@pfortin.com> <F54D3935-C4AF-4A76-8E0A-6B529ABCD35D@pendari.org> <20230115175641.6c3134ff@pfortin.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox