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 1tEKcw-006lRp-Rg for pgsql-admin@arkaria.postgresql.org; Fri, 22 Nov 2024 03:46:39 +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 1tEKcu-004Mz1-0Y for pgsql-admin@arkaria.postgresql.org; Fri, 22 Nov 2024 03:46:36 +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 1tEKct-004Myt-Cu for pgsql-admin@lists.postgresql.org; Fri, 22 Nov 2024 03:46:35 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tEKck-0039ft-S7 for pgsql-admin@lists.postgresql.org; Fri, 22 Nov 2024 03:46:33 +0000 Received: by mail-ot1-x32d.google.com with SMTP id 46e09a7af769-7180a400238so892193a34.0 for ; Thu, 21 Nov 2024 19:46:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732247186; x=1732851986; 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=cZNvY6YNZrBHPHgiGCxu0H47z/71F+7csn/LDUxmRKo=; b=GBufu0tBxdjzdl3H63ia/4AB7JLhkRORZXo4SxcfN4SMp5FMs+0ec+rAAjz4/GV9OL iIiVG0DFIG8IUNJXXieIMynLuX7bTjW+nYbyzcTAuki1bpFNGvYT8+wtwVDV3UYCsiWu JJJPF1X75cu2l9seOmaoxadKPeJjn8/ZXhQytr22V971hi5FWT5TtyeJcgNviZezDg2n gyo+/mDoQpwiocWGfziGCMXuqZELJOt5C0AGA7D9GazGd9fPm1m4PXGXB6dj4h6IebnJ ccw+kl2K14XBJ9Ze0tXWE62lsMMWsdnGq80QZiViYB+twGtDhnSQ4LEKC85nQG1Sj5Zi G7EQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732247186; x=1732851986; 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=cZNvY6YNZrBHPHgiGCxu0H47z/71F+7csn/LDUxmRKo=; b=t5zbqVbMsanu2TbdHh8BNeYnpna9YCY1KzZXX/tAJjNU1ErppOQXb++5R0hPcVSZhy p/xD1Bfe4jPUX4XHFz1KfxKuz74O657uh6fmA2WDf9yXdFph+lfOZcgsfCFb1Bb/ifnM y4DPMrjRp3YDIXPKnaU8XCuX7Y87qGCwnKnItAsXQn5UQskdFrdl1q9Kx+akEgSMHswe 40642VNCUu7+coADq6za6x5dM7TZ1mj7Vlhv7lvAsvTh6BstBL7vL+ftvn72DArcFjmA ge11XgpX8jZ5ZhfUkZkeWlGQKfPIwf2/9yjrfZXKg+1jaxdvyAAgk2XhWNxsx5+2nMME dNfg== X-Gm-Message-State: AOJu0Yzp7MzchcSLb4pJw7FF2MIh9h+vApxbnE9FIz0I63kyoFpPugAm 0tndKa/SAtGXVW0i5+rFkCKtPFqVnjU9AbH06mOalrE7zpJaabclU0Ur8KUUhhHsrW6S5dJ6jPs j98VyESvrMSle6FWvSzye5mRfZn3hebVb X-Gm-Gg: ASbGncvghQMTmv+2zXpsV4P+cjF0OdQeIm/zIY8shN7zWRR9plGMk+wbVWadmdSVHTy PAL6NpvMk6C0TL4k3hBYDWfwQOqcFjgIv X-Google-Smtp-Source: AGHT+IFpePadoqf29U0mnROzzDrGYgzv7+FX0gp5u+ZYTKjWYLVlnVdR/wMDO6b16xdRoLIoLWGA4aUqTf0t1cQU3FM= X-Received: by 2002:a05:6830:6e0a:b0:718:9ad9:3082 with SMTP id 46e09a7af769-71c03b23ef7mr1049757a34.5.1732247185648; Thu, 21 Nov 2024 19:46:25 -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 22:46:14 -0500 Message-ID: Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 To: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000029f6650627783a4a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000029f6650627783a4a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable pg_dump and pg_restore only work on a single database, but they contain *EVERYTHING* that database needs. Thus, you'll have to do a pg_dump/pg_restore pair If tasked with migrating multiple databases, and LR was not an option, I would: On the new server, 1. "pg_dumpall -h $OldServer --globals > globals.sql" to get the roles, etc. Will need to remove the "postgres" role. 2. "pg_dump -h $OldServer -j$Threads -Fd $DB ..." for each database. 3. psql postgres -af globals.sql 4. "pg_restore -v -j $Threads --exit-on-error -cC -Fd --no-tablespaces -d postgres $DB" for each database. 5. vacuumdb --analyze --jobs=3D$(nproc) -d $DB Do all the pg_dump and then all the pg_restore commands, or alternate pg_dump/pg_restore pairs, one database at a time. That's up to you. I would set these config params before each Step4: pg_ctl restart -wt9999 -mfast \ -o "-c hba_file=3D$PGDATA/pg_hba_maintmode.conf" \ -o "-c fsync=3Doff" \ -o "-c log_statement=3Dnone" \ -o "-c log_temp_files=3D100kB" \ -o "-c log_checkpoints=3Don" \ -o "-c log_min_duration_statement=3D120000" \ -o "-c shared_buffers=3D${SharedBuffs}GB" \ -o "-c maintenance_work_mem=3D${MaintMem}GB" \ -o "-c synchronous_commit=3Doff" \ -o "-c archive_mode=3Doff" \ -o "-c full_page_writes=3Doff" \ -o "-c checkpoint_timeout=3D${CheckPoint}min" \ -o "-c max_wal_size=3D${MaxWalSize}GB" \ -o "-c wal_level=3Dminimal" \ -o "-c max_wal_senders=3D0" \ -o "-c wal_buffers=3D${WalBuffs}MB" \ -o "-c autovacuum=3Doff" And then this after Step 5: pg_ctl -wt9999 stop -mfast && pg_ctl -wt9999 start Be careful with what you set ${SharedBuffs} and ${MaintMem} to: with lots of threads, it's easy to run out of memory, and then the oom will kill the pg_restore. On Thu, Nov 21, 2024 at 9:55=E2=80=AFPM Motog Plus wr= ote: > 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 fi= le, >> 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 >>> 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 = 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 unmodifie= d >>>> except that, when linking started, a .old suffix was appended to >>>> $PGDATA/global/pg_control. To reuse the old cluster, remove the .old s= uffix >>>> 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 ;-) >>>> >>> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000029f6650627783a4a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
pg_dump and pg_restore only work on a single database= , but they contain EVERYTHING=C2=A0that database needs.=C2=A0 Thus, = you'll have to do a pg_dump/pg_restore pair=C2=A0

