From: Stephen Frost <sfrost@snowman.net>
To: Tim <timfosho@gmail.com>
Cc: posgres support <pgsql-admin@postgresql.org>
Subject: Re: pg_basebackup and pg_receivewal timing, missing WAL files
Date: Thu, 9 Jun 2022 17:24:09 -0400
Message-ID: <20220609212409.GR9030@tamriel.snowman.net> (raw)
In-Reply-To: <CAKhLO5gY4rUS65RUD_OGyHQTxoOa1-akLBWjh-CDQiVDJknecw@mail.gmail.com>
References: <CAKhLO5itE_xp7KX7Vxnho8Uzczgwt8iUD9bVtKNPamBOst=hPA@mail.gmail.com>
<20220609183641.GN9030@tamriel.snowman.net>
<CAKhLO5gY4rUS65RUD_OGyHQTxoOa1-akLBWjh-CDQiVDJknecw@mail.gmail.com>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, timfosho@gmail.com
Subject: Re: pg_basebackup and pg_receivewal timing, missing WAL files
In-Reply-To: <20220609212409.GR9030@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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