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 1scmyI-00AUNJ-WD for pgsql-admin@arkaria.postgresql.org; Sat, 10 Aug 2024 14:21:31 +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 1scmyH-0095tX-Ir for pgsql-admin@arkaria.postgresql.org; Sat, 10 Aug 2024 14:21:29 +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 1scmyH-0095tP-33 for pgsql-admin@lists.postgresql.org; Sat, 10 Aug 2024 14:21:29 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1scmyE-004371-Gk for pgsql-admin@lists.postgresql.org; Sat, 10 Aug 2024 14:21:27 +0000 Received: by mail-pl1-x629.google.com with SMTP id d9443c01a7336-1fc4c33e746so743565ad.2 for ; Sat, 10 Aug 2024 07:21:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723299685; x=1723904485; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=D80rs4YTEjnceIaCqfuq3Bc6/2eP6xO8/rZcvh1kT3U=; b=QcrFfkBXV+zrwssBaExFFC5aSX9oKwmNiyyzta9HO0qSDg63oUHlCLlhHzEOOV/2AV e7QxZnV3ZaCaHxZrgGgfwuZLGbjxht0Yhvp+Vwm22bd46quSNOM8uXANDMNQCbareuVz dYHOgYNITKT5WnlGdI2ToSln7eJ61bCrm4s5wRIyzeN1vxybRVu/R3pEIAF0dwqItdAk DCG95LGsYGJ06X0ssVrKYT4x3gf0yT0EuJAPTtHT9aHqq8QaJLqxqX/RF5DwYS4TkvEp LeouD+M33TXlRnYZxL8TXxs5at8cQRvHFKRQooCj0gcfn3/eD+vGGu8dKeCfgRGLRp6I tqig== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723299685; x=1723904485; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=D80rs4YTEjnceIaCqfuq3Bc6/2eP6xO8/rZcvh1kT3U=; b=ESYnFrYU6N0BTwCqD23lwZs7BPuDbWWudc3xkaFhZxk4MiU+uTmdmQHqOUtXO8mvCO ZCAkll7aD3vB/JdxxmwESEVldsVF9hA0zXQqaxFvqFmZHnGsdqd1YnulNxPAodZhycF9 aJF/O0pex3FtBA/+JoKbeLfgj9utgAZvLhnZiF2le0uqHjhOT4xVqJtu/KAnDIHMsSvF u8o3P60WxBk2WUop7uThcW6e+3ZtDSxigK+E/sYkXDb4omvi7lHa2we9pBOm4fxo1+1A PTRZz+0dMEc5GBEKMEmuZF4L6gYP+VjjWz5Opc9l4rOZn5QkF12pxMTt2jpkr8qqVGAW sAQQ== X-Forwarded-Encrypted: i=1; AJvYcCVf3CIb65AmwpB1xemq23ak3xYClhotmIK/oQVoLi8m+hcSXn50J52uocl0aJsbM5WGmzn/nL8sFe8gDg==@lists.postgresql.org X-Gm-Message-State: AOJu0YyzzjZpGcDFw4sIZKAsrHiavoZ9H1gFynBWKiCnTSdTWywHBGi+ aSjRDuRPySmsDEHsafFtPYczwVb8gsd1YkcZmdEZbo0AoOBMduayJ75avvbHBr6ujiTANbOXO+P rtrrt+zkBYwHlCki2gufCa5NRy7U= X-Google-Smtp-Source: AGHT+IEPen06tEx0V3fuSeaGTxzvI1BbvHKrFAKhM/jG8JywLyPA/9Eh+zlD56LXljuaYbTcGWe1lWKar8r7mPd8+pA= X-Received: by 2002:a17:902:e80f:b0:1fd:a7b8:edaf with SMTP id d9443c01a7336-200ae61c921mr29914595ad.8.1723299685370; Sat, 10 Aug 2024 07:21:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Motog Plus Date: Sat, 10 Aug 2024 19:51:12 +0530 Message-ID: Subject: Re: Seeking Guidance on Upgrading from PostgreSQL 12 to 1 To: Ron Johnson , pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000969572061f54f9a2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000969572061f54f9a2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you so much, Ron. I=E2=80=99ll go through your suggestions and reach = out if I have any further questions. Regards, Raman On Sat, Aug 10, 2024, 15:55 Ron Johnson wrote: > On Sat, Aug 10, 2024 at 3:37=E2=80=AFAM Motog Plus = wrote: > >> Dear PostgreSQL Admins, >> >> I hope this message finds you well. We are currently running PostgreSQL >> 12 in dev as well as prod environment . However, with the end of support >> for this version scheduled for November 2024, we are considering upgradi= ng >> to a higher version, particularly PostgreSQL 16. >> >> I would greatly appreciate your insights on the following points: >> >> 1. **Upgrading to PostgreSQL 16**: Would upgrading to version 16 be a >> prudent decision, given the advancements and changes since PostgreSQL 12= ? >> Are there any significant concerns or benefits that we should be aware o= f >> before making this decision? >> > > It's the latest, and the 4th release just dropped a few days ago. > > 2. **Pre-Upgrade and Post-Upgrade Checks**: Could you please suggest the >> critical checks we need to perform prior to the upgrade to ensure it is >> successful? >> > > https://www.postgresql.org/docs/16/pgupgrade.html > -c > --check > > check clusters only, don't change any data > >> Additionally, what post-upgrade checks should be conducted to confirm >> everything is functioning as expected? >> > > Comprehensive QA testing never hurts. > > 3. **Upgrade Resources**: Are there any recommended links, videos, or >> other resources that can guide us through the upgrade process, especiall= y >> from version 12 to 16? >> > > https://www.postgresql.org/docs/release/ > https://www.postgresql.org/docs/release/13.0/ > https://www.postgresql.org/docs/release/14.0/ > https://www.postgresql.org/docs/release/15.0/ > https://www.postgresql.org/docs/release/16.0/ > > >> 4. **Application Impact**: If we proceed with the upgrade on the same >> host, could there be any potential changes or impacts on the application >> side that we should be prepared for? >> > > Read the release notes. > > You might also have to upgrade drivers on your application servers. For > example, a change in how JDBC handles strings with single quotes in them > bit us when moving from PG 9.6 to PG 14, and an older JDBC version (don't > know the number) to whatever the latest was a couple of months ago. > > 5. **pg_upgrade Functionality**: When using `pg_upgrade`, does it >> effectively copy all user-defined functions, extensions, sequences, alon= g >> with other database objects like tables, indexes, and schemas? Are there >> any specific objects or elements that `pg_upgrade` does not handle, whic= h >> we should manually address? >> > > https://www.postgresql.org/docs/16/pgupgrade.html > "Major PostgreSQL releases regularly add new features that often change > the layout of the system tables, but the internal data storage format > rarely changes. pg_upgrade uses this fact to perform rapid upgrades by > creating new system tables and simply *reusing the old user data files.*" > > -- > Death to America, and butter sauce. > Iraq lobster! > --000000000000969572061f54f9a2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you so much, Ron. I=E2=80=99ll go through your sugg= estions and reach out if I have any further questions.
Regards,
Raman
<= br>
On Sat,= Aug 10, 2024, 15:55 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Sat, Aug 10, 2024 at 3:37=E2= =80=AFAM Motog Plus <mplus7535@gmail.com> wrote:
Dear PostgreSQL Admins,

