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 1tNZAq-000euo-Ad for pgsql-admin@arkaria.postgresql.org; Tue, 17 Dec 2024 15:07:48 +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 1tNZAp-002akg-I0 for pgsql-admin@arkaria.postgresql.org; Tue, 17 Dec 2024 15:07:47 +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 1tNZAp-002akY-4e for pgsql-admin@lists.postgresql.org; Tue, 17 Dec 2024 15:07:46 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tNZAl-000Cxi-H2 for pgsql-admin@lists.postgresql.org; Tue, 17 Dec 2024 15:07:46 +0000 Received: by mail-oi1-x236.google.com with SMTP id 5614622812f47-3eb8db8ae9aso2420617b6e.1 for ; Tue, 17 Dec 2024 07:07:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734448061; x=1735052861; 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=w1h+nMCoTX+jAfxOK3KYbMyaKpSbNPm28CAyDLvSOYA=; b=Ab7QvR6ME6+fcEQ2ZpA7QAZ6hPoEQ/WXUFggz+WNrJjW65IBd5Cr1zP0Ja6lB4dk2Q t235yHnmUr7QLpSdQgjmrwK+ue6scQyi1//cwUDiBA63qxdn94fwuT207hpoFxQch7qG Uu7ZzH63d6fEXVtaWVkhiHlQxohrC+XGcY+HZnuA9OejA17jTDpa/IEDE1+2x3KZif4T 5MeiTCLKelLdGU3VQplWQE0La4xdIiyEH71G78xtR/8GpMK7nRtlfr95tMvLDk+ZY+Zf hR6VSV67R9ueNJkkax49UmNZTokJI3bPxZ7izhW2zo7yvKnIaJgi1BJrssXuC6SPxto4 DfRA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734448061; x=1735052861; 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=w1h+nMCoTX+jAfxOK3KYbMyaKpSbNPm28CAyDLvSOYA=; b=lHTGZKPxAcoLwcVjnkwc3h1Syf35K0Q3M/ka7TyR4fvvFFQ21gBJRLC1nyVNZO1R1w fZYYTFd3EhKi7NlXUYqFW6ezoQtmLYwO8DWMhCNL0HDrCpukda2lYsBgVOSYVMY5mcRv dgDDOCA3hGs1eYxrdIx/Pn2ijsl9T6rFUy0cxKBjJ2BpHl/VTh+nOXi++lsOJ8ICOgg6 h66+1gKrTDdrczJOjMtn7PnVrTCiXAbv89q3g2xVht1kKkL1ch0ZRRoTf1smqkU8TwGZ L1x5y5UgeKARcOPKhEzyZhW6qzPq8bxhvlnmN2v3rIf5Eflzd2ZZSTfyZrZkjxbJzUmM 4sGQ== X-Gm-Message-State: AOJu0Yx0Y3KiKB6Q+n2CywmtHRpztR4SpyPof4E20XnnTBwCsfSP+mV8 Oi/XOmUJfvkpBII1NWvysI4HG9o68YTxkFzbOd1LLPy5NZgcL9tfH1+33jUhJGkrAMxx848Gbfz t9bvLKTP4i2/K5nnc3Pup0UoBG0v/Be9a X-Gm-Gg: ASbGnct23OCqMNAy/oArJAu9fwTHE+4yw/hHFseUqcc1AAh10abmIgPdCmAcKFLu4ak MDfZd5MkZs4BJcxR2dEKIIel21YNZGYpBGMJ4wSFcvUSUwqHP/3sd57JC+ehFc4sGk5tzDd7j X-Google-Smtp-Source: AGHT+IEQ7vfeDSv48JI/1TPWrakhKcALkZLL5fo7205XcckUZmdh7WpfrqbeVGItEfMLkTmJhvJsKRrY2QR++P+C7Hw= X-Received: by 2002:a05:6870:568c:b0:29f:c5f3:a827 with SMTP id 586e51a60fabf-2a3ac8be212mr9704436fac.35.1734448061392; Tue, 17 Dec 2024 07:07:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 17 Dec 2024 10:07:30 -0500 Message-ID: Subject: Re: Postgres upgradation To: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000094ad60062978a805" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000094ad60062978a805 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I don't understand the obsession with pg_dumpall. Sure, you use "pg_dumpall --global" to get roles, tablespace definitions, etc, but pg_dump/pg_restore is so much faster than pg_dumpall|pgsql for anything beyond the trivial. For those multiple databases... run pg_dump multiple times. export PGHOST=3Doldserver pg_dumpall -gvf globals.sql 2> /dev/null DbList=3D`psql -AXtc "select datname from pg_database where datistemplate=3Dfalse and datname <> 'postgres' order by datname ;"` cd $BackupDir for DB in $DbList; do date +"%n%F %T Backup of $DB started to $BackupDir with compression level ${ZLvl}%n." pg_dump -j ${Threads} -Z${ZLvl} -v -C -Fd --file=3D$DB $DB 2> ${DB}_pgdump.log done On Tue, Dec 17, 2024 at 9:52=E2=80=AFAM Motog Plus wr= ote: > Hi Ron, > > Is it advisable to use pg_dumpall to take backup and psql utility to > restore so that along with data(multiple DBs) roles, tablespaces if any a= re > also copied and then restored. > > On Tue, Dec 17, 2024, 20:11 Ron Johnson wrote: > >> On Tue, Dec 17, 2024 at 5:21=E2=80=AFAM Narendran .j >> wrote: >> >>> Hello All, >>> >>> In our environment, PostgreSQL is currently running on lower versions, >>> ranging from 9 to 11. >>> >> >> PG 9.x is presumably running on an EOL version of the OS, meaning you'll >> migrate to a current version of the OS. That means you'll use >> pg_dump/pg_restore OR Logical Replication, instead of pg_upgrade. >> >> You should probably do the same on the server running PG 11. >> >> >>> We are planning to upgrade to the latest version, and I have a few >>> questions regarding the process. >>> >>> What are the key considerations or checks we need to perform before the >>> upgrade? >>> >>> Can we directly upgrade from version 9 to 15, or is a step-by-step >>> upgrade required? >>> >> >> Read https://www.postgresql.org/docs/current/app-pgdump.html and >> https://www.postgresql.org/docs/current/app-pgrestore.html >> >> >> >> >>> How should we handle extensions during the upgrade process? >>> >> >> Depends on the extension, and how you install Postgresql. Since we >> install from RPM packages, it was as simple as installing the latest >> versions of those packages, and then running pg_restore. >> >> >>> What challenges or issues should we anticipate during or after the >>> upgrade? >>> >> >> You might need to update db drivers like JDBC at the same time. >> >> >>> If anyone with experience in upgrading Postgres, please share your >>> guidance. >>> >> >> Since pg_dump can be performed online, test the process beforehand. I'd >> bet that your app servers are also running on old servers, too. Upgrade >> them, and you'll have a perfect chance to test the system beforehand. >> >> The only hiccup we faced when moving from PG 9.6 to 14 was that the >> updated JDBC drivers on the application servers choked on some strings w= ith >> quotes in them. >> >> YMMV. >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000094ad60062978a805 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I don't understand the obsession with= pg_dumpall.=C2=A0 Sure, you use "pg_dumpall --global" to get rol= es, tablespace definitions, etc, but pg_dump/pg_restore is so much faster t= han pg_dumpall|pgsql for anything beyond the trivial.

