Postgres Pro
Downloads
Home   >   mailing lists

Thread: Upgrading postgres quickly, without downtime.

Upgrading postgres quickly, without downtime.

From
Karthik Krishnakumar
Date:
Hi admins,

I have a number of pg servers(~100 (each with a couple of replicas, so 300 clusters in total), each with ~2TB data) that is running version 10.
I am planning on upgrading to PG 15 and looking for ways to get it done without downtime and quickly.
There seem to be a couple of ways to do this -
1. dump/restore or upgrade -> but this causes downtime.
2. logical replication/pglogical -> this is going to be slow, adds additional load on the master, and it isnt recommended to do DDL during time.

I do have daily backups of these databases.
Is there a way I can use these daily backups to upgrade to 15 and sync the changes for the day alone?
I can code in C/scripts, and know bit of internals and wanted to ask where to start on this.
I am also open to any alternate ideas. 

thanks
karthik



Re: Upgrading postgres quickly, without downtime.

From
Laurenz Albe
Date:
On Fri, 2023-02-17 at 15:01 +0530, Karthik Krishnakumar wrote:
> I have a number of pg servers(~100 (each with a couple of replicas, so 300
> clusters in total), each with ~2TB data) that is running version 10.
> I am planning on upgrading to PG 15 and looking for ways to get it done
> without downtime and quickly.
> There seem to be a couple of ways to do this -
> 1. dump/restore or upgrade -> but this causes downtime.
> 2. logical replication/pglogical -> this is going to be slow, adds additional
>    load on the master, and it isnt recommended to do DDL during time.
>
> I do have daily backups of these databases.
> Is there a way I can use these daily backups to upgrade to 15 and sync the changes for the day alone?
> I can code in C/scripts, and know bit of internals and wanted to ask where to start on this.
> I am also open to any alternate ideas. 

Take 10 minutes downtime and use "pg_upgrade --link".

Yours,
Laurenz Albe

Re: Upgrading postgres quickly, without downtime.

From
MichaelDBA
Date:
pg_upgrade --link is the way to go for sure, but pay attention to the details surrounding it for primary and replicas.

Regards,
Michael Vitale

Laurenz Albe wrote on 2/17/2023 7:13 AM:
On Fri, 2023-02-17 at 15:01 +0530, Karthik Krishnakumar wrote:

I have a number of pg servers(~100 (each with a couple of replicas, so 300
clusters in total), each with ~2TB data) that is running version 10.
I am planning on upgrading to PG 15 and looking for ways to get it done
without downtime and quickly. 
There seem to be a couple of ways to do this - 
1. dump/restore or upgrade -> but this causes downtime. 
2. logical replication/pglogical -> this is going to be slow, adds additional   load on the master, and it isnt recommended to do DDL during time. 

I do have daily backups of these databases. 
Is there a way I can use these daily backups to upgrade to 15 and sync the changes for the day alone? 
I can code in C/scripts, and know bit of internals and wanted to ask where to start on this. 
I am also open to any alternate ideas. 

Take 10 minutes downtime and use "pg_upgrade --link".

Yours,
Laurenz Albe


Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 


Attachment