public inbox for [email protected]
help / color / mirror / Atom feedGuidance Needed for PostgreSQL Upgrade from 12 to 15
14+ messages / 5 participants
[nested] [flat]
* Guidance Needed for PostgreSQL Upgrade from 12 to 15
@ 2024-11-18 16:08 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Motog Plus @ 2024-11-18 16:08 UTC (permalink / raw)
To: [email protected]
Dear Team,
We are in the process of upgrading our PostgreSQL database from version 12
to version 15. I would greatly appreciate your advice on the following:
1. Would it be recommended to use the --link option with the pg_upgrade
command, from disk space point of view? Are there any potential downsides
to consider?
2. Do you have any suggestions or best practices for performing an
efficient upgrade in a replication setup (streaming replication) for both
the primary and standby servers?
Thank you in advance for your insights and guidance.
Best regards,
Raman
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
@ 2024-11-18 17:11 ` Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2024-11-18 17:11 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <[email protected]> wrote:
> Dear Team,
>
> We are in the process of upgrading our PostgreSQL database from version 12
> to version 15. I would greatly appreciate your advice on the following:
>
> 1. Would it be recommended to use the --link option with the pg_upgrade
> command, from disk space point of view?
>
Yes.
> Are there any potential downsides to consider?
>
Very much: it's a one-way journey. No rolling back if something goes wrong!
> 2. Do you have any suggestions or best practices for performing an
> efficient upgrade in a replication setup (streaming replication) for both
> the primary and standby servers?
>
Streaming replication is not an upgrade method. Logical replication is
absolutely a valid method of upgrading PG on one server to PG on a
different server.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
@ 2024-11-18 17:19 ` Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Motog Plus @ 2024-11-18 17:19 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Thanks Ron for your suggestion.
So for pg upgrade in streaming replication setup, rsync will be a better
option for standby upgrade if we are using --link option or recreating the
standby, once upgrade completes on primary? Any suggestions on this please.
Best Regards,
Raman
On Mon, Nov 18, 2024, 22:41 Ron Johnson <[email protected]> wrote:
> On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <[email protected]> wrote:
>
>> Dear Team,
>>
>> We are in the process of upgrading our PostgreSQL database from version
>> 12 to version 15. I would greatly appreciate your advice on the following:
>>
>> 1. Would it be recommended to use the --link option with the pg_upgrade
>> command, from disk space point of view?
>>
>
> Yes.
>
>
>> Are there any potential downsides to consider?
>>
>
> Very much: it's a one-way journey. No rolling back if something
> goes wrong!
>
>
>> 2. Do you have any suggestions or best practices for performing an
>> efficient upgrade in a replication setup (streaming replication) for both
>> the primary and standby servers?
>>
>
> Streaming replication is not an upgrade method. Logical replication is
> absolutely a valid method of upgrading PG on one server to PG on a
> different server.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
@ 2024-11-18 17:26 ` Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2024-11-18 17:26 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
No. Neither rsync nor streaming replication will do version upgrades.
The only ways to do version upgrade are:
1. pg_dump + pg_restore
2. pg_upgrade
3. Logical replication
On Mon, Nov 18, 2024 at 12:20 PM Motog Plus <[email protected]> wrote:
> Thanks Ron for your suggestion.
> So for pg upgrade in streaming replication setup, rsync will be a better
> option for standby upgrade if we are using --link option or recreating the
> standby, once upgrade completes on primary? Any suggestions on this please.
>
> Best Regards,
> Raman
>
>
> On Mon, Nov 18, 2024, 22:41 Ron Johnson <[email protected]> wrote:
>
>> On Mon, Nov 18, 2024 at 11:08 AM Motog Plus <[email protected]> wrote:
>>
>>> Dear Team,
>>>
>>> We are in the process of upgrading our PostgreSQL database from version
>>> 12 to version 15. I would greatly appreciate your advice on the following:
>>>
>>> 1. Would it be recommended to use the --link option with the pg_upgrade
>>> command, from disk space point of view?
>>>
>>
>> Yes.
>>
>>
>>> Are there any potential downsides to consider?
>>>
>>
>> Very much: it's a one-way journey. No rolling back if something
>> goes wrong!
>>
>>
>>> 2. Do you have any suggestions or best practices for performing an
>>> efficient upgrade in a replication setup (streaming replication) for both
>>> the primary and standby servers?
>>>
>>
>> Streaming replication is not an upgrade method. Logical replication is
>> absolutely a valid method of upgrading PG on one server to PG on a
>> different server.
>>
>> --
>> 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] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
@ 2024-11-18 19:10 ` Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Achilleas Mantzios @ 2024-11-18 19:10 UTC (permalink / raw)
To: [email protected]
Στις 18/11/24 19:26, ο/η Ron Johnson έγραψε:
> No. Neither rsync nor streaming replication will do version upgrades.
>
> The only ways to do version upgrade are:
> 1. pg_dump + pg_restore
> 2. pg_upgrade
> 3. Logical replication
>
I'd like to add if someone can tolerate some seconds of downtime, with
logical replication it is possible to swap the roles of publisher /
subscriber so that one can keep the old database as a means of extra
safety. If his/her app breaks beyond repair and there is no time for
fixing, one can simply go back to the old DB with some minor adjustments
(such as sequences).
> On Mon, Nov 18, 2024 at 12:20 PM Motog Plus <[email protected]> wrote:
>
> Thanks Ron for your suggestion.
> So for pg upgrade in streaming replication setup, rsync will be a
> better option for standby upgrade if we are using --link option or
> recreating the standby, once upgrade completes on primary? Any
> suggestions on this please.
>
> Best Regards,
> Raman
>
>
> On Mon, Nov 18, 2024, 22:41 Ron Johnson <[email protected]>
> wrote:
>
> On Mon, Nov 18, 2024 at 11:08 AM Motog Plus
> <[email protected]> wrote:
>
> Dear Team,
>
> We are in the process of upgrading our PostgreSQL database
> from version 12 to version 15. I would greatly appreciate
> your advice on the following:
>
> 1. Would it be recommended to use the --link option with
> the pg_upgrade command, from disk space point of view?
>
>
> Yes.
>
> Are there any potential downsides to consider?
>
>
> Very much: it's a one-way journey. No rolling back if
> something goes wrong!
>
> 2. Do you have any suggestions or best practices for
> performing an efficient upgrade in a replication setup
> (streaming replication) for both the primary and standby
> servers?
>
>
> Streaming replication is not an upgrade method. Logical
> replication is absolutely a valid method of upgrading PG on
> one server to PG on a different server.
> --
> 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] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
@ 2024-11-18 19:20 ` Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2024-11-18 19:20 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; +Cc: [email protected]
> On Nov 18, 2024, at 12:10 PM, Achilleas Mantzios <[email protected]> wrote:
>
>
> I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the roles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences).
In the same vein, if one's file system offers atomic snapshots:
1) shut down PG
2) run pg_upgrade with the hard links option
3) take snapshot
4) start up new PG
Now, if there's a failure, you can go back to the snapshot and either start up the old PG, or do some troubleshooting and try upgrading again.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
@ 2024-11-19 17:32 ` Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Kris Deugau @ 2024-11-19 17:32 UTC (permalink / raw)
To: [email protected]
Scott Ribe wrote:
>> On Nov 18, 2024, at 12:10 PM, Achilleas Mantzios <[email protected]> wrote:
>>
>>
>> I'd like to add if someone can tolerate some seconds of downtime, with logical replication it is possible to swap the roles of publisher / subscriber so that one can keep the old database as a means of extra safety. If his/her app breaks beyond repair and there is no time for fixing, one can simply go back to the old DB with some minor adjustments (such as sequences).
>
> In the same vein, if one's file system offers atomic snapshots:
>
> 1) shut down PG
> 2) run pg_upgrade with the hard links option
> 3) take snapshot
> 4) start up new PG
>
> Now, if there's a failure, you can go back to the snapshot and either start up the old PG, or do some troubleshooting and try upgrading again.
Er... I would think that you'd want to do:
1) shut down PG
2) take snapshot
3) run pg_upgrade with the hard links option
4) start up new PG
as otherwise the old PG won't start properly, due to the changes made by
pg_upgrade.
-kgd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
@ 2024-11-19 17:42 ` Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2024-11-19 17:42 UTC (permalink / raw)
To: Kris Deugau <[email protected]>; +Cc: [email protected]
> On Nov 19, 2024, at 10:32 AM, Kris Deugau <[email protected]> wrote:
>
> as otherwise the old PG won't start properly, due to the changes made by pg_upgrade.
Not true, pg_upgrade leaves it in a state where either can be started. By taking the snapshot after, if you roll back to it, you can attempt changes on either the old or new. (Taking care to take additional snapshots as needed, to preserve the ability to roll back to this state.)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
@ 2024-11-19 20:40 ` Kris Deugau <[email protected]>
2024-11-19 21:16 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Kris Deugau @ 2024-11-19 20:40 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; +Cc: [email protected]
Scott Ribe wrote:
>> On Nov 19, 2024, at 10:32 AM, Kris Deugau <[email protected]> wrote:
>>
>> as otherwise the old PG won't start properly, due to the changes made by pg_upgrade.
>
> Not true, pg_upgrade leaves it in a state where either can be started. By taking the snapshot after, if you roll back to it, you can attempt changes on either the old or new. (Taking care to take additional snapshots as needed, to preserve the ability to roll back to this state.)
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
suffix from $PGDATA/global/pg_control; you can then restart the old
cluster."
I see what you mean.
-kgd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
@ 2024-11-19 21:16 ` Scott Ribe <[email protected]>
2024-11-21 18:17 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2024-11-19 21:16 UTC (permalink / raw)
To: Kris Deugau <[email protected]>; +Cc: [email protected]
> On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> 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 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 just saving the potential time of re-running pg_upgrade. Heck, take a snapshot before *and* after ;-)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 21:16 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
@ 2024-11-21 18:17 ` Motog Plus <[email protected]>
2024-11-21 18:45 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Motog Plus @ 2024-11-21 18:17 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; Pgsql-admin <[email protected]>; +Cc: Kris Deugau <[email protected]>
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 <[email protected]> wrote:
> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> 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 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 just
> saving the potential time of re-running pg_upgrade. Heck, take a snapshot
> before *and* after ;-)
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 21:16 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-21 18:17 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
@ 2024-11-21 18:45 ` Ron Johnson <[email protected]>
2024-11-22 02:54 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2024-11-21 18:45 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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 PM Motog Plus <[email protected]> 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 <[email protected]>
> wrote:
>
>> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> 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 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 just
>> saving the potential time of re-running pg_upgrade. Heck, take a snapshot
>> before *and* after ;-)
>>
>>
>>
>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 21:16 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-21 18:17 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-21 18:45 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
@ 2024-11-22 02:54 ` Motog Plus <[email protected]>
2024-11-22 03:46 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Motog Plus @ 2024-11-22 02:54 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 <[email protected]> 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 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 PM Motog Plus <[email protected]> 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 <[email protected]>
>> wrote:
>>
>>> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> 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 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
>>> just saving the potential time of re-running pg_upgrade. Heck, take a
>>> snapshot before *and* after ;-)
>>>
>>>
>>>
>>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 17:19 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:26 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-18 19:10 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 17:32 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 17:42 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-19 20:40 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Kris Deugau <[email protected]>
2024-11-19 21:16 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Scott Ribe <[email protected]>
2024-11-21 18:17 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-21 18:45 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Ron Johnson <[email protected]>
2024-11-22 02:54 ` Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
@ 2024-11-22 03:46 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Ron Johnson @ 2024-11-22 03:46 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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=$(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=$PGDATA/pg_hba_maintmode.conf" \
-o "-c fsync=off" \
-o "-c log_statement=none" \
-o "-c log_temp_files=100kB" \
-o "-c log_checkpoints=on" \
-o "-c log_min_duration_statement=120000" \
-o "-c shared_buffers=${SharedBuffs}GB" \
-o "-c maintenance_work_mem=${MaintMem}GB" \
-o "-c synchronous_commit=off" \
-o "-c archive_mode=off" \
-o "-c full_page_writes=off" \
-o "-c checkpoint_timeout=${CheckPoint}min" \
-o "-c max_wal_size=${MaxWalSize}GB" \
-o "-c wal_level=minimal" \
-o "-c max_wal_senders=0" \
-o "-c wal_buffers=${WalBuffs}MB" \
-o "-c autovacuum=off"
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 PM Motog Plus <[email protected]> wrote:
> 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 <[email protected]> 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 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 PM Motog Plus <[email protected]> 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 <[email protected]>
>>> wrote:
>>>
>>>> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> 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 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
>>>> just saving the potential time of re-running pg_upgrade. Heck, take a
>>>> snapshot before *and* after ;-)
>>>>
>>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-11-22 03:46 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-18 16:08 Guidance Needed for PostgreSQL Upgrade from 12 to 15 Motog Plus <[email protected]>
2024-11-18 17:11 ` Ron Johnson <[email protected]>
2024-11-18 17:19 ` Motog Plus <[email protected]>
2024-11-18 17:26 ` Ron Johnson <[email protected]>
2024-11-18 19:10 ` Achilleas Mantzios <[email protected]>
2024-11-18 19:20 ` Scott Ribe <[email protected]>
2024-11-19 17:32 ` Kris Deugau <[email protected]>
2024-11-19 17:42 ` Scott Ribe <[email protected]>
2024-11-19 20:40 ` Kris Deugau <[email protected]>
2024-11-19 21:16 ` Scott Ribe <[email protected]>
2024-11-21 18:17 ` Motog Plus <[email protected]>
2024-11-21 18:45 ` Ron Johnson <[email protected]>
2024-11-22 02:54 ` Motog Plus <[email protected]>
2024-11-22 03:46 ` 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