public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Koen De Groote <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
Date: Fri, 31 Jan 2025 08:50:34 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>

On 1/31/25 01:47, Koen De Groote wrote:

Comments in line.

> 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
> 

What is the complete pg_basebackup command?

> 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.

I don't understand the above.

What is determining that a particular WAL file should be asked for?

> 
> 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.

How active is the primary database you are pulling from?

> 
> 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.

Available where?

If that was the last one available how could the subsequent one be a 
failure to download?

> 
> 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

-- 
Adrian Klaver
[email protected]

view thread (2+ messages)  latest in thread

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: <[email protected]>

* 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