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 1tEJpD-006hyk-Js for pgsql-admin@arkaria.postgresql.org; Fri, 22 Nov 2024 02:55:15 +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 1tEJpC-00463A-8v for pgsql-admin@arkaria.postgresql.org; Fri, 22 Nov 2024 02:55:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tEJpB-004632-Rq for pgsql-admin@lists.postgresql.org; Fri, 22 Nov 2024 02:55:13 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tEJp8-003Crn-VP for pgsql-admin@lists.postgresql.org; Fri, 22 Nov 2024 02:55:13 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-2ea5a22d80cso1332050a91.0 for ; Thu, 21 Nov 2024 18:55:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732244108; x=1732848908; 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=Lm7wQP0GnPpQ9ravzdkpeh35ZQG9xUvar/Dg6jK+0Wk=; b=acDfr57no8UO0LfZBEx0kajJlI4WsnBtJ1/D3dsSQmt2YBu+MzCkxVp0Mjws3FXJbT fPjtB5+nHz56iYovppz9Z1Hv2Yg5zXvfn7tZPn57aF/4CroRdUpadvFaU5XuxPc0tj2Q LAHZEfPuCw/13j5sKnpcUVi61ZsRW+RCY7f8lVbcJ16Y3z6R6oXCCLt3hYs/lsuBlNTp EwNiCq1QGn7Xky8/vIhr+uDljSJZFglxtAn8MGoeLVpmzgdwVu3P9cTtQrlvQ2u5Nkgv uWTyQWLAetKK0xZ1/g2H+ZeOs/NUUXhKXYi4JOWhsTz1MK3INj6KNce3RUCBIyE/8vjx 2keA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732244108; x=1732848908; 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=Lm7wQP0GnPpQ9ravzdkpeh35ZQG9xUvar/Dg6jK+0Wk=; b=N6gW0ek2g4omZeNvRzx+iju1pbO5yUMOH6jGuY/dqzJ53LiRFISQ78qv21TSbemfqF kgMvlgwULOLwBta4PGctEfhCuo66YtRyZKx0+bL6B45IBLjyF3260hs5mEC/Utj1AB8c pA1cX+fTZ/EmuTtD8JqX/tGGtz7nzwTlI7av38hh7Yh4Y2EA2vWjrShEPyA0OruHAbTr wVB6NsAA49vdh6uGg9WGvmxNK4Hfz20Mc+ZU0GLSUmZC4q9uA+FPfHVMDI8Ktr0NuMLj C9bfyVGjNCB4H3SNIyCmyk4SIZ0nwC+WN4o70v4+jKqwuh0iGNtO9XnUp/pUrVbRHnSC xbxw== X-Gm-Message-State: AOJu0YzSr0fcvd1nsf2NqMRvC6EWjI8M5aP0fkhr8493JGBzya3NEFJB iW87SJal/xQbMS+hVl7K5yj4N9pf1oc5Snl3f6grjQ/QwfhC+SvoNdYw+41N+9lIVtabXNIWh4a zeUHyNrhZOtpo2X2eaM8w1JYi672WQg== X-Gm-Gg: ASbGncv7EqxE227WKdw3xv+oBuZ+kg57pa1KLE6JzFVnxutJPFuhThuOXcQrrbCU/l5 WZFtAgCdqu6vKvaI1s8gdTyFTljnbfKshhfBOd5NGgVgGQ0gQFDp4824Cgh2p5rqL X-Google-Smtp-Source: AGHT+IE60QZ74oNjqw2I7eNr385jDITdjV2245WLAYpfiSDZStbAreic8TnDvtqQbracPfYbpZ8ooFirL4mOVmqYQXc= X-Received: by 2002:a17:90b:2246:b0:2ea:aa69:1069 with SMTP id 98e67ed59e1d1-2eb0d506071mr1662128a91.0.1732244108048; Thu, 21 Nov 2024 18:55:08 -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: From: Motog Plus Date: Fri, 22 Nov 2024 08:24:56 +0530 Message-ID: Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000b98900062777826b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b98900062777826b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Ron, Thanks for your response l. Logical replication i can't use because of primary key issues. I have multiple DBs, then multiple schemas and then multiple tables, functions etc Will I be able to copy whole cluster including data, roles, globals everything using pg_dump? Also while restoring, can I restore everything, the same structure, in a single go using pg_restore? On Fri, Nov 22, 2024, 00:16 Ron Johnson wrote: > pg_dumpall uses a single thread to generate one big .sql file. If your > database(s) are small enough that generating (even a compressed) .sql fil= e, > copying it then importing it is Good Enough, then that's fine. > > Otherwise, "logical replication" or "multithreaded pg_dump" is what you > want. > > On Thu, Nov 21, 2024 at 1:17=E2=80=AFPM Motog Plus = wrote: > >> 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 issue= s >> 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 = wrote: >>> > >>> > 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 su= ffix >>> 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 ;-) >>> >>> >>> >>> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000b98900062777826b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Ron,

Thanks for your response l.
Logical replication i c= an't use because of primary key issues.
I have m= ultiple DBs, then multiple schemas and then multiple tables, functions etc<= /div>
Will I be able to copy whole cluster including data,= roles, globals everything using pg_dump?
Also while= restoring, can I restore everything, the same structure, in a single go us= ing pg_restore?


On Fri, Nov 22, 2024, 00:16 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
pg_dumpall uses a = single thread to generate one big .sql file.=C2=A0 If your database(s) are = small enough that generating (even a compressed) .sql file, copying it then= importing it is Good Enough, then that's fine.

Otherwise, "logical replication" or "multithreaded pg_dump= " is what you want.

On Thu, Nov 2= 1, 2024 at 1:17=E2=80=AFPM Motog Plus <mplus7535@gmail.com> wrot= e:
Dear Team,
Thank you for your valuabl= e inputs on the PostgreSQL upgrade.
Given the challenges encountered with pg_upgrade in a Kubernetes= environment, we're considering a more traditional approach involving p= g_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:
=C2=A0 =C2= =A0* Connect to the existing PostgreSQL 12 pod.
=C2=A0 =C2=A0* Execute pg_dumpall to create a comple= te database dump.

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

=C2=A03. Restore:
=C2=A0 =C2=A0* Transfer the backup file to the new po= d.
=C2=A0 =C2=A0* Use psq= l utility to restore the database from the dump.

=C2=A04. Verification:
=C2=A0 =C2=A0* Thoroughly test the restored database to ensure data integ= rity and functionality.
<= br>
=C2=A05. Cutover:
=C2=A0 =C2=A0* Once verifica= tion is complete, switch over traffic to the new PostgreSQL 16 pod.
=C2=A0 =C2=A0* Delete the old Po= stgreSQL 12 pod.

Best Regards,
Ramzy

On Wed, Nov 20, 2024, 02:47= Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Nov 19, 2024, a= t 1:40=E2=80=AFPM, Kris Deugau <kdeugau@vianet.ca> wrot= e:
>
> 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/pgupgr= ade.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 ;-)





--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000b98900062777826b--