Message-ID: <a8f26c633315420c2267f0e9e85944edcd474835.camel@cybertec.at>
Subject: Re: Postgres restore sometimes restores to a point 2 days in the
 past
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Koen De Groote <kdg.dev@gmail.com>, PostgreSQL General
	 <pgsql-general@lists.postgresql.org>
Date: Fri, 31 Jan 2025 14:30:01 +0100
In-Reply-To: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
References: 
	<CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/a8f26c633315420c2267f0e9e85944edcd474835.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-01-31 at 10:47 +0100, Koen De Groote wrote:
> I'm running postgres 16.6
>=20
> My backup strategy is: basebackup and WAL archive. These get uploaded to =
the cloud.
>=20
> 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=C2=A0%f and unpack them int=
o %p
>=20
> In the script, the final unpacking is simply "gzip -dc %f > %p". The gz f=
iles
> are first checked with "gzip -t".
>=20
> If a WAL archive is asked that doesn't exist yet, the script naturally ca=
nnot
> find it, and exits with status code 1. This is the end of the recovery.
>=20
> There are a few tables that are known to receive new entries multiple tim=
es
> 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 i=
tems
> since that ID.
>=20
> 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.
>=20
> The postgres logs themselves seem perfectly normal. It logs all these WAL
> recoveries, switches the timeline, and becomes available.
>=20
> What could be going wrong? My main issue is that I don't know where to st=
art
> 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 ind=
eed
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, which =
is
necessarily not totally up to date.

Yours,
Laurenz Albe