From: Andrew Dunstan <andrew@dunslane.net>
To: Westwood, Giles <g.westwood@orcid.org>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Performance for initial copy when using pg_logical to upgrade Postgres
Date: Fri, 24 Sep 2021 12:00:42 -0400
Message-ID: <8b064d7e-204f-9a89-24a1-e856c66d408d@dunslane.net> (raw)
In-Reply-To: <CAEMahfP0FCUUuFrDY7gpacwSRp_jM7eNCA__kbVeMupWShE=8A@mail.gmail.com>
References: <CAEMahfP0FCUUuFrDY7gpacwSRp_jM7eNCA__kbVeMupWShE=8A@mail.gmail.com>
On 9/24/21 10:28 AM, Westwood, Giles wrote:
> At Orcid we're trying to upgrade our Postgres database (10 to 13)
> using pg_logical for no downtime. The problem we have is how long the
> initial copy is taking for the ~500GB database. If it takes say 20days
> to complete, will we need to have 20days of WAL files to start
> catching up when it's complete?
>
> I read an earlier thread which pointed me to the tool
> pglogical_create_subscriber which does a pg_basebackup to start the
> initial replication but this is only going to be useful for logical
> clusters on the same version.
>
> I had hoped that the COPY could be parallelized more by
> "max_sync_workers_per_subscription" which is set to 2. However there's
> only a single process:-
>
> postgres 1022196 6.0 24.5 588340 491564 ? Ds Sep22 193:19
> postgres: main: xxx xxxx 10.xx.xx.xx(59144) COPY
>
> One of the best resources I've found of real world examples are thead
> on gitlabs own gitlab about their Postgres migrations. They discussed
> one method that might work:-
>
> 1. Setup 9.6 secondary via streaming
> 2. Turn physical secondary into logical secondary
> 3. Shutdown and upgrade secondary to 10
> 4. Turn secondary back on.
>
> In which case we would only need the time required to perform the upgrade.
If you're using the pglogical extension, the best way is often to create
the replica as a physical replica (using pg_basebackup for example), and
then using the extension's utility program pglogical_create_subscriber
to convert the physical replica to a logical replica, which you then
upgrade and switch over to.
Of course, test it out before doing this for real.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, g.westwood@orcid.org, pgsql-performance@lists.postgresql.org
Subject: Re: Performance for initial copy when using pg_logical to upgrade Postgres
In-Reply-To: <8b064d7e-204f-9a89-24a1-e856c66d408d@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox