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
