Re: Fast logical replication jump start with PG 10
От | Adrian Klaver |
---|---|
Тема | Re: Fast logical replication jump start with PG 10 |
Дата | |
Msg-id | 239dddd5-aa45-a7fe-b35a-085b978f8bd6@aklaver.com обсуждение исходный текст |
Ответ на | Re: Fast logical replication jump start with PG 10 (Olivier Gautherot <olivier@gautherot.net>) |
Ответы |
Re: Fast logical replication jump start with PG 10
(Olivier Gautherot <olivier@gautherot.net>)
|
Список | pgsql-general |
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-passivecluster 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@gautherot.net <mailto:olivier@gautherot.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Olivier GautherotДата:
Сообщение: Re: Fast logical replication jump start with PG 10