<= div>If tasked with migrating multiple databases, and LR was not an option, = I would:

On the new server,=C2=A0
1. &qu= ot;pg_dumpall -h $OldServer --globals > globals.sql" to get the rol= es, etc.=C2=A0 Will need to remove the "postgres" role.
2. "pg_dump=C2=A0-h $OldServer=C2=A0-j$Threads -Fd $DB ..." for = each database.
3. psql postgres -af globals.sql
4.= "pg_restore -v -j $Threads --exit-on-error -cC -Fd --no-tablespaces -= d postgres $DB" for each database.
5. vacuumdb --analyze --j= obs=3D$(nproc) -d $DB

Do all the pg_dump and= then all the pg_restore commands, or alternate pg_dump/pg_restore pairs, o= ne database at a time.=C2=A0 That's up to you.

I would set these config params before each Step4:
pg_ctl restart -wt9999 -mfast \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = -o "-c hba_file=3D$PGDATA/pg_hba_maintmode.conf" \
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff" \
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 -o "-c log_statement=3Dnone" \
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 -o "-c log_temp_files=3D100kB" \
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 -o "-c log_checkpoints=3Don" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0= -o "-c log_min_duration_statement=3D120000" \
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 -o "-c shared_buffers=3D${SharedBuffs}GB" \
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance_work_mem=3D${MaintMem}GB&q= uot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3Doff&= quot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff"= \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Doff" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D${CheckPo= int}min" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D$= {MaxWalSize}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_level= =3Dminimal" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_sende= rs=3D0" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_buffers=3D${W= alBuffs}MB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c autovacuum=3D= off"=C2=A0

And then this after Step 5:=
pg_ctl -wt9999 stop -mfast && p= g_ctl -wt9999 start

Be careful with what yo= u set=C2=A0${SharedBuffs} and=C2=A0${MaintMem} to: with lots o= f threads, it's easy to run out of memory, and then the oom will kill t= he pg_restore.

On Thu, Nov 21, 2024 at 9:55=E2=80=AFPM Mot= og Plus <mplus7535@gmail.com&= gt; wrote:
Hi Ron,

Than= ks for your response l.
Logical replication i can= 9;t use because of primary key issues.
I have multip= le DBs, then multiple schemas and then multiple tables, functions etc
=
Will I be able to copy whole cluster including data, role= s, globals everything using pg_dump?
Also while rest= oring, can I restore everything, the same structure, in a single go using p= g_restore?


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

Otherwise, "logical replication&qu= ot; or "multithreaded pg_dump" is what you want.

On Thu, Nov 21, 2024 at 1:17=E2=80=AFPM Motog Plus <= ;mplus7535@gmail.com> wrote:
Dear Team,
Thank you for your valuable inputs on the PostgreSQL upgrade.
Given the challenges encounte= red with pg_upgrade in a Kubernetes environment, we're considering a mo= re traditional approach involving pg_dumpall to take backup and then restor= e the data using psql utility.
Can you please advise if this approach will be fine or you see any is= sues with it?

<= div dir=3D"auto" style=3D"font-size:12.8px">A high level overview of the st= eps:

1. Backup:
=C2=A0 =C2=A0* Connect to the existing PostgreSQL 12= pod.
=C2=A0 =C2=A0* Exec= ute 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 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 pod.
=C2=A0 =C2=A0* Use psql utility to restore the database from the du= mp.

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

=C2=A05. Cutover:
=C2=A0 =C2=A0* Once verification is complete, switch over traffic to the = new PostgreSQL 16 pod.
= =C2=A0 =C2=A0* Delete the old PostgreSQL 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/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> lobster!
--00000000000029f6650627783a4a--