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 1vEBal-006rWl-In for pgsql-admin@arkaria.postgresql.org; Wed, 29 Oct 2025 19:12:19 +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 1vEBak-003DJQ-Hd for pgsql-admin@arkaria.postgresql.org; Wed, 29 Oct 2025 19:12:17 +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 1vEBak-003DJI-1l for pgsql-admin@lists.postgresql.org; Wed, 29 Oct 2025 19:12:17 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEBag-004w2c-2b for pgsql-admin@lists.postgresql.org; Wed, 29 Oct 2025 19:12:16 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-44588e94decso162097b6e.0 for ; Wed, 29 Oct 2025 12:12:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761765132; x=1762369932; darn=lists.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=x15NwUzURPL9qnXYMtObKIDuRxGwPrBQHQGyvDKK0tU=; b=G7AoqcXtpUa1Q4iOTAqlB9sk82VFA1eGR0uMh5phz+QBk60aFujsmr1mszKJ3hjRPs E0vfzphLBp6+Jf+P/U+PTbg+rGYgSX9FI5z+95BqGUa5ctfxitVrw2iIrSsBJbvmMbdA 95yCxXgn7zUUU1cejli0gAPipAhyeSEpjGwbECmNvC9/65Ok/BbhUhrrpYv/kjj0SABe W2DP0pJJ8zHurQq8oVevSBTuCCX3g0vGFjedkf56XCpRYyXk1gWiFbd+dl47qUdcU4UP 7LToxJvJscRK4xZ+J4LqSZc9fBHoCBELp3zfmfX+r8sNQ+KOP/hiKyPI6y/kK6cEYOex +CmQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761765132; x=1762369932; 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=x15NwUzURPL9qnXYMtObKIDuRxGwPrBQHQGyvDKK0tU=; b=hVSMmrO/K4yeyh9RbhdtlLkiq3wah5pajc4K7vLGk9ERTaqA12U3CkHHIurvpsQLOM e5YLlVyIr8myO+4tSEDvAniJWjBbV6dWCOFn3+fv7lQdMtEIIq/DGpGEeXoNS6aKQVdD 1/qKl6gwausspa5MmFqtoIlCqC//Z9gkX6e/klPs7+s66wvrQbA3YrWk5FVDH5FP34QE kA2KgxnRf2Pe625rDbHsoZ25AEazw2h2fc5o4gVTDScQWvGFmCEcN76WQOOEwkB28I6n GpC/rpd5XBgF6/9CvpWwAaPFDnH1fHo9FvKrXxZaCQdyy0OD97Xs4gX+9ny3nTeTJMs4 cwiA== X-Gm-Message-State: AOJu0YwAicFEJJl9HjAoAWEIbVcYN+h+dalFHhXxumRx+cw6W0S17+XX y4wrH8yoPDvsEX2TUItTekbOC6dAleHUCmJUwi2J5ACmqk6cw8UiWMe8C+5Q8KtPM5w4ROg6PPV +4rxmXnewodccAOAqslX3xa1262tr2rsdmhrx X-Gm-Gg: ASbGncsSzLTchzKKCd1f9qAcJeh1Emi8XC63merLH8x4zzt+WJ7Fl2PA6uKURvdntVo PHtNIEZ/+2jOF3re0p3GzVYr+9QcwoQ3esSwdip9hQ3cZeYhwDYyoVE751dCTON6EZBArLo17A1 NBruAPAS7COLj/Yv+KeSYnztISZHbI1LY0Ileadw86VkEVMY7IcFT8N8a769u3CzZcWGgM5rcXA ONVLfT3+iAfopJZRMsYswBd4+7taUp1tZFJZZ+tG/XBHoGv9WX3OPCrsfZGKmQBburYa9WopA== X-Google-Smtp-Source: AGHT+IFDzqd8RSfLLpMfXTkHUvTCZlFC8T6a1LcrEDa8KMCiB158VKweRzH0mBHK04b0RqEyV6sn2bbfcnoICUjcId8= X-Received: by 2002:a05:6808:15a8:b0:445:a90:b8af with SMTP id 5614622812f47-44f88b904b9mr368272b6e.32.1761765132207; Wed, 29 Oct 2025 12:12:12 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 29 Oct 2025 15:12:01 -0400 X-Gm-Features: AWmQ_bnmloTCUBoXEen1ejTmiZVFD3iv0At6r8h_UpswxNHzt6mS5GbY7Yd92e8 Message-ID: Subject: Re: pg_basebackup --incremental To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000e20b09064250e851" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e20b09064250e851 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 w= rote: > 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 && 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 > wrote: > >> 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 ch= anging >> 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 bot= h >> of those archive_command lines in your config. Always keep "archive_mod= e =3D >> on" but disable it by setting archive_command to /bin/true (which will b= e 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 >>>>> wrote: Howdy, We're running version 17. 6. Would anyone be able to po= int 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, or >>>>>> provide, some sample use cases / scripts / usage to deploy a pg_base= backup >>>>>> 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 i= s >>>>> quite simple and easy to configure if you back up to a local mount po= int >>>>> (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 >>>>> >>>>> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000e20b09064250e851 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 Stearns <sam.s= tearns@dat.com> wrote:
Awesome.=C2=A0 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 && rsync -a %p /postgres_wal_archive/rtsstage/%= f && rsync -a %p postgres@10.36.160.48:/postgres_wal_archive/rtssta= ge/%f'

How does incorporating pgbackrest affec= t that setup?

On Wed, Oct 29, 2025 at 10:53=E2=80=AFAM Ron Johnson <= ;ronljohnsonjr= @gmail.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!
--000000000000e20b09064250e851--