public inbox for [email protected]
help / color / mirror / Atom feedPostgres upgradation
4+ messages / 3 participants
[nested] [flat]
* Postgres upgradation
@ 2024-12-17 10:21 Narendran .j <[email protected]>
2024-12-17 14:41 ` Re: Postgres upgradation Ron Johnson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Narendran .j @ 2024-12-17 10:21 UTC (permalink / raw)
To: [email protected]
Hello All,
In our environment, PostgreSQL is currently running on lower versions,
ranging from 9 to 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?
How should we handle extensions during the upgrade process?
What challenges or issues should we anticipate during or after the upgrade?
If anyone with experience in upgrading Postgres, please share your guidance.
Regards,
Naren
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres upgradation
2024-12-17 10:21 Postgres upgradation Narendran .j <[email protected]>
@ 2024-12-17 14:41 ` Ron Johnson <[email protected]>
2024-12-17 14:51 ` Re: Postgres upgradation Motog Plus <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2024-12-17 14:41 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Tue, Dec 17, 2024 at 5:21 AM Narendran .j <[email protected]> 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 with quotes
in them.
YMMV.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres upgradation
2024-12-17 10:21 Postgres upgradation Narendran .j <[email protected]>
2024-12-17 14:41 ` Re: Postgres upgradation Ron Johnson <[email protected]>
@ 2024-12-17 14:51 ` Motog Plus <[email protected]>
2024-12-17 15:07 ` Re: Postgres upgradation Ron Johnson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Motog Plus @ 2024-12-17 14:51 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 are
also copied and then restored.
On Tue, Dec 17, 2024, 20:11 Ron Johnson <[email protected]> wrote:
> On Tue, Dec 17, 2024 at 5:21 AM Narendran .j <[email protected]>
> 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 with
> quotes in them.
>
> YMMV.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres upgradation
2024-12-17 10:21 Postgres upgradation Narendran .j <[email protected]>
2024-12-17 14:41 ` Re: Postgres upgradation Ron Johnson <[email protected]>
2024-12-17 14:51 ` Re: Postgres upgradation Motog Plus <[email protected]>
@ 2024-12-17 15:07 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2024-12-17 15:07 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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=oldserver
pg_dumpall -gvf globals.sql 2> /dev/null
DbList=`psql -AXtc "select datname from pg_database
where datistemplate=false 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=$DB $DB 2>
${DB}_pgdump.log
done
On Tue, Dec 17, 2024 at 9:52 AM Motog Plus <[email protected]> wrote:
> 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 are
> also copied and then restored.
>
> On Tue, Dec 17, 2024, 20:11 Ron Johnson <[email protected]> wrote:
>
>> On Tue, Dec 17, 2024 at 5:21 AM Narendran .j <[email protected]>
>> 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 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> lobster!
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-12-17 15:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-17 10:21 Postgres upgradation Narendran .j <[email protected]>
2024-12-17 14:41 ` Ron Johnson <[email protected]>
2024-12-17 14:51 ` Motog Plus <[email protected]>
2024-12-17 15:07 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox