Feedback-ID: i76984098:Fastmail
Message-ID: <a12f131a-1597-496e-b9d8-e00c13d723bb@aklaver.com>
Date: Fri, 31 Jan 2025 13:26:55 -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>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAGbX52HkW6926c4tY781+iH01x_0qw6Sfo=kT+LHjo_mENqOfQ@mail.gmail.com>
 <70c20a65-4624-4509-ac6a-ef7f0119ea28@aklaver.com>
 <CAGbX52E5_7fPZVW0YSjoTmj5qN-aORXpPqFF+bKCiicM+h3EZQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAGbX52E5_7fPZVW0YSjoTmj5qN-aORXpPqFF+bKCiicM+h3EZQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/a12f131a-1597-496e-b9d8-e00c13d723bb%40aklaver.com>
Precedence: bulk

On 1/31/25 12:10, Koen De Groote wrote:
>  > What is the complete pg_basebackup command?

> 2/ All my WAL files are archived and uploaded to the cloud. So, I can 
> just have them downloaded.

I should have asked earlier what is the archive command?

Are
>  > What is determining that a particular WAL file should be asked for?
> 
> The postgres server itself does this. Here's the documentation: 
> https://www.postgresql.org/docs/16/runtime-config-wal.html#GUC-RESTORE-COMMAND <https://www.postgresql.org/docs/16/runtime-config-wal.html#GUC-RESTORE-COMMAND>
> 
> And here: https://www.postgresql.org/docs/current/warm-standby.html 
> <https://www.postgresql.org/docs/current/warm-standby.html>
> 
> In practice, Postgres will see the "standby.signal" file and start 

In your OP you say:

"It downloads the basebackup, unpacks it, sets a recovery.signal, ..."

Are you setting standby.signal or recovery.signal or both?


> asking for WAL files. It will read the database it has and determine 
> what the next WAL filename should be. And then it asks for it. And it 
> will keep asking for these hexadecimal filenames, 1 at a time, for as 
> long as the command or set of commands provided to "restore_command" 
> returns exit code 0. If the process receives any other exit code, it 
> stops recovery, switches timeline, and considers the database to be up 
> and running at the state its in.
> 
> It's constantly asking "I want this file now" and the script I have as 
> the restore command will attempt to download it from the cloud. Then it 
> will attempt to unzip it and move it into place. If any of these steps 
> fails, I return exit code 1.
> 

> Regards,
> Koen De Groote
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com