Greetings, * Ron (ronljohnsonjr@xxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
