Search Postgresql Archives

Re: Fast logical replication jump start with PG 10

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx <mailto:olivier@xxxxxxxxxxxxx>
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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux