Re: pgbackrest periodic WAL backups?

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(at)pgmasters(dot)net