I hope this message finds you well. We are = currently running PostgreSQL 12 in dev as well as prod environment . Howeve= r, with the end of support for this version scheduled for November 2024, we= are considering upgrading to a higher version, particularly PostgreSQL 16.=

I would greatly appreci= ate your insights on the following points:

1. **Upgrading to PostgreSQL 16**: Would upgrading to ve= rsion 16 be a prudent decision, given the advancements and changes since Po= stgreSQL 12? Are there any significant concerns or benefits that we should = be aware of before making this decision?

<= /div>
It's the latest, and the 4th release just dropped a few days= =C2=A0ago.

2. **Pre-= Upgrade and Post-Upgrade Checks**: Could you please suggest the critical ch= ecks we need to perform prior to the upgrade to ensure it is successful?
=C2=A0
-c
--check

check clusters only, don't chan= ge any data=C2=A0

Add= itionally, what post-upgrade checks should be conducted to confirm everythi= ng is functioning as expected?

= Comprehensive QA testing never hurts.

3. **U= pgrade Resources**: Are there any recommended links, videos, or other resou= rces that can guide us through the upgrade process, especially from version= 12 to 16?

https://www.postgresql.org/docs/release/15.0/
=
=C2=A0
4. **Application Impact**: If we= proceed with the upgrade on the same host, could there be any potential ch= anges or impacts on the application side that we should be prepared for?
=C2=A0
Read the release notes.=

You might also have to upgrade drivers on your ap= plication servers.=C2=A0 For example, a change in how JDBC handles strings = with single quotes in them bit us when moving from PG 9.6 to PG 14, and an = older JDBC version (don't know the number) to whatever the latest was a= couple of months ago.

5. **pg_upgrade Functionality**: When using `pg_upgrade`, does it effect= ively copy all user-defined functions, extensions, sequences, along with ot= her database objects like tables, indexes, and schemas? Are there any speci= fic objects or elements that `pg_upgrade` does not handle, which we should = manually address?
=
"Major PostgreSQL = releases regularly add new features that often change the layout of the sys= tem tables, but the internal data storage format rarely changes.=C2=A0pg_u= pgrade=C2=A0uses this fact to perform rapid upgr= ades by creating new system tables and simply reusing the old user data = files."
=C2=A0
--
Death to America, and butter sauce.
Iraq lobster!
<= /div>
--000000000000969572061f54f9a2--