pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbackrest periodic WAL backups?
7+ messages / 4 participants
[nested] [flat]
* pgbackrest periodic WAL backups?
@ 2018-12-09 16:05 Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Ron @ 2018-12-09 16:05 UTC (permalink / raw)
To: pgsql-admin
Hi,
I've got a full pgbackrest backup in crontab that runs weekly, and a diff
backup that runs nightly. However, I can't find any way to do hourly WAL
backups.
Or does "archive_command = 'pgbackrest --stanza=demo archive-push %p'" just
keep things automatically caught up, obviating the need for periodic WAL
backups (as are needed in other RDBMSs)?
Thanks
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-09 19:10 Stephen Frost <sfrost@snowman.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Stephen Frost @ 2018-12-09 19:10 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
Greetings,
* Ron (ronljohnsonjr@gmail.com) wrote:
> I've got a full pgbackrest backup in crontab that runs weekly, and a diff
> backup that runs nightly. However, I can't find any way to do hourly WAL
> backups.
>
> Or does "archive_command = 'pgbackrest --stanza=demo archive-push %p'" just
> keep things automatically caught up, obviating the need for periodic WAL
> backups (as are needed in other RDBMSs)?
Yes and no. With the archive command, every WAL archived will be pushed
to the archive more-or-less immediately (or, at least, as fast as your
system can get it there, if you enable parallel archiving in
pgbackrest), however, WAL files have a size (by default 16MB) and if you
have a very slowly changing system then it could possibly be hours
between WALs being pushed to the archive by archive_command.
You can address that risk by enabling archive_timeout, which will make
PostgreSQL archive the WAL file even if it isn't full after a certain
amount of time, reducing the potential data loss window. pgBackRest
will compress the WAL file, but that might not be as effective as you'd
like on older versions of PG because WAL files used to have either
potentially a bunch of garbage at the end (due to being recycled) or
possibly WAL headers through the end of the archive (which is better
than garbage but still not free). We've finally, I believe, got it such
that the end of WAL is zero'd out, leading to very little overhead from
having a low archive_timeout value set, but I don't recall offhand if
that got into v11 or if it's coming in v12.
We're looking at improving pgBackRest to actually scan the WAL file
internals itself to identify the end-of-WAL and possibly eliminate the
garbage or WAL headers at the end of a not-full WAL segment but it isn't
very high on the list currently.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-09 22:24 Ron <ronljohnsonjr@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 7+ messages in thread
From: Ron @ 2018-12-09 22:24 UTC (permalink / raw)
To: pgsql-admin
On 12/09/2018 01:10 PM, Stephen Frost wrote:
> Greetings,
>
> * Ron (ronljohnsonjr@gmail.com) wrote:
>> I've got a full pgbackrest backup in crontab that runs weekly, and a diff
>> backup that runs nightly. However, I can't find any way to do hourly WAL
>> backups.
>>
>> Or does "archive_command = 'pgbackrest --stanza=demo archive-push %p'" just
>> keep things automatically caught up, obviating the need for periodic WAL
>> backups (as are needed in other RDBMSs)?
> Yes and no. With the archive command, every WAL archived will be pushed
> to the archive more-or-less immediately (or, at least, as fast as your
> system can get it there, if you enable parallel archiving in
> pgbackrest), however, WAL files have a size (by default 16MB) and if you
> have a very slowly changing system then it could possibly be hours
> between WALs being pushed to the archive by archive_command.
>
> You can address that risk by enabling archive_timeout, which will make
> PostgreSQL archive the WAL file even if it isn't full after a certain
> amount of time, reducing the potential data loss window.
I thought checkpoint_timeout forced a WAL switch.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-10 05:16 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Shreeyansh Dba @ 2018-12-10 05:16 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
Hi Ron,
Go through this link. hope this will help you.
https://pgbackrest.org/configuration.html#section-archive/option-archive-timeout
<http://www.shreeyansh.com;
On Sun, Dec 9, 2018 at 9:35 PM Ron <ronljohnsonjr@gmail.com> wrote:
> Hi,
>
> I've got a full pgbackrest backup in crontab that runs weekly, and a diff
> backup that runs nightly. However, I can't find any way to do hourly WAL
> backups.
>
> Or does "archive_command = 'pgbackrest --stanza=demo archive-push %p'"
> just
> keep things automatically caught up, obviating the need for periodic WAL
> backups (as are needed in other RDBMSs)?
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-10 13:31 David Steele <david@pgmasters.net>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David Steele @ 2018-12-10 13:31 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
On 12/10/18 12:16 AM, Shreeyansh Dba wrote:
>
> Go through this link. hope this will help you.
This option is not helpful in this case. It defines the amount of time
that pgBackRest will wait for WAL to arrive in the archive for the check
and backup commands.
The Postgres option archive_timeout will do the trick. There's some
discussion of that here:
https://pgbackrest.org/user-guide.html#quickstart/configure-archiving
Regards,
--
-David
david@pgmasters.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-10 13:37 David Steele <david@pgmasters.net>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Steele @ 2018-12-10 13:37 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin; +Cc: Stephen Frost <sfrost@snowman.net>
On 12/9/18 5:24 PM, Ron wrote:
> On 12/09/2018 01:10 PM, Stephen Frost wrote:
>>
>> You can address that risk by enabling archive_timeout, which will make
>> PostgreSQL archive the WAL file even if it isn't full after a certain
>> amount of time, reducing the potential data loss window.
>
> I thought checkpoint_timeout forced a WAL switch.
Checkpoints do not automatically switch WAL. archive_timeout is the way
to go.
Postgres >= 9.5 will mostly zero the remainder of the WAL and Postgres
>= 11 will entirely zero it. Either way, compression works well.
For production systems I generally use archive_timeout=900 so a segment
is archived every 15 minutes when there is not enough activity to force
a switch naturally. Note that if there is *zero* activity then there in
no need for a WAL switch but some older versions would do so anyway if
wal_level=hot_standby. We fixed that in Postgres 10.
Regards,
--
-David
david@pgmasters.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgbackrest periodic WAL backups?
@ 2018-12-10 14:19 Ron <ronljohnsonjr@gmail.com>
parent: David Steele <david@pgmasters.net>
0 siblings, 0 replies; 7+ messages in thread
From: Ron @ 2018-12-10 14:19 UTC (permalink / raw)
To: pgsql-admin
On 12/10/2018 07:37 AM, David Steele wrote:
> On 12/9/18 5:24 PM, Ron wrote:
>> On 12/09/2018 01:10 PM, Stephen Frost wrote:
>>> You can address that risk by enabling archive_timeout, which will make
>>> PostgreSQL archive the WAL file even if it isn't full after a certain
>>> amount of time, reducing the potential data loss window.
>> I thought checkpoint_timeout forced a WAL switch.
> Checkpoints do not automatically switch WAL. archive_timeout is the way
> to go.
Thanks for the clarification.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-12-10 14:19 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-09 16:05 pgbackrest periodic WAL backups? Ron <ronljohnsonjr@gmail.com>
2018-12-09 19:10 ` Stephen Frost <sfrost@snowman.net>
2018-12-09 22:24 ` Ron <ronljohnsonjr@gmail.com>
2018-12-10 13:37 ` David Steele <david@pgmasters.net>
2018-12-10 14:19 ` Ron <ronljohnsonjr@gmail.com>
2018-12-10 05:16 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-10 13:31 ` David Steele <david@pgmasters.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox