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 1vEBi7-006t1M-DC for pgsql-admin@arkaria.postgresql.org; Wed, 29 Oct 2025 19:19:55 +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 1vEBi5-003Gwn-Vf for pgsql-admin@arkaria.postgresql.org; Wed, 29 Oct 2025 19:19:53 +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 1vEBi5-003GwZ-Ay for pgsql-admin@lists.postgresql.org; Wed, 29 Oct 2025 19:19:52 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vEBhv-004R4g-2W for pgsql-admin@lists.postgresql.org; Wed, 29 Oct 2025 19:19:45 +0000 Received: from pps.filterd (m0209981.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.11/8.18.1.11) with ESMTP id 59TIq3wH1138185 for ; Wed, 29 Oct 2025 12:19:41 -0700 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dat.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=ppdk230404; bh=xAtEVpJgdZuoNy1qy/nnoyB UI9CRA89QbaSjTOX2QG8=; b=b9a7xMVjWAs8lbcXRq2x30IabqUT9IKzOZRQai6 yuxC7eh4VtAYOsYfD+WI+nkc2yldbCI/nHh33E3Gs+lbSWbJscPkDIaj20LwEk5R C7H311avEGJ9cS4GwPUsNCUvBmo/gvE9mOXnfIOgnDfvf/uIuxnbwYJhQlx8XcEs exyTcubwDVKGsTHssUkP5l+/3LJig+gmYzFYNCGf3pIxwx2xrOsEdp3ym9l6OH0K U6+zuGtRAQNR7CKvj/uFlpNBA2gI6MA0wX5/d6sfvMw/TkdYLvXr2NEnbf2g6Aox NFDbKx8MBYuqH2ErcHhjkeJxzzwmCOq6rOicvsWkqJ3nVpQ== Received: from mail-ed1-f71.google.com (mail-ed1-f71.google.com [209.85.208.71]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 4a349g1xyx-1 (version=TLSv1.3 cipher=TLS_AES_128_GCM_SHA256 bits=128 verify=NOT) for ; Wed, 29 Oct 2025 12:19:41 -0700 (PDT) Received: by mail-ed1-f71.google.com with SMTP id 4fb4d7f45d1cf-63c588cb6d5so124190a12.1 for ; Wed, 29 Oct 2025 12:19:41 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761765578; x=1762370378; 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=xAtEVpJgdZuoNy1qy/nnoyBUI9CRA89QbaSjTOX2QG8=; b=OCNEqa3O1YN4mhmDK+Lo4DVrZ975WfyTHfijZDayQ9iy/ZZ86T1wuVzrVAceR9Kyvz 2xpXFNgYilFwYJs7gkShBrLTmFGMpjwRZp50TXJS2LweC9nA7vUTvXAtCsuPUOpf1kee HfiwcvKUgbpYoYV6nmowgBE3ngj1CSTOCNOdeWz+7N+hzz5Fi90ZfTI09aNowawz8W/D ThAyuu5PYIZSTVAHnX/EZu1Jee1uchAMHHuTiuZDGVUUzs7EiLAviMn4C1FVII/dhiJv SizBeMjjYiCJYEDXbB/ToLXEKWMGqz2HGvn8EG+krGXe8PNI6GMkdp3aU65w1wwMtgeH GXjg== X-Gm-Message-State: AOJu0YxOyual07bC4dEVvXYS5VYH/lj+lugkWQFIFPjClK5yVAqUrOJO Po2ZmIwdOzm6ZBxEMfDtj3scYlkafQdTHd3+QEHYp3aXrH+8st3AgMFXrioyxdaKHwnlXc00AqI 1uZmcTZIfh8xqgYyRvDeo2GyDv54xD+crqnr/V80qgIge7r0XDEXp91RaXQWRw7sXPBnAFGn+vG euJQIBA+mDszG+5IV+vgxehL/hHIpg61qjCLNXZAkVMyg2J8ZODBWbPwReF0u9ws8gg9fxTUU= X-Gm-Gg: ASbGncvBPIv5+MAmbsoIubT8DsWpF6sX3SvIWlkUSTCHZEjH253ENTemFEnhy7l6H8l QMY7zbdyZmIwym1gAzx/pidfIZvyWbEy6fH3ygWYf2L206fe28NZ3Kz358akbfSPJTp9hoCgJN2 1FQBw0QResPBgIxMTgfKGnsNynzpqW5pHD8vxtMC7/2egcJkI7re0Z9A== X-Received: by 2002:a05:6402:1d4d:b0:62a:a4f0:7e4f with SMTP id 4fb4d7f45d1cf-6404427865emr2896104a12.29.1761765577889; Wed, 29 Oct 2025 12:19:37 -0700 (PDT) X-Google-Smtp-Source: AGHT+IFoSY00h6Fx9XBNjtm4VRLHnZUAxitKAWWmBt8Z5GTlhRmZ2ytDh4Kqs7UGi7dylPHnG38DJsg6MpwpEj50BJ8= X-Received: by 2002:a05:6402:1d4d:b0:62a:a4f0:7e4f with SMTP id 4fb4d7f45d1cf-6404427865emr2896064a12.29.1761765576840; Wed, 29 Oct 2025 12:19:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Stearns Date: Wed, 29 Oct 2025 12:19:25 -0700 X-Gm-Features: AWmQ_bmK--qQaOHI_mF2ASCdURIlQFU-eFiNzM_pjqRIbQBvHh8Q2OqRIHAprpA Message-ID: Subject: Re: pg_basebackup --incremental To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000062a737064251030b" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.100.49 definitions=2025-10-29_07,2025-10-29_03,2025-10-01_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000062a737064251030b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks, Ron. Very helpful. On Wed, Oct 29, 2025 at 12:12=E2=80=AFPM Ron Johnson wrote: > pgbackrest does WAL archiving for you, as seen here: $ dir > /Database/backups/pgbackrest/ total 64 drwxr-x--- 3 nobody nobody 21 > 2024-06-08 21: 35: 11 archive/ drwxr-x--- 3 nobody nobody 21 2025-06-11 1= 3: > 04: 10 backup/ Thus, it obviates the need > ZjQcmQRYFpfptBannerStart > This Message Is From an External Sender > This message came from outside your organization. > > ZjQcmQRYFpfptBannerEnd > pgbackrest does WAL archiving for you, as seen here: > $ dir /Database/backups/pgbackrest/ > total 64 > drwxr-x--- 3 nobody nobody 21 2024-06-08 21:35:11 archive/ > drwxr-x--- 3 nobody nobody 21 2025-06-11 13:04:10 backup/ > > Thus, it obviates the need for the rsync commands. > > If, like me, you still want the WAL files to *also* be somewhere else, > then create a cron job to rsync {repo1-path}/pgbackrest/archive to destination here> every15 minutes or so. Add "--del" to remove the WALs > that pgbackrest deletes when it purges the oldest saveset. > > On Wed, Oct 29, 2025 at 2:26=E2=80=AFPM Sam Stearns = wrote: > >> Awesome. Thank you, Ron. >> >> We have our archive_command set to push WAL's to standby as such: >> >> archive_command =3D 'test ! -f /postgres_wal_archive/rtsstage/%f && rsyn= c >> -a %p /postgres_wal_archive/rtsstage/%f && rsync -a %p >> postgres@10.36.160.48:/postgres_wal_archive/rtsstage/%f' >> >> How does incorporating pgbackrest affect that setup? >> >> On Wed, Oct 29, 2025 at 10:53=E2=80=AFAM Ron Johnson >> wrote: >> >>> I'm certain this is all in the User Guide: $ grep -rh archive >>> $PGDATA/postgresql. conf* archive_mode =3D on #archive_command =3D '/bi= n/true' >>> archive_command =3D 'pgbackrest --stanza=3Dnfs archive-push %p' Since c= hanging >>> archive_mode >>> ZjQcmQRYFpfptBannerStart >>> This Message Is From an External Sender >>> This message came from outside your organization. >>> >>> ZjQcmQRYFpfptBannerEnd >>> I'm certain this is all in the User Guide: >>> $ grep -rh archive $PGDATA/postgresql.conf* >>> archive_mode =3D on >>> #archive_command =3D '/bin/true' >>> archive_command =3D 'pgbackrest --stanza=3Dnfs archive-push %p' >>> >>> Since changing archive_mode requires a restart, but >>> changing archive_command just requires a reload, it's useful to have bo= th >>> of those archive_command lines in your config. Always keep "archive_mo= de =3D >>> on" but disable it by setting archive_command to /bin/true (which will = be a >>> rare occurrence). >>> >>> On Wed, Oct 29, 2025 at 1:40=E2=80=AFPM Sam Stearns wrote: >>> >>>> Hi Ron, >>>> >>>> If I may, please. What are the postgres.conf parameters you set >>>> specifically for pgBackRest? >>>> >>>> Thanks, >>>> >>>> Sam >>>> >>>> On Tue, Oct 28, 2025 at 3:21=E2=80=AFPM Sam Stearns >>>> wrote: >>>> >>>>> Thanks, Ron! We'll take another look at pgBackRest. >>>>> >>>>> On Tue, Oct 28, 2025 at 10:52=E2=80=AFAM Ron Johnson >>>>> wrote: >>>>> >>>>>> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns >>>>> com> wrote: Howdy, We're running version 17. 6. Would anyone be able= to >>>>>> point me to, or provide, some sample use cases / scripts / usage to = deploy >>>>>> a pg_basebackup full + --incremental >>>>>> ZjQcmQRYFpfptBannerStart >>>>>> This Message Is From an External Sender >>>>>> This message came from outside your organization. >>>>>> >>>>>> ZjQcmQRYFpfptBannerEnd >>>>>> On Tue, Oct 28, 2025 at 1:43=E2=80=AFPM Sam Stearns >>>>>> wrote: >>>>>> >>>>>>> Howdy, >>>>>>> >>>>>>> We're running version 17.6. Would anyone be able to point me to, o= r >>>>>>> provide, some sample use cases / scripts / usage to deploy a pg_bas= ebackup >>>>>>> full + --incremental strategy as a backup solution, please? >>>>>>> >>>>>> >>>>>> The question confuses me a bit (though maybe because weekly "full", >>>>>> and remainder "incremental" is pretty standard). PgBackRest really = is >>>>>> quite simple and easy to configure if you back up to a local mount p= oint >>>>>> (even when that mount point is NFS). >>>>>> >>>>>> This is in the "postgres" crontab: >>>>>> 15 01 * * Sun Type=3Dfull; pgbackrest backup --stanza=3Dnfs --type= =3D$Type >>>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>>> 15 01 * * 1-6 Type=3Dincr; pgbackrest backup --stanza=3Dnfs --type= =3D$Type >>>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>>> >>>>>> And this is my /etc/pgbackrest.conf: >>>>>> [global] >>>>>> repo1-path=3D/Database/backups/pgbackrest >>>>>> repo1-cipher-type=3Daes-256-cbc >>>>>> repo1-cipher-pass=3D >>>>>> repo1-bundle=3Dy >>>>>> repo1-bundle-limit=3D20MiB >>>>>> repo1-bundle-size=3D200MiB >>>>>> [nfs] >>>>>> pg1-path=3D/Database/17/data >>>>>> resume=3Dn >>>>>> start-fast=3Dy >>>>>> stop-auto=3Dy >>>>>> compress-type=3Dzst >>>>>> log-level-console=3Ddetail >>>>>> log-level-file=3Dinfo >>>>>> log-path=3D/var/lib/pgsql/logs/pgbackrest >>>>>> retention-full=3D4 >>>>>> process-max=3D >>>>>> [nfs:archive-push] >>>>>> compress-type=3Dzst >>>>>> >>>>>> > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --00000000000062a737064251030b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks, Ron.=C2=A0 Very helpful.

On W= ed, Oct 29, 2025 at 12:12=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
pgbackrest does WAL archiving for you, as seen here: $ dir /Database/backup= s/pgbackrest/ total 64 drwxr-x--- 3 nobody nobody 21 2024-06-08 21:=E2=80= =8A35:=E2=80=8A11 archive/ drwxr-x--- 3 nobody nobody 21 2025-06-11 13:=E2= =80=8A04:=E2=80=8A10 backup/ Thus, it obviates the need
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
ZjQcmQRYFpfptBannerEnd
pgbackrest does WAL archiving for you, as seen here:<= /div>
$ dir /Database/backups/pgbackrest/
t= otal 64
drwxr-x--- 3 nobody nobody 21 2024-06-08 21:35:11 archive/
dr= wxr-x--- 3 nobody nobody 21 2025-06-11 13:04:10 backup/
Thus, it obviates the need for the rsync commands.
<= br>
If, like me, you still want the WAL files to also=C2= =A0be somewhere else, then create a cron job to rsync {repo1-path}/pgbackre= st/archive to <insert destination here> every15 minutes or so. Add &q= uot;--del" to remove the WALs that=C2=A0pgbackrest deletes when it pur= ges the=C2=A0oldest saveset.

On Wed, Oct 29, 2025 at 2:26=E2=80=AFPM Sam S= tearns <sam.ste= arns@dat.com> wrote:
Awesome.=C2=A0 Thank you, Ron.

<= div>We have our archive_command set to push WAL's to standby as such:

archive_command =3D 'test ! -f /postgres_wal_ar= chive/rtsstage/%f && rsync -a %p /postgres_wal_archive/rtsstage/%f = && rsync -a %p postgres@10.36.160.48:/postgres_wal_archive/rtsstage= /%f'

How does incorporating pgbackrest affect = that setup?

On Wed, Oct 29, 2025 at 10:53=E2=80=AFAM Ron Johnson <<= a href=3D"mailto:ronljohnsonjr@gmail.com" target=3D"_blank">ronljohnsonjr@g= mail.com> wrote:
I'm certain this is all in the User Guide: $ grep -rh archive $PGDATA/p= ostgresql.=E2=80=8Aconf* archive_mode =3D on #archive_command =3D '/bin= /true' archive_command =3D 'pgbackrest --stanza=3Dnfs archive-push = %p' Since changing archive_mode
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
ZjQcmQRYFpfptBannerEnd
I'm certain this is all in the User Guide:
<= div>$ grep -rh archive $PGDATA/postgresql.conf*archive_mode =3D on
#archive_command =3D '/bin/true'
archive= _command =3D 'pgbackrest --stanza=3Dnfs archive-push %p'
=
Since changing archive_mode requires a restart, but changing= =C2=A0archive_command just requires a reload, it's useful to have both = of those archive_command lines in your config.=C2=A0 Always keep "arch= ive_mode =3D on" but disable it by setting archive_command to=C2=A0/bi= n/true (which will be a rare=C2=A0occurrence).

On Wed, Oct 29, 2025 at 1:40=E2=80=AFPM Sam Stearns &l= t;sam.stearns@dat.= com> wrote:
Hi Ron,

If I = may, please.=C2=A0 What are the postgres.conf parameters you set specifical= ly for pgBackRest?

Thanks,

Sam

On Tue, Oct 28, 2025 at 3:21=E2=80=AFPM Sam Stearns <sam.stearns@dat.com&g= t; wrote:
Thanks, Ron!=C2=A0 We'll take another look at pgBackRest.
On = Tue, Oct 28, 2025 at 10:52=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wro= te:
On Tue, Oct 28, 2025 at 1:=E2=80=8A43 PM Sam Stearns <sam.=E2=80=8Astear= ns@=E2=80=8Adat.=E2=80=8Acom> wrote: Howdy, We're running version 17= .=E2=80=8A6. Would anyone be able to point me to, or provide, some sample u= se cases / scripts / usage to deploy a pg_basebackup full + --incremental
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
ZjQcmQRYFpfptBannerEnd
On Tue, Oct 28, 2025 at 1:43=E2=80=AFPM S= am Stearns <sam= .stearns@dat.com> wrote:
Howdy,

We're running version=C2=A017.6.=C2=A0 Would=C2=A0any= one be able to point me to, or provide, some sample use cases / scripts / u= sage to deploy a pg_basebackup full=C2=A0+ --incremental strategy as a back= up solution, please?

The question confuses me a bit (though maybe because weekly "fu= ll", and remainder "incremental" is pretty standard).=C2=A0 = PgBackRest really is quite simple and easy to configure if you back up to a= local mount point (even when that mount point is NFS).

This is in the "postgres" crontab:
15 01 * * Sun Type=3Dfull; pgbackrest backup --stanza=3Dnfs --ty= pe=3D$Type &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.l= og
15 01 * * 1-6 Type=3Dincr; pgbackrest backup --stanza=3Dnfs --type=3D= $Type &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log

And this is my /etc/pgbackrest.conf:
[global]
repo1-path=3D/Database/backups/p= gbackrest
repo1-cipher-type=3Daes-256-cbc
repo1-cipher-pass=3D<red= acted>
repo1-bundle=3Dy
repo1-bundle-limit=3D20MiB
repo1-bundle= -size=3D200MiB
[nfs]
pg1-path=3D/Database/17/data
resume=3Dn
st= art-fast=3Dy
stop-auto=3Dy
compress-type=3Dzst
log-level-console= =3Ddetail
log-level-file=3Dinfo
log-path=3D/var/lib/pgsql/logs/pgback= rest
retention-full=3D4
process-max=3D<nproc * 3/4>
[nfs:arc= hive-push]
compress-type=3Dzst


--
Death to <Redacted>, and butter sauce.
Don't boil me= , I'm still alive.
<Redacted> lobster!


--

Samuel Stearns
Team Lead - Databas= e
c: 971 76= 2 6879 = | o: 97= 1 762 6879 | D= AT.com


--00000000000062a737064251030b--