public inbox for [email protected]  

help / color / mirror / Atom feed

From: Koen De Groote <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
Date: Fri, 31 Jan 2025 21:11:37 +0100
Message-ID: <CAGbX52HJD1=r2Mh3_0rxATXB=oKvHm0dGCQp2SvcSbp=upTVpg@mail.gmail.com> (raw)
In-Reply-To: <CAGbX52HFGM89+0RdXq3v4SLBodWQBeDRa5e91NVhV1ajnG7zTA@mail.gmail.com>
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
	<[email protected]>
	<CAGbX52HFGM89+0RdXq3v4SLBodWQBeDRa5e91NVhV1ajnG7zTA@mail.gmail.com>

Any advice on a different mailing list that something like this would be
more suited to?

Regards,
Koen De Groote

On Fri, Jan 31, 2025 at 8:38 PM Koen De Groote <[email protected]> wrote:

> 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 PM Laurenz Albe <[email protected]>
> 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
>> to 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
>> 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
>> 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
>> 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.
>>
>> 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,
>> which is
>> necessarily not totally up to date.
>>
>> Yours,
>> Laurenz Albe
>>
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
  In-Reply-To: <CAGbX52HJD1=r2Mh3_0rxATXB=oKvHm0dGCQp2SvcSbp=upTVpg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox