Re: Fast logical replication jump start with PG 10

On 05/25/2018 06:35 PM, Olivier Gautherot wrote:
> Hi Adrian, thanks for your reply. Here is the clarification.
>
> 1) It is indeed a pg_upgrade from 9.2 to 10.4. Depending on the test
> machine, it runs in between 15 and 20 minutes for just over 100GB. I can
> negotiate this time with our customer. The vacuum process took another 5
> to 7 minutes. This this what I was referring to with the 30 minutes
> (point 3 in your questions)
>
> 2) After pg_upgrade, I published the tables on the database (in the
> sense "CREATE DATABASE") and subscribed to this publication on the
> second server (logical replication). The data copy processed started
> immediately and took around 1 hour. I then loaded the indexes, what took > another 2h20m. At that point the active-passive cluster was ready to go.

The index creation was done on the replicated machine I presume, using
what command?

> Note that the active and the passive databases are on different machines.
>
> 4) By "database" I mean the result of "CREATE DATABASE" and we have 1
> per server (or "cluster" in your terminology - I tend to use this word
> for a group of machines). We are currently using a streaming replication

Yeah I understand, it is just that database and cluster have specific
meanings in Postgres and it helps to stick to those meanings when
discussing replication operations. Lowers the confusion level:)

> between the 9.2 servers, so it could be a fall-back option after the
> upgrade (I wanted to remove part of the indexes on the master to lower
> the load, reason to use the logical replication... if the execution time
> is not too excessive).

So the time you showed was with those indexes removed or not?

>
> Hope it clarifies the question
> Best regards
> Olivier
>
>
> Olivier Gautherot
> olivier(at)gautherot(dot)net <mailto:olivier(at)gautherot(dot)net>
> Cel:+56 98 730 9361
> Skype: ogautherot
> www.gautherot.net <http://www.gautherot.net>
> http://www.linkedin.com/in/ogautherot
>
> On Fri, May 25, 2018 at 7:51 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 05/25/2018 02:12 PM, Olivier Gautherot wrote:
>
> Hi,
>
> I just sent the question on StackOverflow but realized that this
> audience may be more savvy. So sorry in advance for cross-posting...
>
> I'm in the process of upgrading a PG from 9.2 to 10.4.
> pg_upgrade worked fine on the master and was rather fast. The
> problem is that the database is replicated and I'm planning to
> switch from streaming to logical. The problem is that it is
> rather slow (30 minutes for the master and over 3 hours for the
> replication, between data transfer and indexes).
>
>
> I am not clear on what you did, so can you clarify the following:
>
> 1) pg_upgrade from 9.2 master instance to 10.4 master instance, correct?
>
> 2) What replication are you talking about for the 3 hour value?
>
> 3) What is the 30 minute value referring to?
>
> 4) When you say database are you talking about a Postgres cluster or
> a database in the cluster?
>
>
> Is there a way to speed up the replication or should I rather
> stick to streaming replication? As I have only 1 database on the
> server, it would not be a show-stopper.
>
>
> See 4) above, but if you are talking about a single database in a
> cluster streaming replication will not work for that.
>
>
>
>
> Thanks in advance
> Olivier Gautherot
> http://www.linkedin.com/in/ogautherot
> <http://www.linkedin.com/in/ogautherot>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com