Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnGtr-00CQbg-DN for pgsql-general@arkaria.postgresql.org; Thu, 21 Mar 2024 11:48:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rnGto-00CntO-U4 for pgsql-general@arkaria.postgresql.org; Thu, 21 Mar 2024 11:47:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnGto-00CntG-Hk for pgsql-general@lists.postgresql.org; Thu, 21 Mar 2024 11:47:57 +0000 Received: from fhigh2-smtp.messagingengine.com ([103.168.172.153]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnGtk-005f0a-Ex for pgsql-general@postgresql.org; Thu, 21 Mar 2024 11:47:55 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfhigh.nyi.internal (Postfix) with ESMTP id 6354B11400DC; Thu, 21 Mar 2024 07:47:52 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Thu, 21 Mar 2024 07:47:52 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1711021672; x= 1711108072; bh=bOpYCmMD+zjA4VL+QIUMW8quavTlRTf3XBJkk6lETO4=; b=s CsK+kZMn/0QetfFi9slXb/3cNFyglEQpIjXjs75dz+809idMbZPIoUPsbepq9a0V 4CVfiPrbleHn6E00o0B8csm10837QalDPkBWnaYBNWHykmUTSKAfDtTs8wZ0uAXK GuJPNY2OlLPw5NOid9Utm2TzkEVHIlrjmToBbwCKxG876gqdrxwXAbJc0YilcZ6+ qevA8ykY/yKq9ypNa2EYkz7pfCmTIPibX1jLujlG3LFdyd6s+u2hYFPhMfT3v3HV 6xlC5FqT29dy5BUlmZPVvWoBQ3/2buKg75nXZuGYcrOfTMvO3JvqR+QcjEIs8Gmb IZ4SYQTtVYMso4ollOU2A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrleeigdefvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvvefukfggtggugfgjsehtkeertddttdejnecuhfhrohhmpeetlhhvrghr ohcujfgvrhhrvghrrgcuoegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhgqe enucggtffrrghtthgvrhhnpefhkeetvdevtefhgffhtdegfeetkeeufefhheetheehtddv udffkeefheehtedtvdenucffohhmrghinhepphhoshhtghhrvghsqhhlrdgvuhdpvghnth gvrhhprhhishgvuggsrdgtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghm pehmrghilhhfrhhomheprghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 21 Mar 2024 07:47:52 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=alvh.no-ip.org; s=schmee; t=1711021669; bh=ElYVd/qNFaiYWWW6UcfA6oYWYdbTWxGHF9cEV4/767Y=; h=Date:From:To:Cc:Subject:In-Reply-To:From; b=LeIg6HZ+EmhHF+jKVbtAUWakmI906e4175C8i6JTGlyz69TWJdgYUhkZlP3GpxlK7 j1k+zNNMhJaVKI/LnL314I+qHqexMiG2XTcIkyxKdXDzK/x/YlJfSNzFqik/aEbK+Y sULO/NsQPcKbk8X1ATGdH6G7JyFoO6vxucs98qRFeK6nyRp6FizpyAnYJfRgcYWrwW 9McX/3ukDk4Ac/RDCJBAC1rRWPX2yq9vITKXAKbfSWTi6Ycy8rdjXUmiVSyPAPb+BC yTjz6TwzntMejjiju38eudQLknIm6QEG25UeAGTM8ggdG8FNkAfL2b72BWpdMe8PF7 us4wysw1NzNaA== Received: by schmee.alvh.no-ip.org (Postfix, from userid 1000) id 9ABD83C9; Thu, 21 Mar 2024 12:47:49 +0100 (CET) Date: Thu, 21 Mar 2024 12:47:49 +0100 From: Alvaro Herrera To: Joseph Kennedy Cc: pgsql-general@postgresql.org Subject: Re: Question about PostgreSQL upgrade from version 12 to version 15 Message-ID: <202403211147.emwyiyb5t6wx@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <32E46CD1-EAD1-4F03-98E2-B842673D547F@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2024-Mar-21, Joseph Kennedy wrote: > I'm planning to upgrade my PostgreSQL database from version 12 to > version 15 using pg_upgrade. After completing the upgrade process, I'm > curious to know whether it's necessary to reindex the database. > > Could anyone please clarify whether reindexing is required after > completing the upgrade process from PostgreSQL version 12 to version > 15 using pg_upgrade? A reindex(*) is necessary for indexes on textual columns(**), and only if you're also changing the underlying OS version(***) such that the collation behavior changes. If you're keeping the database on the same OS version, there's no need to reindex anything. (*) More than reindex actually: you may need to refresh materialized views and consider carefully any partition bounds you may have, if you have any partition keys that include textual columns. Even worse: if you have FDWs on a Postgres server that queries a table from another Postgres server with different collation libraries, it could bit you there too. (**) textual column in this case means anything that is affected by collation changes; typically that's things like varchar, text, citext, etc, for which a collation other than "C" is explicit or implied. You don't need to do anything for indexes on numbers, dates, geometries, etc, nor for textual columns where the index is defined with the C collation. (***) the underlying C library changes collation rules rather frequently (especially glibc), because the developers of said libraries don't consider that this has any important, permanent impact (but it does impact indexes for Postgres). Most such changes are innocuous, but from time to time they make changes that wreak havoc. If you're using ICU collations with your Postgres 12 databases, you may also be affected if you upgrade from one ICU version to another. Joe Conway gave a very good presentation on this topic recently: https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/ -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Por suerte hoy explotó el califont porque si no me habría muerto de aburrido" (Papelucho)