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 1tEBjz-005nSU-Ib for pgsql-admin@arkaria.postgresql.org; Thu, 21 Nov 2024 18:17:19 +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 1tEBjy-000DC1-2X for pgsql-admin@arkaria.postgresql.org; Thu, 21 Nov 2024 18:17:18 +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 1tEBjx-000DBt-Ix for pgsql-admin@lists.postgresql.org; Thu, 21 Nov 2024 18:17:17 +0000 Received: from mail-pg1-x52d.google.com ([2607:f8b0:4864:20::52d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tEBju-0035hH-RH for pgsql-admin@lists.postgresql.org; Thu, 21 Nov 2024 18:17:16 +0000 Received: by mail-pg1-x52d.google.com with SMTP id 41be03b00d2f7-7ea76a12c32so941356a12.1 for ; Thu, 21 Nov 2024 10:17:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732213034; x=1732817834; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=PW3qqzglyGcAUhjwhoBU10vG6Lw3U0ltBYAH/mD2VnE=; b=exDIoF55Mj1grbL05/ixaWVG2RVw3nvOSbDcB0xeEvmQ2Vwqc0+EIxWzXjmqIN+IOb sk24PKtWZhhNjeCps4d5iHvmOROFjyfVGM+eVVRyWf7BocCklq7O4kgUlBE+grrur8tj BzwQFTLz2rA1Jyn1NlELPiICXFFTDkE81qFvsq8wKItU+Uw8wm5V+cD62wZoPZhwQVU5 Ol3Er0W/nRv3MKUuBhi2e8MViqexISGsgfxBSjl4hLwGWGH3vRb9Ny8vK9nwtOEjTtGH ZE1Ao4X4Woia/RJbwWwBV0JdoJAB/k08Ko69+rJthvECmG1ZN3JAxy2VOR6S9F5qtyAd Teeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732213034; x=1732817834; h=cc: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=PW3qqzglyGcAUhjwhoBU10vG6Lw3U0ltBYAH/mD2VnE=; b=n5+0e/HFk66ysfDojEZvSqe81lFgcd+qC8gKd1rWXCClu34ZDiOkrwmz3Rksp4cMrP //IvlpfLi/qMKR6Ors3XEaqgLHLqymo4Wx+IbIzpGh3R7bKhEE/oPv3RGehn+f0GhFBa fFcRZr2vBforyEFMiQtbn2wN4Fu9ayCyLpdZtrxHzYh62LRKGSIIhQAxj81LxD4UC6HM SaGne0cCtYaXZqwjpPK8t2po085utOpfdKD0A5NqR+949Wr8z7ORVMztyARSLYQ9IPSI XiSYPHDYLmzeja523dDOb5vEbcka80ITlODFrv8QfyFR0Ug6OD2ObVUgySIYKXn061Rx w4Hg== X-Forwarded-Encrypted: i=1; AJvYcCU0TJ+QD0gb3ySpQuJ5mkACVFCiLfOK+dsEYlsJdHiYfsQbqZHzO1tR0ubpEFgoTUln63vWsFI/a+5cCQ==@lists.postgresql.org X-Gm-Message-State: AOJu0YyBQRbZqEBZCHm0jIPe66xvBXSv/5okPNVcRaQvBXBwsb0xAxh9 9cuZdE5vOJGNvr7W7tHSTgg9KrwaGypPlMdFj5+VQpp7H38KAG2yb4OqdZ59S5q2psUNo3X7yMq cu7rX+NqGbWmDnrtAyU4bVhRQI23flg== X-Gm-Gg: ASbGnctkbXbOFTWtRv4Y3ujoxICVR9lUrMkqHNLXhuNVIQ2RkjyFB24k6CWb+E0dLlO e/4cTjTX0N8A0jgV1gJgLl8P/cQajiRZpLOdF+fufugAg+lPqYOla8pLWLwSH99sZ X-Google-Smtp-Source: AGHT+IHMhtfuKbOic3b5Rp3IT1cpp3YaVq5ga/BSqyuElbxpzRhITu8jf46mNDknh7vv7aAIR8uvy0/Yf7A2YYBARWI= X-Received: by 2002:a17:90b:4c88:b0:2ea:7595:21ec with SMTP id 98e67ed59e1d1-2eaca6bc40dmr8514867a91.1.1732213033547; Thu, 21 Nov 2024 10:17:13 -0800 (PST) MIME-Version: 1.0 References: <0b8c1b80-93f5-40a7-b67b-19e58207c12c@cloud.gatewaynet.com> <9724B260-8B0E-4B03-A66A-784F792459C9@elevated-dev.com> <355729f6-74a0-1b5c-a065-bd919507a490@vianet.ca> <39901423-2003-493F-8A5C-12D1B1062F00@elevated-dev.com> <46c1255a-725f-3f0d-a18f-e1a9c20dd36e@vianet.ca> <5720FA08-368D-4A8C-B160-A104D08C829B@elevated-dev.com> In-Reply-To: <5720FA08-368D-4A8C-B160-A104D08C829B@elevated-dev.com> From: Motog Plus Date: Thu, 21 Nov 2024 23:47:01 +0530 Message-ID: Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 To: Scott Ribe , Pgsql-admin Cc: Kris Deugau Content-Type: multipart/alternative; boundary="0000000000008a417b06277046e6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008a417b06277046e6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Dear Team, Thank you for your valuable inputs on the PostgreSQL upgrade. Given the challenges encountered with pg_upgrade in a Kubernetes environment, we're considering a more traditional approach involving pg_dumpall to take backup and then restore the data using psql utility. Can you please advise if this approach will be fine or you see any issues with it? A high level overview of the steps: 1. Backup: * Connect to the existing PostgreSQL 12 pod. * Execute pg_dumpall to create a complete database dump. 2. New Deployment: * Create a new PostgreSQL 16 pod. I think no need to use initidb as it will be autoinitialized . 3. Restore: * Transfer the backup file to the new pod. * Use psql utility to restore the database from the dump. 4. Verification: * Thoroughly test the restored database to ensure data integrity and functionality. 5. Cutover: * Once verification is complete, switch over traffic to the new PostgreSQL 16 pod. * Delete the old PostgreSQL 12 pod. Best Regards, Ramzy On Wed, Nov 20, 2024, 02:47 Scott Ribe wrote: > > On Nov 19, 2024, at 1:40=E2=80=AFPM, Kris Deugau wr= ote: > > > > I stand corrected. I hadn't read the docs on pg_upgrade for quite a > while, but after reading the last section in > https://www.postgresql.org/docs/current/pgupgrade.html: > > > > "If you did not start the new cluster, the old cluster was unmodified > except that, when linking started, a .old suffix was appended to > $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suff= ix > from $PGDATA/global/pg_control; you can then restart the old cluster." > > > > I see what you mean. > > > > There's nothing wrong per se about taking the snapshot before, I was just > saving the potential time of re-running pg_upgrade. Heck, take a snapshot > before *and* after ;-) > > > > --0000000000008a417b06277046e6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Team,
Thank you for your valuable inputs on= the PostgreSQL upgrade.
= Given the challenges encountered with pg_upgrade in a Kubernetes environmen= t, we're considering a more traditional approach involving pg_dumpall t= o take backup and then restore the data using psql utility.
Can you please advise if this approach= will be fine or you see any issues with it?

A high level overview of the steps:

1. Back= up:
=C2=A0 =C2=A0* Connec= t to the existing PostgreSQL 12 pod.
=C2=A0 =C2=A0* Execute pg_dumpall to create a complete database= dump.

=C2=A02. New Deployment:
=C2=A0 =C2=A0* Create a new PostgreSQL= 16 pod.
=C2=A0 =C2=A0I t= hink no need to use initidb as it will be autoinitialized .

=C2=A03. Restore:
=C2=A0 =C2=A0* Transfer the backup file to the new pod.
=C2=A0 =C2=A0* Use psql utility to= restore the database from the dump.

=C2=A04.= Verification:
=C2=A0 =C2= =A0* Thoroughly test the restored database to ensure data integrity and fun= ctionality.

=C2=A05. Cutover:
=C2=A0 =C2=A0* Once verification is co= mplete, switch over traffic to the new PostgreSQL 16 pod.
=C2=A0 =C2=A0* Delete the old PostgreSQL 1= 2 pod.

Best Regards,
Ramzy

<= div dir=3D"ltr" class=3D"gmail_attr">On Wed, Nov 20, 2024, 02:47 Scott Ribe= <scott_ribe@elevated-dev= .com> wrote:
> On Nov 19,= 2024, at 1:40=E2=80=AFPM, Kris Deugau <kdeugau@vianet.ca> wrote:<= br> >
> I stand corrected.=C2=A0 I hadn't read the docs on pg_upgrade for = quite a while, but after reading the last section in https://www.postgresql.org/docs/current/pgupgrade.html:
>
> "If you did not start the new cluster, the old cluster was unmodi= fied except that, when linking started, a .old suffix was appended to $PGDA= TA/global/pg_control. To reuse the old cluster, remove the .old suffix from= $PGDATA/global/pg_control; you can then restart the old cluster."
>
> I see what you mean.
>

There's nothing wrong per se about taking the snapshot before, I was ju= st saving the potential time of re-running pg_upgrade. Heck, take a snapsho= t before *and* after ;-)



--0000000000008a417b06277046e6--