Re: Streaming Replication Error

--000000000000898c26059a256544
Content-Type: text/plain; charset="UTF-8"

On Fri, Dec 20, 2019 at 11:08 AM <[email protected]> wrote:

> Hi Experts,
>
>
>
> I have set up streaming replication on PostgreSQL 12.1
>
>
>
> The master and slave are configured as below and WAL files are
> accumulating on the master.
>
>
>
> However, something is wrong as I get complaints that the WAL files are
> missing after a pg_restore on MASTER
>

You ran pg_restore on the master?  Why did you do that?  Doesn't that mean
you now have a new master, different from the old master?  Did you restore
into a new instance, or into the existing instance?  What command line did
you use?


>
> *SLAVE*
>
>  ...
>
> 2019-12-20 16:37:08.042 CET [21981] FATAL:  could not receive data from
> WAL stream: ERROR:  requested WAL segment 000000010000000100000076 has
> already been removed
>
>
>
>
>
> Then the pg_basebackup is run and the slave started.
>

Presumably you just didn't restart the slave.  You blew away the old one
entirely, and started the new one created by the pg_basebackup.

What was the command line used for pg_basebackup?


>
>
> The slave has all the data as of the time of the backup, but no new data
> from the WAL files, and the error above.
>

This is confusing.  You got the above errors before you did the new
pg_basebackup, or after?


>
>
> *max_wal_senders = 10          *
>
> *wal_keep_segments = 120*
>
>
>
> What have I mis-configured?
>

Your email doesn't seem to be written in chronological order, and you
didn't include the parameters for the commands you ran, so it is hard to
say what you did wrong, as we don't know what you did.

It could be that your replica is seeking files from the wrong master.


> Do we need to enabled archive_mode = on for streaming replication?
>

That can sometimes be useful, but it is not necessary.  You can use a
replication slot to force the master to retain sufficient logs, or you can
set wal_keep_segments high enough that it (probably) keeps enough on its
own.

One reason I sometimes find archive_mode to be useful in a streaming setup
is that I can inject a compression step.  WAL files compress very well, and
you don't get that compression with a streaming connection.  So if you fall
behind and have a slow network, you can catch up much faster using a
compressed archive.

Cheers,

Jeff

--000000000000898c26059a256544
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 11:08 AM &lt;<a h=
ref=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_2291428639134109915WordSection1">
<p class=3D"MsoNormal"><span lang=3D"EN-GB">Hi Experts,<u></u><u></u></span=
></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">I have set up streaming replica=
tion on PostgreSQL 12.1<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">The master and slave are config=
ured as below and WAL files are accumulating on the master.
<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">However, something is wrong as =
I get complaints that the WAL files are missing after a pg_restore on MASTE=
R</span></p></div></div></blockquote><div><br></div><div>You ran pg_restore=
 on the master?=C2=A0 Why did you do that?=C2=A0 Doesn&#39;t that mean you =
now have a new master, different from the old master?=C2=A0 Did you restore=
 into a new instance, or into the existing instance?=C2=A0 What command lin=
e did you use?</div><div><br></div><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_2291428639134109915WordSe=
ction1"><p class=3D"MsoNormal"><span lang=3D"EN-GB">=C2=A0<u></u></span></p=
>
<p class=3D"MsoNormal"><b><u><span lang=3D"EN-GB">SLAVE<u></u><u></u></span=
></u></b></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0...</span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">2019-12-20 16:37:08.042 CET [21=
981] FATAL:=C2=A0 could not receive data from WAL stream: ERROR:=C2=A0 requ=
ested WAL segment 000000010000000100000076 has already been removed<u></u><=
u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">Then the pg_basebackup is run a=
nd the slave started.</span></p></div></div></blockquote><div><br></div><di=
v>Presumably you just didn&#39;t restart the slave.=C2=A0 You blew away the=
 old one entirely, and started the new one created by the pg_basebackup.</d=
iv><div><br></div><div>What was the command line used for pg_basebackup?</d=
iv><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
lang=3D"NL"><div class=3D"gmail-m_2291428639134109915WordSection1"><p class=
=3D"MsoNormal"><span lang=3D"EN-GB"><u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">The slave has all the data as o=
f the time of the backup, but no new data from the WAL files, and the error=
 above.</span></p></div></div></blockquote><div><br></div><div>This is conf=
using.=C2=A0 You got the above errors before you did the new pg_basebackup,=
 or after?</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-le=
ft:1ex"><div lang=3D"NL"><div class=3D"gmail-m_2291428639134109915WordSecti=
on1"><p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u><u></u></span></p>
<p class=3D"MsoNormal"><b><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span><=
/b></p>
<p class=3D"MsoNormal"><b><span lang=3D"EN-GB">max_wal_senders =3D 10=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <u></u><u></u></span></b><=
/p>
<p class=3D"MsoNormal"><b><span lang=3D"EN-GB">wal_keep_segments =3D 120<u>=
</u><u></u></span></b></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB">What have I mis-configured?</sp=
an></p></div></div></blockquote><div><br></div><div>Your email doesn&#39;t =
seem to be written in chronological order, and you didn&#39;t include the p=
arameters for the commands you ran, so it is hard to say what you did wrong=
, as we don&#39;t know what you did.</div><div><br></div><div>It could be t=
hat your replica is seeking files from the wrong master.</div><div>=C2=A0</=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang=3D"NL"><div=
 class=3D"gmail-m_2291428639134109915WordSection1"><p class=3D"MsoNormal"><=
span lang=3D"EN-GB"> Do we need to enabled archive_mode =3D on for streamin=
g replication?</span></p></div></div></blockquote><div><br></div><div>That =
can sometimes be useful, but it is not necessary.=C2=A0 You can use a repli=
cation slot to force the master to retain sufficient logs, or you can set w=
al_keep_segments high enough that it (probably) keeps enough on its own.</d=
iv><div><br></div><div>One reason I sometimes find archive_mode to be usefu=
l in a streaming setup is that I can inject a compression step.=C2=A0 WAL f=
iles compress very well, and you don&#39;t get that compression with a stre=
aming connection.=C2=A0 So if you fall behind and have a slow network, you =
can catch up much faster using a compressed archive.</div><div><br></div><d=
iv>Cheers,</div><div><br></div><div>Jeff</div><div><br></div></div></div>

--000000000000898c26059a256544--