upgrade using logical replication

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

upgrade using logical replication

Atul Kumar-2
Hi,

We are planning to upgrade from postgres 9.5  to postgres 10, on
centos version 6.8, We have a database of around 400GBs.

We need to perform the upgrade activity with minimum downtime (around
1-2 hours). We are thinking of logical replication for the same.

but The issue is we already have configured streaming replication on
it (1 master and 2 slaves).

So On master, we can upgrade the master by changing parameter
wal_level to replica.

but I need to know how will I upgrade the slave servers, so I am
little confused for the approach for upgrade
.


Reply | Threaded
Open this post in threaded view
|

Re: upgrade using logical replication

Michael Lewis
Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ hour of downtime, it seems overly complicated to use logical replication.
Reply | Threaded
Open this post in threaded view
|

Re: upgrade using logical replication

Paul Förster
Hi Michael,

> On 20. Jan, 2021, at 20:37, Michael Lewis <[hidden email]> wrote:
>
> Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ hour of downtime, it seems overly complicated to use logical replication.

my all time best score was 18 seconds for migrating from 11 to 12. :-)

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: upgrade using logical replication

Mohamed Wael Khobalatte
In reply to this post by Michael Lewis


On Wed, Jan 20, 2021 at 2:37 PM Michael Lewis <[hidden email]> wrote:
Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ hour of downtime, it seems overly complicated to use logical replication.

I suppose the Atul's issue is what to do with the replicas. Once he does pg_upgrade, then he will need to provision new ones, no? I suppose in this case logical would be better, with the new instance itself having replicas. I haven't done it, and it's gonna require some setup time, definitely much longer than pg_upgrade then make do with one server until your new physical replicas are set up. 
Reply | Threaded
Open this post in threaded view
|

Re: upgrade using logical replication

Ian Lawrence Barwick
2021年1月21日(木) 9:19 Mohamed Wael Khobalatte <[hidden email]>:


On Wed, Jan 20, 2021 at 2:37 PM Michael Lewis <[hidden email]> wrote:
Using pg_upgrade takes minutes for an in place upgrade. If you can allow 1+ hour of downtime, it seems overly complicated to use logical replication.

I suppose the Atul's issue is what to do with the replicas. Once he does pg_upgrade, then he will need to provision new ones, no? I suppose in this case logical would be better, with the new instance itself having replicas. I haven't done it, and it's gonna require some setup time, definitely much longer than pg_upgrade then make do with one server until your new physical replicas are set up. 

The replicas will need to be set up at some point anyway; with logical
replication the new cluster is ready to go once the new primary is fully
"seeded" (and the new replicas have caught up with that). Switchover can then
take place whenever convenient, with minimal downtime, more time for testing,
and the possibility of switching back if issues are encountered.

Potential downsides to this approach are that the database schema may need to be
modified to be suitable for logical replication, and additional resources may be
needed to host the old and new clusters simultaneously during the migration
process.

Regards

Ian Barwick

--
Reply | Threaded
Open this post in threaded view
|

Re: upgrade using logical replication

Bruce Momjian
On Thu, Jan 21, 2021 at 10:25:39AM +0900, Ian Lawrence Barwick wrote:

> 2021年1月21日(木) 9:19 Mohamed Wael Khobalatte <[hidden email]>:
>
>
>
>     On Wed, Jan 20, 2021 at 2:37 PM Michael Lewis <[hidden email]> wrote:
>
>         Using pg_upgrade takes minutes for an in place upgrade. If you can
>         allow 1+ hour of downtime, it seems overly complicated to use logical
>         replication.
>
>
>     I suppose the Atul's issue is what to do with the replicas. Once he does
>     pg_upgrade, then he will need to provision new ones, no? I suppose in this
>     case logical would be better, with the new instance itself having replicas.
>     I haven't done it, and it's gonna require some setup time, definitely much
>     longer than pg_upgrade then make do with one server until your new physical
>     replicas are set up. 
>
>
> The replicas will need to be set up at some point anyway; with logical
> replication the new cluster is ready to go once the new primary is fully
> "seeded" (and the new replicas have caught up with that). Switchover can then
> take place whenever convenient, with minimal downtime, more time for testing,
> and the possibility of switching back if issues are encountered.
>
> Potential downsides to this approach are that the database schema may need to
> be
> modified to be suitable for logical replication, and additional resources may
> be
> needed to host the old and new clusters simultaneously during the migration
> process.

pg_upgrade docs have instructions on how to upgrade replicas in place
using rsync with hard links.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee