Re: Streaming Replication Error

--00000000000012ba1c059a28a984
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Dec 20, 2019 at 2:12 PM <[email protected]> wrote:

> -          Did a *pg_restore* on MASTER (The existing instance - MASTER)
>
> -          The SLAVE  has all the data after the restore was done in
> MASTER =E2=80=93 both were in sync.
>
How did you determine that it had all the data?  I suspect that they fell
out of sync towards the end of the pg_restore, and your method just
couldn't detect this fact.  At least, I can't think of anything which would
cause them to lose sync exactly at the end of pg_restore.  Maybe it was
just that the first checkpoint after pg_restore was finished caused the
necessary WAL files to be recycled.  It could have just as easily been a
checkpoint running during the pg_restore which caused the problem, but by
luck it was not.


Please let me know if I did something wrong above step highlighted ?
>

I don't think you did anything objectively wrong.  You could argue that
using wal_keep_segments rather than a replication slot was wrong, or you
could say it was not wrong but just a calculated risk.  In this case, it
seems the risk was realized.  Using a replication slot would also be a
risk, the risk in that case being that the streaming to replica can't keep
up, and so pg_wal fills up to capacity and crashes the master.  You have to
decide what risk you would rather take.


> Does that mean I cannot refresh the MASTER anytime which should replicate
> to SLAVE?
>

Usually the master is your production server.  Why would you be refreshing
it?  Where would you be refreshing it from?  What other server exists that
contains a higher level of truth than what your master production server
already has?

You can use a replication slot, you can increase wal_keep_segments to a
larger value (although there is no way to know with certainty ahead of time
what value will be large enough), or you can just deal with the risk that
your replica may occasionally lose sync and need to be recreated.  You
might also be able to change the topology so your current replica and
current master both stream from the higher-source-of-truth server, rather
than cascading changes, first logically and then physically. There is no
correct answer, you have to understand and weigh the balance of risks for
yourself.

Cheers,

Jeff

>

--00000000000012ba1c059a28a984
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Dec 20, 2019 at 2:12 PM &lt;<a hr=
ef=3D"mailto:[email protected]">[email protected]</a>=
&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex">





<div lang=3D"NL">
<div class=3D"gmail-m_-1774053279096863242WordSection1">
<p class=3D"MsoNormal"><span lang=3D"EN-GB" style=3D"background:yellow">-<s=
pan style=3D"font-variant-numeric:normal;font-variant-east-asian:normal;fon=
t-stretch:normal;font-size:7pt;line-height:normal;font-family:&quot;Times N=
ew Roman&quot;">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
</span></span><u></u><span lang=3D"EN-GB" style=3D"background:yellow">Did a
<b>pg_restore</b> on MASTER (The </span><span lang=3D"EN-GB" style=3D"backg=
round:yellow">existing instance</span><span lang=3D"EN-GB" style=3D"backgro=
und:yellow"> - MASTER)</span><br></p>
<p class=3D"gmail-m_-1774053279096863242MsoListParagraph"><u></u><span lang=
=3D"EN-GB"><span>-<span style=3D"font:7pt &quot;Times New Roman&quot;">=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
</span></span></span><u></u><span lang=3D"EN-GB">The SLAVE =C2=A0has all th=
e data after the restore was done in MASTER =E2=80=93 both were in sync.</s=
pan></p></div></div></blockquote><div>How did you determine that it had all=
 the data?=C2=A0 I suspect that they fell out of sync towards the end of th=
e pg_restore, and your method just couldn&#39;t detect this fact.=C2=A0 At =
least, I can&#39;t think of anything which would cause them to lose sync ex=
actly at the end of pg_restore.=C2=A0 Maybe it was just that the first chec=
kpoint after pg_restore was finished caused the necessary WAL files to be r=
ecycled.=C2=A0 It could have just as easily been a checkpoint running durin=
g the pg_restore which caused the problem, but by luck it was not.</div><di=
v>=C2=A0</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div lang=3D"NL"><div class=3D"gmail-m_-1774053279096863242WordSection1=
"><p class=3D"MsoNormal"><span lang=3D"EN-GB">Please let me know if I did s=
omething wrong above step highlighted ? </span></p></div></div></blockquote=
><div><br></div><div>I don&#39;t think you did anything objectively wrong.=
=C2=A0 You could argue that using=C2=A0wal_keep_segments rather than a repl=
ication slot was wrong, or you could say it was not wrong but just a calcul=
ated risk.=C2=A0 In this case, it seems the risk was realized.=C2=A0 Using =
a replication slot would also be a risk, the risk in that case being that t=
he streaming to replica can&#39;t keep up, and so pg_wal fills up to capaci=
ty and crashes the master.=C2=A0 You have to decide what risk you would rat=
her take.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div lang=3D"NL"><div class=3D"gmail-m_-1774053279096863242WordSecti=
on1"><p class=3D"MsoNormal"><span lang=3D"EN-GB">Does that mean I cannot re=
fresh the MASTER anytime which should replicate to SLAVE?</span></p></div><=
/div></blockquote><div><br></div><div>Usually the master is your production=
 server.=C2=A0 Why would you be refreshing it?=C2=A0 Where would you be ref=
reshing it from?=C2=A0 What other server exists that contains a higher leve=
l of truth than what your master production server already has?</div><div><=
br></div><div>You can use a replication slot, you can increase wal_keep_seg=
ments to a larger value (although there is no way to know with certainty ah=
ead of time what value will be large enough), or you can just deal with the=
 risk that your replica may occasionally lose sync and need to be recreated=
.=C2=A0 You might also be able to change the topology so your current repli=
ca and current master both stream from the higher-source-of-truth server, r=
ather than cascading changes, first logically and then physically. There is=
 no correct answer, you have to understand and weigh the balance of risks f=
or yourself.</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang=3D"NL"><d=
iv class=3D"gmail-m_-1774053279096863242WordSection1"><div><div><div>
</div>
</div>
</div>
</div>
</div>

</blockquote></div></div>

--00000000000012ba1c059a28a984--