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 1ultAA-006t0F-9s for pgsql-general@arkaria.postgresql.org; Tue, 12 Aug 2025 17:51: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 1ultA8-0090tv-QH for pgsql-general@arkaria.postgresql.org; Tue, 12 Aug 2025 17:51:53 +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 1ultA8-0090te-EX for pgsql-general@lists.postgresql.org; Tue, 12 Aug 2025 17:51:52 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ultA6-000F79-00 for pgsql-general@postgresql.org; Tue, 12 Aug 2025 17:51:52 +0000 Received: by mail-ot1-x331.google.com with SMTP id 46e09a7af769-741b1657dd8so2870596a34.2 for ; Tue, 12 Aug 2025 10:51:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1755021108; x=1755625908; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=MnfPBnjT4RjJBf1yVksZULPboVB9RBDxhFQRrKbUNN8=; b=BAWjYQeyUyGtgxlhHzJPk4Bj1uYB5K3+FdMqMlbLNDzqmb+hzLDcL9lrP/ztLwm/ss j6xQ+Rfvyk8XZRpEvu3YvfTRxWl1/csYv6L3ocF6FaVYlwf3dN8lv5v5PQoHJLc54AJl LIICH9BAsS03ZwcsIjjo+IR5X7fDhhw9zr3yLEuv2tbc2TrutaxCjjswuWW2yji3EKYm ZIAs3v97t3DyVhzHxzWuInR4H2O4I1g1bmX1IuGnR88eVUhHIqVl3+ddAqq+u/hGLvVp xA9iuD7Q1lgs8uDk++oKL3SP5eaCTHS9si0FTHc5mMncmpwiMFhXGF86WwWz9W7iI8CV lpZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1755021108; x=1755625908; h=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=MnfPBnjT4RjJBf1yVksZULPboVB9RBDxhFQRrKbUNN8=; b=wK85CTNxsLQfpoWUKnXj4q/L8cAAbWDIZ7UMsw5qPt4bf0lmsK5JYBQGe70W93RaMV 5RhXYK5XYHL1PY1rHfNN9SMfYk9iY6MDYB/BNwmkYP4FYRSn4g9E0Ix26jaNG+u0mNnM D3ZzO7DExtL82bHyPy7MQBLql6+QowTSu9SNXrJkobZpPloCiX9jBQwQ2m4pc3hwhuHU JiyxuJYyouJSTgpw5PtuGe/xw2Ehj4pQS65YVCSaSPY9AbTAWbMoCPYWRwAvZU+8V3y1 lyObH34bUXGOjZQ1T6STN8SjYVbIjrpw52jB5rFBOOeTEKkgv0R1Tu21Vi00hnlmIDY9 ez9Q== X-Gm-Message-State: AOJu0YycS+359RoVPtWvDFh8A0YZPblrNW8QJ9kotY9nUk6L5HNaDqex wI6GxZyTYY5vcgKvMfRzJijVpLZSlDcP5Ik0W+z2RGEjwhcd8OscOtfWEOhZe2bjEp0AuQzRYvw MYCoK2dubPWqGsQrHQ/8NlRjX047Wmb2WK5ei X-Gm-Gg: ASbGncst0OI6xRz/i9dm7O7NmJcA7toPvg4bn8W1llZCoYs1NWC6tVHyCx2QOgGPDZk XSd05Hjl7TSbTr2Gh8w2TQGnlAENPI4OIySPWUK1WcDSkUNrkQVtTK4rlWqZgv9CfEGBri4Xja9 kAssU60sFt4nK/yXKUVdV3zOj5tPsIw4hD1uhmGc2eys3nPXKkkwE3ZyZX0R9gBmgkQUXL5raI8 dDjLJTV X-Google-Smtp-Source: AGHT+IGR8v5memP+jetbLSD7TcJQmc9tYJdveDqyv295LhWsVp/HX35E1x1oVzrnmsRBh93w29k790vM/oGn2ESsmAY= X-Received: by 2002:a05:6830:621a:b0:743:48c:5b1 with SMTP id 46e09a7af769-743754a086emr186507a34.20.1755021108011; Tue, 12 Aug 2025 10:51:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 12 Aug 2025 13:51:37 -0400 X-Gm-Features: Ac12FXxFUHddyXhXwyzRVSGo3vXbtD6D-Mn9sgtoDawNRP3MmhCNeKJdva2uYUY Message-ID: Subject: Re: Questions about the continuity of WAL archiving To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000b773b4063c2eb196" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b773b4063c2eb196 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Aug 12, 2025 at 4:37=E2=80=AFAM px shi wrote: > 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. > 1) PG does not purge WAL files that are needed for immediate crash recovery= . 2) PgBackRest can archive (compressed and encrypted) WAL files to S3. https://pgbackrest.org/user-guide-rhel.html#s3-support > > 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 nod= e >>>> is 1, and the latest WAL file is 100. The standby node has also receiv= ed 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 = the >>>> 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 th= e >>>> 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-arch= ive-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 grabbi= ng >>> from the replica before they get recycled. You can set archive_mode=3Da= lways >>> 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}" = restore >> without giving a second thought to timelines or whether the WAL exists. >> >> Maybe I've just ignored the problem, since it (seemingly) does everythin= g >> for PITR backups. >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000b773b4063c2eb196 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Aug 12, 2025 at 4:37=E2=80=AFAM p= x shi <spxlyy123@gmail.com>= ; wrote:
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 circu= mstances. However, if the primary node crashes under high load, the archive= d WAL logs on S3 may be discontinuous.
1) PG does not purge WAL=C2=A0files that are needed for immedia= te crash recovery.
2) PgBackRest can archive (compressed and encr= ypted) WAL files to S3.=C2=A0=C2=A0https://pgbackrest.org/user-guide-rhel.html#s3-s= upport

=C2=A0

Ron Johnson <ronljohnsonjr@gmail.c= om> =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 <htamfids@gmail.com> wrote:
=
There is a scenario: the current timeline of the Po= stgreSQL primary node is 1, and the latest WAL file is 100. The standby nod= e has also received up to WAL file 100. However, the latest WAL file archiv= ed is only file 80. If the primary node crashes at this point and the stand= by is promoted to the new primary, archiving will resume from file 100 on t= imeline 2. As a result, WAL files from 81 to 100 on timeline 1 will be miss= ing from the archive.
Is there a good solution to prevent this situation= ?

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


Yes, you would lost some ability = for easy PITR for 80-100, but could still be done by resurrecting your cras= hed primary, or carefully grabbing from the replica before they get recycle= d. You can set archive_mode=3Dalways on the replicas to help with this.

Bog-standar= d PgBackRest retains all WAL files required for a full backup set and its a= ssociated differential/incremental backups, no?=C2=A0 I've certainly do= ne more than one=C2=A0--type=3Dtime --target=3D"${RestoreUntil}" = restore without giving a second thought to timelines or whether the WAL exi= sts.

Maybe I've just ignored the problem, sinc= e it (seemingly) does everything for PITR backups.=C2=A0


--
Death to <Redacted>, and butter sa= uce.
Don't boil me, I'm still alive.
<Redacted&= gt; lobster!
--000000000000b773b4063c2eb196--