public inbox for [email protected]help / color / mirror / Atom feed
pg_basebackup --incremental 12+ messages / 3 participants [nested] [flat]
* pg_basebackup --incremental @ 2025-10-28 17:42 Sam Stearns <[email protected]> 0 siblings, 2 replies; 12+ messages in thread From: Sam Stearns @ 2025-10-28 17:42 UTC (permalink / raw) To: Pgsql-admin <[email protected]>; +Cc: Avi Vallarapu <[email protected]>; Henry Ashu <[email protected]> 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 strategy as a backup solution, please? This will be used temporarily as we are migrating to RDS soon which is why we're not going with pgBackRest. Thanks, Sam -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-28 17:52 Ron Johnson <[email protected]> parent: Sam Stearns <[email protected]> 1 sibling, 1 reply; 12+ messages in thread From: Ron Johnson @ 2025-10-28 17:52 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Tue, Oct 28, 2025 at 1:43 PM Sam Stearns <[email protected]> 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 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 point (even when that mount point is NFS). This is in the "postgres" crontab: 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log And this is my /etc/pgbackrest.conf: [global] repo1-path=/Database/backups/pgbackrest repo1-cipher-type=aes-256-cbc repo1-cipher-pass=<redacted> repo1-bundle=y repo1-bundle-limit=20MiB repo1-bundle-size=200MiB [nfs] pg1-path=/Database/17/data resume=n start-fast=y stop-auto=y compress-type=zst log-level-console=detail log-level-file=info log-path=/var/lib/pgsql/logs/pgbackrest retention-full=4 process-max=<nproc * 3/4> [nfs:archive-push] compress-type=zst -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-28 22:21 Sam Stearns <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Sam Stearns @ 2025-10-28 22:21 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> Thanks, Ron! We'll take another look at pgBackRest. On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> wrote: > On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. 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 PM Sam Stearns <[email protected]> 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 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 point (even > when that mount point is NFS). > > This is in the "postgres" crontab: > 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type &> > logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log > 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type &> > logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log > > And this is my /etc/pgbackrest.conf: > [global] > repo1-path=/Database/backups/pgbackrest > repo1-cipher-type=aes-256-cbc > repo1-cipher-pass=<redacted> > repo1-bundle=y > repo1-bundle-limit=20MiB > repo1-bundle-size=200MiB > [nfs] > pg1-path=/Database/17/data > resume=n > start-fast=y > stop-auto=y > compress-type=zst > log-level-console=detail > log-level-file=info > log-path=/var/lib/pgsql/logs/pgbackrest > retention-full=4 > process-max=<nproc * 3/4> > [nfs:archive-push] > compress-type=zst > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-29 17:40 Sam Stearns <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Sam Stearns @ 2025-10-29 17:40 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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 PM Sam Stearns <[email protected]> wrote: > Thanks, Ron! We'll take another look at pgBackRest. > > On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> > wrote: > >> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. 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 PM Sam Stearns <[email protected]> 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 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 point (even >> when that mount point is NFS). >> >> This is in the "postgres" crontab: >> 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type &> >> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >> 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type &> >> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >> >> And this is my /etc/pgbackrest.conf: >> [global] >> repo1-path=/Database/backups/pgbackrest >> repo1-cipher-type=aes-256-cbc >> repo1-cipher-pass=<redacted> >> repo1-bundle=y >> repo1-bundle-limit=20MiB >> repo1-bundle-size=200MiB >> [nfs] >> pg1-path=/Database/17/data >> resume=n >> start-fast=y >> stop-auto=y >> compress-type=zst >> log-level-console=detail >> log-level-file=info >> log-path=/var/lib/pgsql/logs/pgbackrest >> retention-full=4 >> process-max=<nproc * 3/4> >> [nfs:archive-push] >> compress-type=zst >> >> -- >> Death to <Redacted>, and butter sauce. >> Don't boil me, I'm still alive. >> <Redacted> lobster! >> > > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-29 17:52 Ron Johnson <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Ron Johnson @ 2025-10-29 17:52 UTC (permalink / raw) To: Pgsql-admin <[email protected]> I'm certain this is all in the User Guide: $ grep -rh archive $PGDATA/postgresql.conf* archive_mode = on #archive_command = '/bin/true' archive_command = 'pgbackrest --stanza=nfs archive-push %p' Since changing archive_mode requires a restart, but changing archive_command just requires a reload, it's useful to have both of those archive_command lines in your config. Always keep "archive_mode = 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 PM Sam Stearns <[email protected]> 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 PM Sam Stearns <[email protected]> wrote: > >> Thanks, Ron! We'll take another look at pgBackRest. >> >> On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> >> wrote: >> >>> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. 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 PM Sam Stearns <[email protected]> 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 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 point (even >>> when that mount point is NFS). >>> >>> This is in the "postgres" crontab: >>> 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type &> >>> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>> 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type &> >>> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>> >>> And this is my /etc/pgbackrest.conf: >>> [global] >>> repo1-path=/Database/backups/pgbackrest >>> repo1-cipher-type=aes-256-cbc >>> repo1-cipher-pass=<redacted> >>> repo1-bundle=y >>> repo1-bundle-limit=20MiB >>> repo1-bundle-size=200MiB >>> [nfs] >>> pg1-path=/Database/17/data >>> resume=n >>> start-fast=y >>> stop-auto=y >>> compress-type=zst >>> log-level-console=detail >>> log-level-file=info >>> log-path=/var/lib/pgsql/logs/pgbackrest >>> retention-full=4 >>> process-max=<nproc * 3/4> >>> [nfs:archive-push] >>> compress-type=zst >>> >>> -- >>> Death to <Redacted>, and butter sauce. >>> Don't boil me, I'm still alive. >>> <Redacted> lobster! >>> >> >> >> -- >> >> Samuel Stearns >> Team Lead - Database >> c: 971 762 6879 | o: 971 762 6879 | DAT.com >> >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-29 18:26 Sam Stearns <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Sam Stearns @ 2025-10-29 18:26 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> Awesome. Thank you, Ron. We have our archive_command set to push WAL's to standby as such: archive_command = 'test ! -f /postgres_wal_archive/rtsstage/%f && rsync -a %p /postgres_wal_archive/rtsstage/%f && rsync -a %p [email protected]: /postgres_wal_archive/rtsstage/%f' How does incorporating pgbackrest affect that setup? On Wed, Oct 29, 2025 at 10:53 AM Ron Johnson <[email protected]> wrote: > I'm certain this is all in the User Guide: $ grep -rh archive > $PGDATA/postgresql. conf* archive_mode = on #archive_command = '/bin/true' > archive_command = 'pgbackrest --stanza=nfs archive-push %p' Since changing > 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 = on > #archive_command = '/bin/true' > archive_command = 'pgbackrest --stanza=nfs archive-push %p' > > Since changing archive_mode requires a restart, but > changing archive_command just requires a reload, it's useful to have both > of those archive_command lines in your config. Always keep "archive_mode = > 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 PM Sam Stearns <[email protected]> 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 PM Sam Stearns <[email protected]> wrote: >> >>> Thanks, Ron! We'll take another look at pgBackRest. >>> >>> On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> >>> wrote: >>> >>>> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. 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 PM Sam Stearns <[email protected]> >>>> 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 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 point (even >>>> when that mount point is NFS). >>>> >>>> This is in the "postgres" crontab: >>>> 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type &> >>>> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>> 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type &> >>>> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>> >>>> And this is my /etc/pgbackrest.conf: >>>> [global] >>>> repo1-path=/Database/backups/pgbackrest >>>> repo1-cipher-type=aes-256-cbc >>>> repo1-cipher-pass=<redacted> >>>> repo1-bundle=y >>>> repo1-bundle-limit=20MiB >>>> repo1-bundle-size=200MiB >>>> [nfs] >>>> pg1-path=/Database/17/data >>>> resume=n >>>> start-fast=y >>>> stop-auto=y >>>> compress-type=zst >>>> log-level-console=detail >>>> log-level-file=info >>>> log-path=/var/lib/pgsql/logs/pgbackrest >>>> retention-full=4 >>>> process-max=<nproc * 3/4> >>>> [nfs:archive-push] >>>> compress-type=zst >>>> >>>> -- >>>> Death to <Redacted>, and butter sauce. >>>> Don't boil me, I'm still alive. >>>> <Redacted> lobster! >>>> >>> >>> >>> -- >>> >>> Samuel Stearns >>> Team Lead - Database >>> c: 971 762 6879 | o: 971 762 6879 | DAT.com >>> >>> >>> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >>> >>> >> >> -- >> >> Samuel Stearns >> Team Lead - Database >> c: 971 762 6879 | o: 971 762 6879 | DAT.com >> >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-29 19:12 Ron Johnson <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Ron Johnson @ 2025-10-29 19:12 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 <insert 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 PM Sam Stearns <[email protected]> wrote: > Awesome. Thank you, Ron. > > We have our archive_command set to push WAL's to standby as such: > > archive_command = 'test ! -f /postgres_wal_archive/rtsstage/%f && rsync -a > %p /postgres_wal_archive/rtsstage/%f && rsync -a %p [email protected]: > /postgres_wal_archive/rtsstage/%f' > > How does incorporating pgbackrest affect that setup? > > On Wed, Oct 29, 2025 at 10:53 AM Ron Johnson <[email protected]> > wrote: > >> I'm certain this is all in the User Guide: $ grep -rh archive >> $PGDATA/postgresql. conf* archive_mode = on #archive_command = '/bin/true' >> archive_command = 'pgbackrest --stanza=nfs archive-push %p' Since changing >> 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 = on >> #archive_command = '/bin/true' >> archive_command = 'pgbackrest --stanza=nfs archive-push %p' >> >> Since changing archive_mode requires a restart, but >> changing archive_command just requires a reload, it's useful to have both >> of those archive_command lines in your config. Always keep "archive_mode = >> 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 PM Sam Stearns <[email protected]> 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 PM Sam Stearns <[email protected]> wrote: >>> >>>> Thanks, Ron! We'll take another look at pgBackRest. >>>> >>>> On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> >>>> wrote: >>>> >>>>> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. 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 PM Sam Stearns <[email protected]> >>>>> 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 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 point >>>>> (even when that mount point is NFS). >>>>> >>>>> This is in the "postgres" crontab: >>>>> 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type >>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>> 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type >>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>> >>>>> And this is my /etc/pgbackrest.conf: >>>>> [global] >>>>> repo1-path=/Database/backups/pgbackrest >>>>> repo1-cipher-type=aes-256-cbc >>>>> repo1-cipher-pass=<redacted> >>>>> repo1-bundle=y >>>>> repo1-bundle-limit=20MiB >>>>> repo1-bundle-size=200MiB >>>>> [nfs] >>>>> pg1-path=/Database/17/data >>>>> resume=n >>>>> start-fast=y >>>>> stop-auto=y >>>>> compress-type=zst >>>>> log-level-console=detail >>>>> log-level-file=info >>>>> log-path=/var/lib/pgsql/logs/pgbackrest >>>>> retention-full=4 >>>>> process-max=<nproc * 3/4> >>>>> [nfs:archive-push] >>>>> compress-type=zst >>>>> >>>>> -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-29 19:19 Sam Stearns <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Sam Stearns @ 2025-10-29 19:19 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> Thanks, Ron. Very helpful. On Wed, Oct 29, 2025 at 12:12 PM Ron Johnson <[email protected]> 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 13: > 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 <insert > 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 PM Sam Stearns <[email protected]> wrote: > >> Awesome. Thank you, Ron. >> >> We have our archive_command set to push WAL's to standby as such: >> >> archive_command = 'test ! -f /postgres_wal_archive/rtsstage/%f && rsync >> -a %p /postgres_wal_archive/rtsstage/%f && rsync -a %p >> [email protected]:/postgres_wal_archive/rtsstage/%f' >> >> How does incorporating pgbackrest affect that setup? >> >> On Wed, Oct 29, 2025 at 10:53 AM Ron Johnson <[email protected]> >> wrote: >> >>> I'm certain this is all in the User Guide: $ grep -rh archive >>> $PGDATA/postgresql. conf* archive_mode = on #archive_command = '/bin/true' >>> archive_command = 'pgbackrest --stanza=nfs archive-push %p' Since changing >>> 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 = on >>> #archive_command = '/bin/true' >>> archive_command = 'pgbackrest --stanza=nfs archive-push %p' >>> >>> Since changing archive_mode requires a restart, but >>> changing archive_command just requires a reload, it's useful to have both >>> of those archive_command lines in your config. Always keep "archive_mode = >>> 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 PM Sam Stearns <[email protected]> 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 PM Sam Stearns <[email protected]> >>>> wrote: >>>> >>>>> Thanks, Ron! We'll take another look at pgBackRest. >>>>> >>>>> On Tue, Oct 28, 2025 at 10:52 AM Ron Johnson <[email protected]> >>>>> wrote: >>>>> >>>>>> On Tue, Oct 28, 2025 at 1: 43 PM Sam Stearns <sam. stearns@ dat. >>>>>> 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 PM Sam Stearns <[email protected]> >>>>>> 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 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 point >>>>>> (even when that mount point is NFS). >>>>>> >>>>>> This is in the "postgres" crontab: >>>>>> 15 01 * * Sun Type=full; pgbackrest backup --stanza=nfs --type=$Type >>>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>>> 15 01 * * 1-6 Type=incr; pgbackrest backup --stanza=nfs --type=$Type >>>>>> &> logs/pgbackrest_$(date +"\%F_\%T")_${Type}.log >>>>>> >>>>>> And this is my /etc/pgbackrest.conf: >>>>>> [global] >>>>>> repo1-path=/Database/backups/pgbackrest >>>>>> repo1-cipher-type=aes-256-cbc >>>>>> repo1-cipher-pass=<redacted> >>>>>> repo1-bundle=y >>>>>> repo1-bundle-limit=20MiB >>>>>> repo1-bundle-size=200MiB >>>>>> [nfs] >>>>>> pg1-path=/Database/17/data >>>>>> resume=n >>>>>> start-fast=y >>>>>> stop-auto=y >>>>>> compress-type=zst >>>>>> log-level-console=detail >>>>>> log-level-file=info >>>>>> log-path=/var/lib/pgsql/logs/pgbackrest >>>>>> retention-full=4 >>>>>> process-max=<nproc * 3/4> >>>>>> [nfs:archive-push] >>>>>> compress-type=zst >>>>>> >>>>>> > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-31 10:28 Serkan Akdemir <[email protected]> parent: Sam Stearns <[email protected]> 1 sibling, 1 reply; 12+ messages in thread From: Serkan Akdemir @ 2025-10-31 10:28 UTC (permalink / raw) To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Avi Vallarapu <[email protected]>; Henry Ashu <[email protected]> I created a script for incremental backup+merge on PostgreSQL 17+ It creates a full backup and later (next run) incremental backups. Incremental backups are merged to full backup with script, so you have always updated full backup. Disclaimer: use it at your own risk. Simple usage is: # first backup should be full pg_backup.sh --mode=fulll # then inc pg_backup.sh --mode=fincremental <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; medium-resources/pg_backup.sh at main · rdnkrkmz/medium-resources<https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; github.com<https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; [apple-touch-icon-180x180-a80b8e11abe2.png]<https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; On 28 Oct 2025, at 20:42, Sam Stearns <[email protected]> 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 strategy as a backup solution, please? This will be used temporarily as we are migrating to RDS soon which is why we're not going with pgBackRest. Thanks, Sam -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com [https://lh7-rt.googleusercontent.com/docsz/AD_4nXchMTXZiU3P-OkN7UwPyrdxiCAOkHWIArmSrehhAl-cXaYxECakb...]<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; Attachments: [image/png] apple-touch-icon-180x180-a80b8e11abe2.png (20.6K, 3-apple-touch-icon-180x180-a80b8e11abe2.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-31 14:25 Sam Stearns <[email protected]> parent: Serkan Akdemir <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Sam Stearns @ 2025-10-31 14:25 UTC (permalink / raw) To: Serkan Akdemir <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Avi Vallarapu <[email protected]>; Henry Ashu <[email protected]> Thanks very much, Serkan. We'll have a look. On Fri, Oct 31, 2025 at 3:28 AM Serkan Akdemir <[email protected]> wrote: > I created a script for incremental backup+merge on PostgreSQL 17+ It > creates a full backup and later (next run) incremental backups. Incremental > backups are merged to full backup with script, so you have always updated > full backup. Disclaimer: > ZjQcmQRYFpfptBannerStart > This Message Is From an Untrusted Sender > You have not previously corresponded with this sender. > > ZjQcmQRYFpfptBannerEnd > I created a script for incremental backup+merge on PostgreSQL 17+ > It creates a full backup and later (next run) incremental backups. > Incremental backups are merged to full backup with script, so you have > always updated full backup. > Disclaimer: use it at your own risk. > > Simple usage is: > # first backup should be full > pg_backup.sh --mode=fulll > > # then inc > pg_backup.sh --mode=fincremental > > medium-resources/pg_backup.sh at main · rdnkrkmz/medium-resources > <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; > github.com > <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; > [image: apple-touch-icon-180x180-a80b8e11abe2.png] > <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; > <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; > > > On 28 Oct 2025, at 20:42, Sam Stearns <[email protected]> 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 strategy as a backup solution, please? > > This will be used temporarily as we are migrating to RDS soon which is why > we're not going with pgBackRest. > > Thanks, > > Sam > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; Attachments: [image/png] apple-touch-icon-180x180-a80b8e11abe2.png (20.6K, 3-apple-touch-icon-180x180-a80b8e11abe2.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-31 15:16 Ron Johnson <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Ron Johnson @ 2025-10-31 15:16 UTC (permalink / raw) To: Pgsql-admin <[email protected]> Note that pg_basebackup is single-threaded, and not encrypted. On Fri, Oct 31, 2025 at 10:26 AM Sam Stearns <[email protected]> wrote: > Thanks very much, Serkan. We'll have a look. > > On Fri, Oct 31, 2025 at 3:28 AM Serkan Akdemir <[email protected]> > wrote: > >> I created a script for incremental backup+merge on PostgreSQL 17+ It >> creates a full backup and later (next run) incremental backups. Incremental >> backups are merged to full backup with script, so you have always updated >> full backup. Disclaimer: >> ZjQcmQRYFpfptBannerStart >> This Message Is From an Untrusted Sender >> You have not previously corresponded with this sender. >> >> ZjQcmQRYFpfptBannerEnd >> I created a script for incremental backup+merge on PostgreSQL 17+ >> It creates a full backup and later (next run) incremental backups. >> Incremental backups are merged to full backup with script, so you have >> always updated full backup. >> Disclaimer: use it at your own risk. >> >> Simple usage is: >> # first backup should be full >> pg_backup.sh --mode=fulll >> >> # then inc >> pg_backup.sh --mode=fincremental >> >> medium-resources/pg_backup.sh at main · rdnkrkmz/medium-resources >> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >> github.com >> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >> [image: apple-touch-icon-180x180-a80b8e11abe2.png] >> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >> >> >> On 28 Oct 2025, at 20:42, Sam Stearns <[email protected]> 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 strategy as a backup solution, please? >> >> This will be used temporarily as we are migrating to RDS soon which is >> why we're not going with pgBackRest. >> >> Thanks, >> >> Sam >> >> -- >> >> Samuel Stearns >> Team Lead - Database >> c: 971 762 6879 | o: 971 762 6879 | DAT.com >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> >> > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! Attachments: [image/png] apple-touch-icon-180x180-a80b8e11abe2.png (20.6K, 3-apple-touch-icon-180x180-a80b8e11abe2.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_basebackup --incremental @ 2025-10-31 15:33 Sam Stearns <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Sam Stearns @ 2025-10-31 15:33 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> Thanks, Ron. On Fri, Oct 31, 2025 at 8:17 AM Ron Johnson <[email protected]> wrote: > Note that pg_basebackup is single-threaded, and not encrypted. On Fri, Oct > 31, 2025 at 10: 26 AM Sam Stearns <sam. stearns@ dat. com> wrote: Thanks > very much, Serkan. We'll have a look. On Fri, Oct 31, 2025 at 3: 28 AM > Serkan Akdemir <osmosyum@ hotmail. com> > ZjQcmQRYFpfptBannerStart > This Message Is From an External Sender > This message came from outside your organization. > > ZjQcmQRYFpfptBannerEnd > Note that pg_basebackup is single-threaded, and not encrypted. > > On Fri, Oct 31, 2025 at 10:26 AM Sam Stearns <[email protected]> wrote: > >> Thanks very much, Serkan. We'll have a look. >> >> On Fri, Oct 31, 2025 at 3:28 AM Serkan Akdemir <[email protected]> >> wrote: >> >>> I created a script for incremental backup+merge on PostgreSQL 17+ It >>> creates a full backup and later (next run) incremental backups. Incremental >>> backups are merged to full backup with script, so you have always updated >>> full backup. Disclaimer: >>> ZjQcmQRYFpfptBannerStart >>> This Message Is From an Untrusted Sender >>> You have not previously corresponded with this sender. >>> >>> ZjQcmQRYFpfptBannerEnd >>> I created a script for incremental backup+merge on PostgreSQL 17+ >>> It creates a full backup and later (next run) incremental backups. >>> Incremental backups are merged to full backup with script, so you have >>> always updated full backup. >>> Disclaimer: use it at your own risk. >>> >>> Simple usage is: >>> # first backup should be full >>> pg_backup.sh --mode=fulll >>> >>> # then inc >>> pg_backup.sh --mode=fincremental >>> >>> medium-resources/pg_backup.sh at main · rdnkrkmz/medium-resources >>> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >>> github.com >>> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >>> [image: apple-touch-icon-180x180-a80b8e11abe2.png] >>> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >>> <https://github.com/rdnkrkmz/medium-resources/blob/main/pg_backup.sh; >>> >>> >>> On 28 Oct 2025, at 20:42, Sam Stearns <[email protected]> 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 strategy as a backup solution, please? >>> >>> This will be used temporarily as we are migrating to RDS soon which is >>> why we're not going with pgBackRest. >>> >>> Thanks, >>> >>> Sam >>> >>> -- >>> >>> Samuel Stearns >>> Team Lead - Database >>> c: 971 762 6879 | o: 971 762 6879 | DAT.com >>> >>> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >>> >>> >>> >> >> -- >> >> Samuel Stearns >> Team Lead - Database >> c: 971 762 6879 | o: 971 762 6879 | DAT.com >> >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; Attachments: [image/png] apple-touch-icon-180x180-a80b8e11abe2.png (20.6K, 3-apple-touch-icon-180x180-a80b8e11abe2.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-10-31 15:33 UTC | newest] Thread overview: 12+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-28 17:42 pg_basebackup --incremental Sam Stearns <[email protected]> 2025-10-28 17:52 ` Ron Johnson <[email protected]> 2025-10-28 22:21 ` Sam Stearns <[email protected]> 2025-10-29 17:40 ` Sam Stearns <[email protected]> 2025-10-29 17:52 ` Ron Johnson <[email protected]> 2025-10-29 18:26 ` Sam Stearns <[email protected]> 2025-10-29 19:12 ` Ron Johnson <[email protected]> 2025-10-29 19:19 ` Sam Stearns <[email protected]> 2025-10-31 10:28 ` Serkan Akdemir <[email protected]> 2025-10-31 14:25 ` Sam Stearns <[email protected]> 2025-10-31 15:16 ` Ron Johnson <[email protected]> 2025-10-31 15:33 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox