Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHBvj-0000sK-Sd for pgsql-general@arkaria.postgresql.org; Sun, 15 Jan 2023 22:56:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHBvi-0003Ce-Js for pgsql-general@arkaria.postgresql.org; Sun, 15 Jan 2023 22:56:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHBvi-0003CU-9P for pgsql-general@lists.postgresql.org; Sun, 15 Jan 2023 22:56:46 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHBvf-0000OW-N9 for pgsql-general@lists.postgresql.org; Sun, 15 Jan 2023 22:56:45 +0000 Received: from pfortin.com (h163.145.185.173.dynamic.ip.windstream.net [173.185.145.163]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 30FMufQI998375 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Sun, 15 Jan 2023 22:56:42 GMT Date: Sun, 15 Jan 2023 17:56:41 -0500 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...] Message-ID: <20230115175641.6c3134ff@pfortin.com> In-Reply-To: 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> X-Mailer: Claws Mail 4.1.1 (GTK 3.24.36; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Lux-Comment: Message 30FMufQI998375 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:to:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=1Jnr+S3Hxf6UgxWemZYeFLZCNTRW6jlgi4fnFayc6SA=; b=qf1SeigQJK8j8ghG8tfEZug6e8vVCH1bN//KDjD8x3l86ZRiVp2OoYiugSIL1qhDPo9IiG3l/IBB+0Gr7Bp9Wz0nGv4Bj1wUu1aggKsEM2+jsYrt5zRUbYhcJy6P5ls98Bt/NsAE7j+hDxftqNnfPtduZ7Qdp6KfQ+9HTqiYLPU= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 >> =20 >Suggest the old dB using UTF8 is the better practice, and the new dB shoul= d do likewise I was surprised to see pg_upgrade even suggest that...=20 >> "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? >> =20 >This is created at initdb and mostly you don=E2=80=99t need/want to mess w= ith 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=20 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 =20 /mnt/work/var/lib/pgsql/data13/base/24597/35874 =20 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... =20 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=20 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? >> =20 >AFAICT the least work option is to redo the initdb for the new v15.1 datab= ase. There is a lot of pain (and potential data corruption) to be had tryin= g to reconfigure the old one before it can be moved. > >Personally, UTF8 is the way to go. It will handle everything in the old da= tabase 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 te= xt. And there could well be non-ASCII characters lurking in the old dB, esp= ecially since someone set it up to receive them. > >Regards > >Gavan Schneider >=E2=80=94=E2=80=94 >Gavan Schneider, Sodwalls, NSW, Australia >Explanations exist; they have existed for all time; there is always a well= -known solution to every human problem =E2=80=94 neat, plausible, and wrong. >=E2=80=94 H. L. Mencken, 1920 > > >