public inbox for [email protected]
help / color / mirror / Atom feedUpgrading PG11 to PG17 without dump/restore
4+ messages / 4 participants
[nested] [flat]
* Upgrading PG11 to PG17 without dump/restore
@ 2025-05-01 13:06 Durumdara <[email protected]>
2025-05-01 13:19 ` Re: Upgrading PG11 to PG17 without dump/restore Ron Johnson <[email protected]>
2025-05-01 15:25 ` Re: Upgrading PG11 to PG17 without dump/restore Adrian Klaver <[email protected]>
2025-05-01 22:27 ` Re: Upgrading PG11 to PG17 without dump/restore Justin Clift <[email protected]>
0 siblings, 3 replies; 4+ messages in thread
From: Durumdara @ 2025-05-01 13:06 UTC (permalink / raw)
To: pgsql-general
Hello!
There is a heavily used server, with older debian, and PG11.
The data is more than 1,2 TB.
The PG_Upgrade is not possible because of lesser space and too old debian.
As we see now we have only one way to move this server.
1.) Installing a new server with actual debian.
2.) Installing the newest PG (17) on it.
3.) Stop work on one database. Dump it in the old, restore it in the new
and start the work with that. So we can move them one by one.
But this seems to be very hard, because we need to do this through an
internet connection, and the data is too much.
I have a question about it - is there a better way to do this?
For example we make a new cluster element (a read only slave) with newest
debian/PG, and use it to move the data in the background (replication).
And then we rename it to master. But I don't know if it's possible or not.
Maybe the slaves must be the same version as the master.
The main problem is that debian is too old, and we are afraid to use
PG_Upgrade because of too many version differences (11 < 17).
But maybe you have some good advice, how to do this with less complication.
Users can tolerate short downtimes, but not longer ones.
Thank you!
Best regards
dd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading PG11 to PG17 without dump/restore
2025-05-01 13:06 Upgrading PG11 to PG17 without dump/restore Durumdara <[email protected]>
@ 2025-05-01 13:19 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2025-05-01 13:19 UTC (permalink / raw)
To: pgsql-general
On Thu, May 1, 2025 at 9:06 AM Durumdara <[email protected]> wrote:
> Hello!
>
> There is a heavily used server, with older debian, and PG11.
> The data is more than 1,2 TB.
> The PG_Upgrade is not possible because of lesser space and too old debian.
>
> As we see now we have only one way to move this server.
> 1.) Installing a new server with actual debian.
> 2.) Installing the newest PG (17) on it.
> 3.) Stop work on one database. Dump it in the old, restore it in the new
> and start the work with that. So we can move them one by one.
>
> But this seems to be very hard, because we need to do this through an
> internet connection, and the data is too much.
>
The new server isn't in the same data center?
> I have a question about it - is there a better way to do this?
>
> For example we make a new cluster element (a read only slave) with newest
> debian/PG, and use it to move the data in the background (replication).
> And then we rename it to master. But I don't know if it's possible or not.
> Maybe the slaves must be the same version as the master.
>
> The main problem is that debian is too old, and we are afraid to use
> PG_Upgrade because of too many version differences (11 < 17).
>
The fear is justified, since collation changes might corrupt text indices.
> But maybe you have some good advice, how to do this with less complication.
> Users can tolerate short downtimes, but not longer ones.
>
Logical replication, not physical replication. Works great if there's not
many DDL changes.
Another possibility is COPY TO / COPY FROM in chunks. This will work if
most of the 1.2TB is inserted, and then never updated or deleted. That
way, at cutover, you only need to COPY TO / COPY FROM and dump/restore
(which is really a wrapper around COPY TO / COPY FROM) and "pg_restore
--section=post-data".
You (hopefully) know your system better than we do, so you'd have to decide
which is better.
--
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: Upgrading PG11 to PG17 without dump/restore
2025-05-01 13:06 Upgrading PG11 to PG17 without dump/restore Durumdara <[email protected]>
@ 2025-05-01 15:25 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2025-05-01 15:25 UTC (permalink / raw)
To: Durumdara <[email protected]>; pgsql-general
On 5/1/25 06:06, Durumdara wrote:
> Hello!
>
> There is a heavily used server, with older debian, and PG11.
> The data is more than 1,2 TB.
> The PG_Upgrade is not possible because of lesser space and too old debian.
1) Specify what the old and new versions of Debian are.
2) I don't understand what the following means: "The PG_Upgrade is not
possible because of lesser space ..." .
> But this seems to be very hard, because we need to do this through an
> internet connection, and the data is too much.
An Internet connection can be dial up speed or direct fiber speed, you
will need to be more specific about the connection capabilities.
> Best regards
> dd
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading PG11 to PG17 without dump/restore
2025-05-01 13:06 Upgrading PG11 to PG17 without dump/restore Durumdara <[email protected]>
@ 2025-05-01 22:27 ` Justin Clift <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Justin Clift @ 2025-05-01 22:27 UTC (permalink / raw)
To: Durumdara <[email protected]>; +Cc: pgsql-general
On 2025-05-01 23:06, Durumdara wrote:
<snip>
> The PG_Upgrade is not possible because of lesser space and too old
> debian.
As a data point, pg_upgrade has an option to do an in-place upgrade
which
reuses the vast majority of the data files as they are on disk, rather
than
making a copy.
It's useful for larger databases, and for situations where you don't
have
much space.
BUT, you *do* have backups don't you?
Because stuff *can* go wrong, even though it's not common. ;)
Regards and best wishes,
Justin Clift
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-05-01 22:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-01 13:06 Upgrading PG11 to PG17 without dump/restore Durumdara <[email protected]>
2025-05-01 13:19 ` Ron Johnson <[email protected]>
2025-05-01 15:25 ` Adrian Klaver <[email protected]>
2025-05-01 22:27 ` Justin Clift <[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