Feedback-ID: i76984098:Fastmail
Message-ID: <70c20a65-4624-4509-ac6a-ef7f0119ea28@aklaver.com>
Date: Fri, 31 Jan 2025 08:50:34 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Postgres restore sometimes restores to a point 2 days in the past
To: Koen De Groote <kdg.dev@gmail.com>,
 PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/70c20a65-4624-4509-ac6a-ef7f0119ea28%40aklaver.com>
Precedence: bulk

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
adrian.klaver@aklaver.com