For those multiple databases... run pg_dump multiple times.=

export PGHOST=3Doldserve= r
pg_dumpall -gvf globals.sql 2&g= t; /dev/null
DbList=3D`psql -AXtc= "select datname from pg_database
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 where datistemplate=3Dfalse and datn= ame <> 'postgres'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 order by datname ;"`
= cd $BackupDir
for DB in $DbList;
do
=C2=A0 =C2=A0 date +"%n%F %T Backup = of $DB started to $BackupDir with compression level ${ZLvl}%n."
=C2= =A0 =C2=A0 pg_dump -j ${Threads} -Z${ZLvl} -v -C -Fd --file=3D$DB $DB 2>= ${DB}_pgdump.log
done

On Tue, Dec 17, 2= 024 at 9:52=E2=80=AFAM Motog Plus <mplus7535@gmail.com> wrote:
Hi Ron,

<= div dir=3D"auto">Is it advisable to use pg_dumpall to take backup and psql = utility to restore so that along with data(multiple DBs) roles, tablespaces= if any are also copied and then restored.

On Tue, Dec 17, 2024, 20:11= Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Dec 17,= 2024 at 5:21=E2=80=AFAM Narendran .j <jnarendran3@gmail.com> = wrote:
Hello All,

In our environment, PostgreSQL is currently running on lower v= ersions, ranging from 9 to 11.

=
PG 9.x is presumably running on an EOL vers= ion of the OS, meaning you'll migrate to a current version of the OS.= =C2=A0 That means you'll use pg_dump/pg_restore OR Logical Replication,= instead of pg_upgrade.

You should probably do the= same on the server running PG 11.
=C2=A0
We are planning to upgrade to the latest version, and I have a few qu= estions regarding the process.

What are the key considerations or checks we need to perform before = the upgrade?

Can we dire= ctly upgrade from version 9 to 15, or is a step-by-step upgrade required?



=C2=A0
How should we handle extensions during the upgrade process?

Depends on the extension, and how = you install Postgresql.=C2=A0 Since we install from RPM packages, it was as= simple as installing the latest versions of those packages, and then runni= ng pg_restore.
=C2=A0
What challenges or issues s= hould we anticipate during or after the upgrade?

You might need to update db drivers like JDBC at the same= time.
=C2=A0
If anyone with experience in upgrad= ing Postgres, please share your guidance.

=
Since pg_dump can be performed online, test the process beforeha= nd.=C2=A0 I'd bet that your app servers are also running on old servers= , too.=C2=A0 Upgrade them, and you'll have a perfect chance to test the= system beforehand.

The only hiccup we faced when = moving from PG 9.6 to 14 was that the updated JDBC drivers on the applicati= on servers choked on some strings with quotes in them.

=
YMMV.

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


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