MIME-Version: 1.0
References: <CAGqBcTbWpCuCRuWHd9209k2Q0CHA4j+2MnRR2SwszCXnErG9hw@mail.gmail.com>
 <CAKkG4_kt9zZNPwB2Qd0-jAtRn3m=JPWW_-hc8VSyhfZ+VjfKQw@mail.gmail.com>
In-Reply-To: <CAKkG4_kt9zZNPwB2Qd0-jAtRn3m=JPWW_-hc8VSyhfZ+VjfKQw@mail.gmail.com>
From: Craig McIlwee <craigm@vt.edu>
Date: Thu, 7 Nov 2024 08:26:46 -0500
Message-ID: <CAGqBcTZKSYTuVmf6ppR=GKYPtgKKOp6DASaP6YZYUAks49EHoQ@mail.gmail.com>
Subject: Re: Trouble using pg_rewind to undo standby promotion
To: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a97e9f0626529641"
Archived-At: <https://www.postgresql.org/message-id/CAGqBcTZKSYTuVmf6ppR%3DGKYPtgKKOp6DASaP6YZYUAks49EHoQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Nov 7, 2024 at 4:47=E2=80=AFAM Torsten F=C3=B6rtsch <tfoertsch123@g=
mail.com>
wrote:

> Your point of divergence is in the middle of the 7718/000000BF file. So,
> you should have 2 such files eventually, one on timeline 1 and the other =
on
> timeline 2.
>
> Are you archiving WAL on the promoted machine in a way that your
> restore_command can find it? Check archive_command and archive_mode on th=
e
> promoted machine.
>

No, the promoted machine is not archiving.  How should that work?  Is it OK
for a log shipping standby that uses restore_command to also push to the
same directory with an archive_command or would that cause issues of trying
to read and write the same file simultaneously during WAL replay?  Or
should I be setting up an archive_command that pushes to a separate
directory and have a restore_command that knows to check both locations?

Hmm, as I write that out, I realize that I could use archive_mode =3D on
instead of archive_mode =3D always to avoid the potential for read/write
conflicts during WAL replay.  I can try this later and report back.

Also, do your archive/restore scripts work properly for history files?
>

The scripts don't do anything special with history files.  They are based
on the continuous archive docs [1] and this [2] article the with slight
modification to include a throttled scp since the log shipping server is
located in a different data center from the promoted standby and there is
limited bandwidth between the two.  (Also note that the archive script from
[2] is adapted to properly handle file transfer failures - the one in the
article will use the exit code of the rm command so postgres won't be
informed the file transfer fails resulting in missing WAL in the archive.)

Archive script:
---
#!/bin/bash

# $1 =3D %p
# $2 =3D %f

limit=3D10240 # 10Mbps

gzip < /var/lib/pgsql/13/data/$1 > /tmp/archive/$2.gz

scp -l $limit /tmp/archive/$2.gz postgres@x.x.x.x
:/data/wal_archive/operational/$2.gz
exit_code=3D$?

rm /tmp/archive/$2.gz

exit $exit_code
---

Restore script:
---
gunzip < /data/wal_archive/operational/$2.gz > $1
---

[1]
https://www.postgresql.org/docs/13/continuous-archiving.html#COMPRESSED-ARC=
HIVE-LOGS
[2]
https://www.rockdata.net/tutorial/admin-archive-command/#compressing-and-ar=
chiving

Craig

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Nov 7, 2024 at 4:47=E2=80=AFAM To=
rsten F=C3=B6rtsch &lt;<a href=3D"mailto:tfoertsch123@gmail.com">tfoertsch1=
23@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Your point of divergenc=
e is in the middle of the 7718/000000BF file. So, you should have 2 such fi=
les eventually, one on timeline 1 and the other on timeline 2.<div><br></di=
v><div>Are you archiving WAL on the promoted machine in a way that your res=
tore_command can find it? Check archive_command and archive_mode on the pro=
moted machine.</div></div></blockquote><div><br></div><div>No, the promoted=
 machine is not archiving.=C2=A0 How should that work?=C2=A0 Is it OK for a=
 log shipping standby that uses restore_command to also push to the same di=
rectory with an archive_command or would that cause issues of trying to rea=
d and write the same file simultaneously during WAL replay?=C2=A0 Or should=
 I be setting up an archive_command that pushes to a separate directory and=
 have a restore_command that knows to check both locations?</div><div><br><=
/div><div>Hmm, as I write that out, I realize that I could use archive_mode=
 =3D on instead of archive_mode =3D always to avoid the potential for read/=
write conflicts during WAL replay.=C2=A0 I can try this later and report ba=
ck.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div dir=3D"ltr"><div>Also, do your archive/restore scripts work properly fo=
r history files?</div></div></blockquote><div><br></div><div>The scripts do=
n&#39;t do anything special with history files.=C2=A0 They are based on the=
 continuous archive docs [1] and this [2] article the with slight modificat=
ion to include a throttled scp since the log shipping server is located in =
a different data center from the promoted standby and there is limited band=
width between the two.=C2=A0 (Also note that the archive script from [2] is=
 adapted to properly handle file transfer failures - the one in the article=
 will use the exit code of the rm command so postgres won&#39;t be informed=
 the file transfer fails resulting in missing WAL in the archive.)</div><di=
v><br></div><div>Archive script:</div><div>---</div><div>#!/bin/bash<br><br=
># $1 =3D %p<br># $2 =3D %f <br><br>limit=3D10240 # 10Mbps<br><br>gzip &lt;=
 /var/lib/pgsql/13/data/$1 &gt; /tmp/archive/$2.gz<br><br>scp -l $limit /tm=
p/archive/$2.gz postgres@x.x.x.x:/data/wal_archive/operational/$2.gz<br>exi=
t_code=3D$? <br><br>rm /tmp/archive/$2.gz<br><br>exit $exit_code</div><div>=
---</div><div><br></div><div>Restore script:</div><div>---</div><div><span =
class=3D"gmail-ui-provider gmail-a gmail-b gmail-c gmail-d gmail-e gmail-f =
gmail-g gmail-h gmail-i gmail-j gmail-k gmail-l gmail-m gmail-n gmail-o gma=
il-p gmail-q gmail-r gmail-s gmail-t gmail-u gmail-v gmail-w gmail-x gmail-=
y gmail-z gmail-ab gmail-ac gmail-ae gmail-af gmail-ag gmail-ah gmail-ai gm=
ail-aj gmail-ak" dir=3D"ltr">gunzip &lt; /data/wal_archive/operational/$2.g=
z &gt; $1</span></div><div>---</div><div><br></div><div>[1]=C2=A0<a href=3D=
"https://www.postgresql.org/docs/13/continuous-archiving.html#COMPRESSED-AR=
CHIVE-LOGS">https://www.postgresql.org/docs/13/continuous-archiving.html#CO=
MPRESSED-ARCHIVE-LOGS</a></div><div>[2]=C2=A0<a href=3D"https://www.rockdat=
a.net/tutorial/admin-archive-command/#compressing-and-archiving">https://ww=
w.rockdata.net/tutorial/admin-archive-command/#compressing-and-archiving</a=
></div><div><br></div><div>Craig</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">
</blockquote></div>
</blockquote></div></div>

--000000000000a97e9f0626529641--