MIME-Version: 1.0
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
 <a8f26c633315420c2267f0e9e85944edcd474835.camel@cybertec.at>
In-Reply-To: <a8f26c633315420c2267f0e9e85944edcd474835.camel@cybertec.at>
From: Koen De Groote <kdg.dev@gmail.com>
Date: Fri, 31 Jan 2025 20:38:49 +0100
Message-ID: <CAGbX52HFGM89+0RdXq3v4SLBodWQBeDRa5e91NVhV1ajnG7zTA@mail.gmail.com>
Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c2b9ae062d05b19d"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52HFGM89%2B0RdXq3v4SLBodWQBeDRa5e91NVhV1ajnG7zTA%40mail.gmail.com>
Precedence: bulk

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

No, it's meant to be an off-site restore, as to do a daily check if the
restore actually works.

Regards,
Koen De Groote

On Fri, Jan 31, 2025 at 2:30=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Fri, 2025-01-31 at 10:47 +0100, Koen De Groote wrote:
> > I'm running postgres 16.6
> >
> > My backup strategy is: basebackup and WAL archive. These get uploaded t=
o
> the cloud.
> >
> > The restore is on an isolated machine and is performed daily. It
> downloads the
> > basebackup, unpacks it, sets a recovery.signal, and a script is provide=
d
> 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 b=
e
> 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
> the
> > last one that was available. 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.
> >
> > The postgres logs themselves seem perfectly normal. It logs all these W=
AL
> > 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.
>
> I don't know, that all sounds like it is working as it should.
>
> If the last WAL archive that got downloaded by the "restore_command" is
> indeed
> the last one that was available, recovery did just what it is supposed to=
.
> If new WAL segments get archived later, that's too late.
>
> Perhaps you are looking for replication, not for restoring a backup, whic=
h
> is
> necessarily not totally up to date.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">No, it&#39;s meant to be an off-site restore, as to do a d=
aily check if the restore actually works.<div><br></div><div>Regards,</div>=
<div>Koen De Groote</div></div><br><div class=3D"gmail_quote gmail_quote_co=
ntainer"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 31, 2025 at 2:30=
=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">la=
urenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex">On Fri, 2025-01-31 at 10:47 +0100, Koen De Groote wro=
te:<br>
&gt; I&#39;m running postgres 16.6<br>
&gt; <br>
&gt; My backup strategy is: basebackup and WAL archive. These get uploaded =
to the cloud.<br>
&gt; <br>
&gt; The restore is on an isolated machine and is performed daily. It downl=
oads the<br>
&gt; basebackup, unpacks it, sets a recovery.signal, and a script is provid=
ed as<br>
&gt; restore_command, to download the WAL archives=C2=A0%f and unpack them =
into %p<br>
&gt; <br>
&gt; In the script, the final unpacking is simply &quot;gzip -dc %f &gt; %p=
&quot;. The gz files<br>
&gt; are first checked with &quot;gzip -t&quot;.<br>
&gt; <br>
&gt; If a WAL archive is asked that doesn&#39;t exist yet, the script natur=
ally cannot<br>
&gt; find it, and exits with status code 1. This is the end of the recovery=
.<br>
&gt; <br>
&gt; There are a few tables that are known to receive new entries multiple =
times<br>
&gt; per day. However, the state of the recovery showed the latest item to =
be 2<br>
&gt; days in the past. Checking the live DB, there are an expected amount o=
f items<br>
&gt; since that ID.<br>
&gt; <br>
&gt; I checked the logs, the last WAL archive that got downloaded is indeed=
 the<br>
&gt; last one that was available. The one that failed to download on the re=
store<br>
&gt; machine, was uploaded to the cloud 8 minutes later, according to the u=
pload<br>
&gt; logs on the live DB.<br>
&gt; <br>
&gt; The postgres logs themselves seem perfectly normal. It logs all these =
WAL<br>
&gt; recoveries, switches the timeline, and becomes available.<br>
&gt; <br>
&gt; What could be going wrong? My main issue is that I don&#39;t know wher=
e to start<br>
&gt; looking, since nothing in the logs seems abnormal.<br>
<br>
I don&#39;t know, that all sounds like it is working as it should.<br>
<br>
If the last WAL archive that got downloaded by the &quot;restore_command&qu=
ot; is indeed<br>
the last one that was available, recovery did just what it is supposed to.<=
br>
If new WAL segments get archived later, that&#39;s too late.<br>
<br>
Perhaps you are looking for replication, not for restoring a backup, which =
is<br>
necessarily not totally up to date.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000c2b9ae062d05b19d--