Greetings,
* Tim (timfosho@gmail.com) wrote:
> We currently use pgbackrest as our primary backup tool actually, and it
> works great! The issue I ran into with trying to set it up that way with
> pgbackrest, is when configuring the second repo, pgbackrest will have to
> archive all WAL files to it, going back to the oldest backup. And we have
Eh? Why is that? I think there's some confusion here regarding how
pgbackrest multi-repo works. We won't archive back to the 'oldest
backup' when you add a second repo, we'll just archive *new* WAL to it
after it's been configured.
> this (admittedly arbitrary) legal policy requiring a single instance of a 6
> month old backup for auditing purposes. So pgbackrest has to keep WALs that
> far back, the main repo only needs 4 weeks of WALs for PITR, its a bit
> overkill archiving WALs twice and also storing them an extra 5 months. So I
> opted to write a custom backup script.
You just need to handle the modification of the pgbackrest config to
add/remove the second repo (and run the backup to the second repo when
it's enabled) when you want a new backup of it.
> Though I was not aware of the `--archive-copy` option, I wonder if I can
> just turn that option on for the main repo, and then extract the particular
> backup I need to another location. We use Azure Blobs for backups.
Probably this would also work, yes.
> Unfortunately `-Xs` cannot be used with pg_basebackup in TAR format, I'm
> using the AzCopy utility, by piping pg_basebackup into it, to stream the
> basebackup directly into Azure Blob storage, without storing it anywhere
> first (Which pgbackrest does natively as well!). So there is no other way
> without using the TAR format option.
... yikes, that sounds horribly rickety.
> Nevertheless, it seems like I need the contents of the pg_wal directory at
> the start of pg_basebackup, since pg_receivewal only streams WAL generated
> after it starts, the restored DB is missing all the files contained
> within, I've adjusted my script to copy them as well and testing currently.
I have to say that I strongly recommend NOT trying to go down this
direction, as it seems terribly risky, not to mention that the WAL files
in pg_wal are still being written to...
Thanks,
Stephen