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 1tECBl-005rZn-2N for pgsql-admin@arkaria.postgresql.org; Thu, 21 Nov 2024 18:46:01 +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 1tECBi-000MmQ-OW for pgsql-admin@arkaria.postgresql.org; Thu, 21 Nov 2024 18:45:58 +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 1tECBi-000MmE-Aw for pgsql-admin@lists.postgresql.org; Thu, 21 Nov 2024 18:45:58 +0000 Received: from mail-ot1-x330.google.com ([2607:f8b0:4864:20::330]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tECBf-0039Jt-E8 for pgsql-admin@lists.postgresql.org; Thu, 21 Nov 2024 18:45:57 +0000 Received: by mail-ot1-x330.google.com with SMTP id 46e09a7af769-7181caa08a3so665093a34.0 for ; Thu, 21 Nov 2024 10:45:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732214753; x=1732819553; 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=VJbetE3xSXoTjvnkPTD5drIJsIIGGOWRSo+qkfVEPUI=; b=QntLq1eqBg53ZiaHRRgDkn8mHFhYTV3n1cdOHG6i7Hjc9RRqOiqQdnwOzTDr2o+q85 yhVbHx0Rsxxw2h2SRejvVKXjO0KhHWHqe+mzVNViizEBWSVC1eaoshIqLqoPKOqytSPf VkEmzpUmbpnG2fuqcvMEFndSn1NxtO21u5ZqkF5Qunh5aKd+A0c7jPvboTjSj1Jyygwb PMvjYI4vmE19LJ9ybwcCSrATmWWz+dJ4jAdgolEuIYittXtMovYj8Av9C8ik2Waft7ap a/1ok3IBfzo2FENo7C8fXkSCuwlluzRHFgrO/X0EjuypYF8nOI2Koz5Pss5HbEs2/4CY Tp5A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732214753; x=1732819553; 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=VJbetE3xSXoTjvnkPTD5drIJsIIGGOWRSo+qkfVEPUI=; b=pZvH5pkP2izjqZVoIGd/EupPuQEQAGu7dEwdF7O8jfp7f5OgR+OQC9ElUyTNlk68DU jWVhBljijNy6P3oLSLMmSghy7vRr9wczPQf0CiflOcv1/cjJIi9p7EyEH4Hct95npkh5 w67a7xtCSnqpHCGlJgVu4DZAUiDLjU5R03FxWBtYmOO3SQVqqehoPJtPJcNSCNDBK+Fc iGEHfRoz4ta/xVdZnfzbVTTZhuWZnIN4N7M38ioRwWnNK6vxV9MnQVTntstgXTn/6nry IGhDn/y6r3LscEdLVfG8wOetw4yhdTsWn8cqJCN45ebXeyci/5XrrsRpSuESH+AdQJNT iIhQ== X-Gm-Message-State: AOJu0YyNSN5c1dVjGUh3SIqse5PXMmTnAYpw3YkX2b5MOmdD9M7I3u0K S9kyrtzm3ZqgGU4WMDDUi4aTlZ1K6gUTgdxkxGbj2ZkLOvVHd71Ru3y83ZXCy4FfErUAo3wRJ/0 Cm2Nc/KoutLHXjmhnpDZIJyehDZ8Lyw== X-Gm-Gg: ASbGncuniPY+5AU/0DGpCILu1DjROYr/ZIJUB6XHAl1Jx90jBZ+hiifvR8wQmh+t0Zd Az6EsQwEoGLdkMa4mUjnPfjXO0BCzGxM8 X-Google-Smtp-Source: AGHT+IExxfBWpmg+oyRDIo3AqYT6MH/w6GjLJnnG/Wy0hQ5keDzOTxCxOc/T4ElS6H2uxM1sSJ/qwy3mohS9DIQCpes= X-Received: by 2002:a05:6830:40c6:b0:717:f666:9559 with SMTP id 46e09a7af769-71ab30eea4emr9033944a34.9.1732214753559; Thu, 21 Nov 2024 10:45:53 -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: Ron Johnson Date: Thu, 21 Nov 2024 13:45:42 -0500 Message-ID: Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 To: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000000f8c26062770ad1d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000f8c26062770ad1d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 21, 2024 at 1:17=E2=80=AFPM Motog Plus wr= ote: > 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 w= rote: >> > >> > 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 suf= fix >> 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 jus= t >> saving the potential time of re-running pg_upgrade. Heck, take a snapsho= t >> before *and* after ;-) >> >> >> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000000f8c26062770ad1d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 replicat= ion" or "multithreaded pg_dump" is what you want.
=
On Thu, Nov 21, 2024 at 1:17=E2=80=AFPM Motog Pl= us <mplus7535@gmail.com> w= rote:
Dear Team,<= /span>
Thank you for your valua= ble inputs on the PostgreSQL upgrade.
Given the challenges encountered with pg_upgrade in a Kubernet= es 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 th= is 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, at 1:40=E2=80=AFPM, = Kris Deugau <kdeugau@vianet.ca> wrote:
>
> 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 ;-)





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