MIME-Version: 1.0
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
In-Reply-To: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
From: Koen De Groote <kdg.dev@gmail.com>
Date: Thu, 19 Feb 2026 19:30:03 +0000
Message-ID: <CAGbX52H5S1zoqQryS_80oo69m5V7ueauJhU6RkD9GLLzPq8cBQ@mail.gmail.com>
Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000733c66064b325500"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52H5S1zoqQryS_80oo69m5V7ueauJhU6RkD9GLLzPq8cBQ%40mail.gmail.com>
Precedence: bulk

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

Sorry for reviving this old thread.

I found the cause for this a few days after these messages: The WAL file
download was interrupted. It comes from S3, and for some reason the
downloaded file was not the same size as the one on S3. So, some network
error occurred.

PG tries to restore via the restore_command, which is "gzip - dc". The
restore fails because the file is incomplete, and PG concludes it's not
receiving files anymore and thus, that the restore is done.

This happens again the next day, because the system is set up to not
download files already found on local mount.

Mystery solved.


On Fri, Jan 31, 2025 at 9:47=E2=80=AFAM Koen De Groote <kdg.dev@gmail.com> =
wrote:

> I'm running postgres 16.6
>
> My backup strategy is: basebackup and WAL archive. These get uploaded to
> the cloud.
>
> The restore is on an isolated machine and is performed daily. It download=
s
> the basebackup, unpacks it, sets a recovery.signal, and a script is
> provided as restore_command, to download the WAL archives %f and unpack
> them into %p
>
> In the script, the final unpacking is simply "gzip -dc %f > %p". The gz
> files are first checked with "gzip -t".
>
> If a WAL archive is asked that doesn't exist yet, the script naturally
> cannot find it, and exits with status code 1. This is the end of the
> recovery.
>
> There are a few tables that are known to receive new entries multiple
> times per day. However, the state of the recovery showed the latest item =
to
> be 2 days in the past. Checking the live DB, there are an expected amount
> of items since that ID.
>
> I checked the logs, the last WAL archive that got downloaded is indeed th=
e
> last one that was available. The one that failed to download on the resto=
re
> machine, was uploaded to the cloud 8 minutes later, according to the uplo=
ad
> logs on the live DB.
>
> The postgres logs themselves seem perfectly normal. It logs all these WAL
> recoveries, switches the timeline, and becomes available.
>
> What could be going wrong? My main issue is that I don't know where to
> start looking, since nothing in the logs seems abnormal.
>
> Regards,
> Koen De Groote
>

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

<div dir=3D"ltr"><div>Sorry for reviving this old thread.</div><div><br></d=
iv><div>I found the cause for this a few days after these messages: The WAL=
 file download was interrupted. It comes from S3, and for some reason the d=
ownloaded file was not the same size as the one on S3. So, some network err=
or occurred.<br><br></div><div>PG tries to restore via the restore_command,=
 which is &quot;gzip - dc&quot;. The restore fails because the file is inco=
mplete, and PG concludes it&#39;s not receiving files anymore and thus, tha=
t the restore is done.</div><div><br></div><div>This happens again the next=
 day, because the system is set up to not download files already found on l=
ocal mount.</div><div><br></div><div>Mystery solved.</div><div><br></div><d=
iv><br></div></div><br><div class=3D"gmail_quote gmail_quote_container"><di=
v dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 31, 2025 at 9:47=E2=80=AFAM =
Koen De Groote &lt;<a href=3D"mailto:kdg.dev@gmail.com">kdg.dev@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr">I&#39;m running postgres 16.6<div><br></div><div>My backup s=
trategy is: basebackup and WAL archive. These get uploaded to the cloud.</d=
iv><div><br></div><div>The restore is on an isolated machine and is perform=
ed daily. It downloads the basebackup, unpacks it, sets a recovery.signal, =
and a script is provided as restore_command, to download the WAL archives=
=C2=A0%f and unpack them into %p</div><div><br></div><div>In the script, th=
e final unpacking is simply &quot;gzip -dc %f &gt; %p&quot;. The gz files a=
re first checked with &quot;gzip -t&quot;.</div><div><span style=3D"color:r=
gb(122,126,133);font-family:&quot;Victor Mono SemiBold&quot;,monospace;font=
-size:12pt;background-color:rgb(30,31,34)"><br></span></div>If a WAL archiv=
e is asked that doesn&#39;t exist yet, the script naturally cannot find it,=
 and exits with status code 1. This is the end of the recovery.<div><br></d=
iv><div>There are a few tables that are known to receive new entries multip=
le times per day. However, the state of the recovery showed the latest item=
 to be 2 days in the past. Checking the live DB, there are an expected amou=
nt of items since that ID.</div><div><br></div><div>I checked the logs, the=
 last WAL archive that got downloaded is indeed the last one that was avail=
able. The one that failed to download on the restore machine, was uploaded =
to the cloud 8 minutes later, according to the upload logs on the live DB.<=
/div><div><br></div><div>The postgres logs themselves seem perfectly normal=
. It logs all these WAL recoveries, switches the timeline, and becomes avai=
lable.</div><div><br></div><div>What could be going wrong? My main issue is=
 that I don&#39;t know where to start looking, since nothing in the logs se=
ems abnormal.</div><div><br></div><div>Regards,</div><div>Koen De Groote</d=
iv></div>
</blockquote></div>

--000000000000733c66064b325500--