Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tdxMn-00AzwP-IF for pgsql-general@arkaria.postgresql.org; Fri, 31 Jan 2025 20:11:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tdxMm-002R1l-ID for pgsql-general@arkaria.postgresql.org; Fri, 31 Jan 2025 20:11:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tdxMm-002QzL-5u for pgsql-general@lists.postgresql.org; Fri, 31 Jan 2025 20:11:52 +0000 Received: from mail-wr1-x429.google.com ([2a00:1450:4864:20::429]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tdxMj-002adj-1J for pgsql-general@lists.postgresql.org; Fri, 31 Jan 2025 20:11:51 +0000 Received: by mail-wr1-x429.google.com with SMTP id ffacd0b85a97d-385d7b4da2bso2174773f8f.1 for ; Fri, 31 Jan 2025 12:11:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738354309; x=1738959109; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=i1NnJAi0bvhCc+DHKSO9s+NV1zDx7WgX3rgdBacqskY=; b=VTqSaNE/c3Xmum11t2b8fIG0uybxdC9zHh/KUfUTe5w14iWRlrarNZVNN43p7+6dMG DcR1lKlgouldHiO+SFLg/RBr9b/xOiG9/ELeVoRHCg3nAEYM3dhJTjy8YaIbWGa9KdsV vm40WDDv6MzmmSb3h+RwtSyacqo+7M2qZZO0pINhRYZB9jq+Jia9fICrpAh/MJSSp9fF BjnJ1Gnltd0t1IvHTJRcV2kHCSegxLI7zZ+VX/T6i/XHD+Bp6o4VotyzH81ltHdS6snW C4blm57gDCCASYRhiBF8wK6AESMmOjalkhU8iYUhQdwOo01UE1Z4QpLANQwOb23OnxA0 4Mcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738354309; x=1738959109; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=i1NnJAi0bvhCc+DHKSO9s+NV1zDx7WgX3rgdBacqskY=; b=CzWo9WTVJM3D1so/aaPha8C+G105ISfM8g82YgGF9wrXeIJv4SrOJTSdYNmrYal1fo 7iYtl8sxkZdm8hS50wTNKD18w2X1olfx0kPbbVZ/ic0iy8UrbgWtTJkgyzoESTzIdaRn 5IdO4IdyL5VpJApGtQe6GtqEB2YuFIfnHo2LWmJHgEZcWScmjIukzYJrbcNevKDa9iVZ rKHMqbMO2waoikHrOLReU4QkhMM6jptpa6YwdQDqvEPeLTAMzWpr/wOL4ldw0+6cYxAF VoptC7G35kAo5Gy895H6lk4f3uB+VmmztUv2eLVCmjhLS9ZMIdIharestcBCo2MuLvpz gkMg== X-Gm-Message-State: AOJu0YzGwiLVPoJG/EwHxsdptNnpM7n8kc1S6iYfV+OhT7tQWM0rFf9P XtDVcIUeK/5iER6+AWIaQbjg1eMe8i4oCCrMwbaKDsfVDfrJFKN/8IL7KTlv12K9C5okbFVE/DF G6QG0tJ37YWK/GSysfNWSQIPvqBc= X-Gm-Gg: ASbGncsCbtpEpv5XdbcxWjzivd7bIMxnPBC37BmEVbJLM4ZY6iHky9UMZFgN8FjDqUc qozrjb5SLIC0noC68NPaTxJmNiLKchtwUEPU1nOcNV0pMrIVhDkFeATd3AYvqQbl8rtU6iDbkRq VLTWgpaNwMSAVFA/v/CQKiXpEGp8P30OA= X-Google-Smtp-Source: AGHT+IE7iTDC8Zdw7paecb1KrA/g+DNNRQF1yvFlzgAvOSZ8+FpsLOZjyMZtmheKi2gtXSVJLBZ3JSRxTpOJIsfoUtg= X-Received: by 2002:a5d:588f:0:b0:386:375a:8322 with SMTP id ffacd0b85a97d-38c519506eamr13962898f8f.13.1738354308975; Fri, 31 Jan 2025 12:11:48 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Koen De Groote Date: Fri, 31 Jan 2025 21:11:37 +0100 X-Gm-Features: AWEUYZkl6MnMOZe0Hgc98HS6F5ToOiBDNIlHHEb819ThQOCfDoRL3ayICEhh6eU Message-ID: Subject: Re: Postgres restore sometimes restores to a point 2 days in the past To: Laurenz Albe Cc: PostgreSQL General Content-Type: multipart/alternative; boundary="00000000000014952e062d062742" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000014952e062d062742 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Koen De Groote = 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=E2=80=AFPM Laurenz Albe > 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 g= z >> 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 o= f >> 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 t= o. >> 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 >> > --00000000000014952e062d062742 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Any advice on a different mailing list that something like= this would be more suited to?

Regards,
Koen D= e Groote

On Fri, Jan 31, 2025 at 8:38=E2=80=AFPM= Koen De Groote <kdg.dev@gmail.com<= /a>> wrote:
<= div dir=3D"ltr">No, it's meant to be an off-site restore, as to do a da= ily check if the restore actually works.

Regards,
<= div>Koen De Groote
On Fri, Jan 31, 2025 at 2:30=E2=80=AFPM Laurenz Alb= e <laurenz= .albe@cybertec.at> 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 downl= oads the
> basebackup, unpacks it, sets a recovery.signal, and a script is provid= ed as
> restore_command, to download the WAL archives=C2=A0%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 natur= ally 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 o= f 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 re= store
> machine, was uploaded to the cloud 8 minutes later, according to the u= pload
> 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 wher= e 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&qu= ot; is indeed
the last one that was available, recovery did just what it is supposed to.<= br> 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
--00000000000014952e062d062742--