MIME-Version: 1.0
References: <CAAccyYKpNsQMD+S-A7a8YtDevFN0uRXkzg4tYWWBOFsv_jASNg@mail.gmail.com>
In-Reply-To: <CAAccyYKpNsQMD+S-A7a8YtDevFN0uRXkzg4tYWWBOFsv_jASNg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 8 Aug 2025 14:25:13 -0400
Message-ID: <CAKAnmmJPGgEp2kKPomhq7P=GUA5HD1eOaP5X+7c68gm5f4YVqQ@mail.gmail.com>
Subject: Re: Questions about the continuity of WAL archiving
To: px shi <spxlyy123@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000023eb98063bdeb4b4"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJPGgEp2kKPomhq7P%3DGUA5HD1eOaP5X%2B7c68gm5f4YVqQ%40mail.gmail.com>
Precedence: bulk

--00000000000023eb98063bdeb4b4
Content-Type: text/plain; charset="UTF-8"

>
> There is a scenario: the current timeline of the PostgreSQL primary node
> is 1, and the latest WAL file is 100. The standby node has also received up
> to WAL file 100. However, the latest WAL file archived is only file 80. If
> the primary node crashes at this point and the standby is promoted to the
> new primary, archiving will resume from file 100 on timeline 2. As a
> result, WAL files from 81 to 100 on timeline 1 will be missing from the
> archive.
> Is there a good solution to prevent this situation?
>

I'm still not clear on what the problem here is, other than your archiving
not keeping up. The best solution to that is:

https://pgbackrest.org/1/configuration.html#section-archive/option-archive-async

Yes, you would lost some ability for easy PITR for 80-100, but could still
be done by resurrecting your crashed primary, or carefully grabbing from
the replica before they get recycled. You can set archive_mode=always on
the replicas to help with this.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=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"><div>There is a s=
cenario: the current timeline of the PostgreSQL primary node is 1, and the =
latest WAL file is 100. The standby node has also received up to WAL file 1=
00. However, the latest WAL file archived is only file 80. If the primary n=
ode crashes at this point and the standby is promoted to the new primary, a=
rchiving will resume from file 100 on timeline 2. As a result, WAL files fr=
om 81 to 100 on timeline 1 will be missing from the archive.<br>Is there a =
good solution to prevent this situation?</div></div></blockquote><div><br><=
/div><div>I&#39;m still not clear on what the problem here is, other than y=
our archiving not keeping up. The best solution to that is:</div><div><br><=
/div><div><a href=3D"https://pgbackrest.org/1/configuration.html#section-ar=
chive/option-archive-async">https://pgbackrest.org/1/configuration.html#sec=
tion-archive/option-archive-async</a></div><div><br></div><div>Yes, you wou=
ld lost some ability for easy PITR for 80-100, but could still be done by r=
esurrecting your crashed primary, or carefully grabbing from the replica be=
fore they get recycled. You can set archive_mode=3Dalways on the replicas t=
o help with this.</div><div>=C2=A0</div></div><div dir=3D"ltr" class=3D"gma=
il_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</div><div><br></=
div><div>--</div><div>Crunchy Data - <a href=3D"https://www.crunchydata.com=
" target=3D"_blank">https://www.crunchydata.com</a></div><div>Enterprise Po=
stgres Software Products &amp; Tech Support</div><div><br></div></div></div=
></div>

--00000000000023eb98063bdeb4b4--