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 1ulkVo-004ywA-5O for pgsql-general@arkaria.postgresql.org; Tue, 12 Aug 2025 08:37:40 +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 1ulkVm-006FEZ-KQ for pgsql-general@arkaria.postgresql.org; Tue, 12 Aug 2025 08:37:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ulkVm-006FER-5w for pgsql-general@lists.postgresql.org; Tue, 12 Aug 2025 08:37:38 +0000 Received: from mail-vs1-xe2b.google.com ([2607:f8b0:4864:20::e2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ulkVj-0009v3-2g for pgsql-general@lists.postgresql.org; Tue, 12 Aug 2025 08:37:37 +0000 Received: by mail-vs1-xe2b.google.com with SMTP id ada2fe7eead31-4fe98e7b241so3699558137.2 for ; Tue, 12 Aug 2025 01:37:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1754987855; x=1755592655; 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=a1Ko6xxbGWi8DarPSNai3Se9lsQFGMD68+UqiWI8LPk=; b=l7unf1rytzvTi4JigJQQC1SUcJUglKJTDf4io3qiK9aG0fdMwGdSSgXN8webVo5K8i 7vv+X0+CKUAVv7iFpKbkxzcjUcKK36t3oYpW//tvwbTcfPb1F+XnH2h/y9jHMb6nwW8W EHCb2/2WjeRbgv4opfFYffxQ8zwB1mVkwTFVVt++3M6c+YBI2jyP2oSsS4DEJyDNCwMn l44FK2XRBbYJ92+rDrGuNzmVkFRGwkPlIej6CWzUe6ZYwCPfbW0gxoStvP0vjBXN7rZA 4tfF2ccoaBWFOJWoj5qbbhOmgknWetso3YJPwgEmDSgXFVAydXxCEV94gwNXwYRUV3lQ 11+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1754987855; x=1755592655; 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=a1Ko6xxbGWi8DarPSNai3Se9lsQFGMD68+UqiWI8LPk=; b=FVQGAE1EakOyRlRYBSxtwF2y6cam+i/zhG+XdYEf/KfbTJ7NndjkhCetwuXP3V22Jg RWA+U3AlvH55vhysiDQVOPUYbf2o0cLh8IizL2t5W/2BpaUdhpOBqVxbO9KeqQhgn5bR ACEXoIzZ/r7GHYqDo2tHHdjsh56vsiMrfHYS74/cImtqyIGcC2fCan9AZaGuudscFirT 7JUL/5Ru1ixPpkw9yydnyxIsJT4ffKJn79Pv7/XZn+dCx3MKojb0kZdMIDY+uFIb5Le7 fi2sNzHhzlrwZj2UJj9A3E08QD+b4hmYdb1eWIPXy1Sr5/EZdnKEP87xCSMv17UsJVxT LLvw== X-Gm-Message-State: AOJu0YzYISYXHVpsot0HIgExddh9FnNP1VQDj/rKjlNKLHqOSw5GzMpA lk23jPscu5qjsPVIRCJg/xvIo+LEDWt8xE9lQPEfRRw05dJVcR6LPGKVRAvwTd1zs9ku0A1WfSi GhHokSLfgrcx+BTlcoL3QMB/xIr1Rptg= X-Gm-Gg: ASbGncs6V9Y8AtBXpEOeLbkQfxbZ7V1gVtKEXg2huBTXATs9fjhiPEhOJbBnR4SxW73 SrTAOZUScp2VvcPIDKqLU+8eyt38Zfl7qQUyU9FeTlqltZunLljcKFLZehTtbRGEBgdiRxpgm0Y zv1Py+/DQ8i27CxGA4sfNhgrW4sVcMs+LqUHLzYOnE/4IL3oyCN9LFvoFMMA4tP8AOKTHZA0Xfo 082Sc8De3DtH9RWhbQCRGY9svrJXoNlPQGiUyZC X-Google-Smtp-Source: AGHT+IHfZaz8VjhisHkiO5gWuTHZ7EX8lBlURVHRD5wkXnTMNr50KXKNEsr52+3ikQyKLAGoJlQS0cEG9MlTiEKd7UA= X-Received: by 2002:a05:6102:41a4:b0:4e7:7787:1c2a with SMTP id ada2fe7eead31-50cbec79a67mr1073067137.23.1754987855444; Tue, 12 Aug 2025 01:37:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: px shi Date: Tue, 12 Aug 2025 16:37:23 +0800 X-Gm-Features: Ac12FXw40e2kbwM-lQ71b2ragg0NdFqxLx5sdSQ20Q447qHExvQmO40Fbg3-sU8 Message-ID: Subject: Re: Questions about the continuity of WAL archiving To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000b58031063c26f311" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b58031063c26f311 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > > Bog-standard PgBackRest retains all WAL files required for a full backup > set and its associated differential/incremental backups. Yes, WAL files are continuous under normal circumstances. However, if the primary node crashes under high load, the archived WAL logs on S3 may be discontinuous. Ron Johnson =E4=BA=8E2025=E5=B9=B48=E6=9C=889=E6= =97=A5=E5=91=A8=E5=85=AD 02:45=E5=86=99=E9=81=93=EF=BC=9A > On Fri, Aug 8, 2025 at 2:26=E2=80=AFPM Greg Sabino Mullane > wrote: > >> There is a scenario: the current timeline of the PostgreSQL primary node >>> is 1, and the latest WAL file is 100. The standby node has also receive= d up >>> to WAL file 100. However, the latest WAL file archived is only file 80.= If >>> the primary node crashes at this point and the standby is promoted to t= he >>> new primary, archiving will resume from file 100 on timeline 2. As a >>> result, WAL files from 81 to 100 on timeline 1 will be missing from the >>> archive. >>> Is there a good solution to prevent this situation? >>> >> >> I'm still not clear on what the problem here is, other than your >> archiving not keeping up. The best solution to that is: >> >> >> https://pgbackrest.org/1/configuration.html#section-archive/option-archi= ve-async >> >> Yes, you would lost some ability for easy PITR for 80-100, but could >> still be done by resurrecting your crashed primary, or carefully grabbin= g >> from the replica before they get recycled. You can set archive_mode=3Dal= ways >> on the replicas to help with this. >> > > Bog-standard PgBackRest retains all WAL files required for a full backup > set and its associated differential/incremental backups, no? I've > certainly done more than one --type=3Dtime --target=3D"${RestoreUntil}" r= estore > without giving a second thought to timelines or whether the WAL exists. > > Maybe I've just ignored the problem, since it (seemingly) does everything > for PITR backups. > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000b58031063c26f311 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Bog-standard PgBackRest retains all WAL f= iles required for a full backup set and its associated differential/increme= ntal backups.
Yes, WAL files are continuous under normal c= ircumstances. However, if the primary node crashes under high load, the arc= hived WAL logs on S3 may be discontinuous.

R= on Johnson <ronljohnsonjr@gma= il.com> =E4=BA=8E2025=E5=B9=B48=E6=9C=889=E6=97=A5=E5=91=A8=E5=85=AD= 02:45=E5=86=99=E9=81=93=EF=BC=9A
On Fri, Aug 8, 2025 at 2:26=E2=80=AFPM Greg Sabino Mulla= ne <htamfids@gma= il.com> wrote:
<= div>There is a scenario: the current timeline of the PostgreSQL primary nod= e is 1, and the latest WAL file is 100. The standby node has also received = up to WAL file 100. However, the latest WAL file archived is only file 80. = If the primary node crashes at this point and the standby is promoted to th= e new primary, archiving will resume from file 100 on timeline 2. As a resu= lt, WAL files from 81 to 100 on timeline 1 will be missing from the archive= .
Is there a good solution to prevent this situation?

I'm still not clear on what the problem here = is, other than your archiving not keeping up. The best solution to that is:=

https://pgbac= krest.org/1/configuration.html#section-archive/option-archive-async

Yes, you would lost some ability for easy PITR for 80= -100, but could still be done by resurrecting your crashed primary, or care= fully grabbing from the replica before they get recycled. You can set archi= ve_mode=3Dalways on the replicas to help with this.

Bog-standard PgBackRest retains= all WAL files required for a full backup set and its associated differenti= al/incremental backups, no?=C2=A0 I've certainly done more than one=C2= =A0--type=3Dtime --target=3D"${RestoreUntil}" restore without giv= ing a second thought to timelines or whether the WAL exists.

=
Maybe I've just ignored the problem, since it (seemingly) do= es everything for PITR backups.=C2=A0

--
Death to <Redacted>, and butter sauce.
Don&#= 39;t boil me, I'm still alive.
<Redacted> lobster!
--000000000000b58031063c26f311--