Date: Thu, 9 Jun 2022 17:24:09 -0400
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
Message-ID: <20220609212409.GR9030@tamriel.snowman.net>
References: 
 <CAKhLO5itE_xp7KX7Vxnho8Uzczgwt8iUD9bVtKNPamBOst=hPA@mail.gmail.com>
 <20220609183641.GN9030@tamriel.snowman.net>
 <CAKhLO5gY4rUS65RUD_OGyHQTxoOa1-akLBWjh-CDQiVDJknecw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="G4wttjDTPgtSCp2V"
Content-Disposition: inline
In-Reply-To: 
 <CAKhLO5gY4rUS65RUD_OGyHQTxoOa1-akLBWjh-CDQiVDJknecw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20220609212409.GR9030%40tamriel.snowman.net>
Precedence: bulk


--G4wttjDTPgtSCp2V
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--G4wttjDTPgtSCp2V
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJiomT5AAoJEO1sijiDR2RVHKUQAJvovoy7rFJ4q5Lwy3pagt5J
ZwtKtReAftpWvSXMFCcvB30hGlVjL+2Dj5TdimRIUr0hbNdXlkuk7EJT8s02s3ge
ewGtap0vJekiLJSrBi0nzHLeJLQ4oZg8ufPJgG8itEWRfPDntX0INR4VHzI7I4V/
Gjj5W2ILBHDZE53FK99L/wMTuSZC20h5DP7mhIkowe8z/Ufv7mUsnACgOphd0iuj
kUkhmbBI/vmmcCT0+OocnvmjQuVRS8qfbAI+TLlEQxtm6hY/e2FFmqLu/fNnsPCM
zrScV6byWoPtShv3t4V/XSXAndEr1Z76r7Ur+cTdbePt/e9gEIAUvjtKCVLfX48c
2HVfDIgsHpf7lwXD1ny9A14YD7ransrbJbaGb6rWeo2GXm9qScF3n3mGmFjJcTyh
p6vu3CzjYJmwgJCz+4UgeVpZBbH+Odtdnxc/5i87ps4ji1N3J18jbnUjgoVZH51X
rUKkFTo6SZ6r/erRqKxxWr36WFCCh7zJyisO8TLQrmvD0mFH6clUrdIsCbxkkqwi
8Jd5VtxtijLAcVmLvXMKzQafxU/BqsH7u6jFalAwyYjuYLlOn/qEdKvybfLCmW/H
+TMASjDm44TkC8TsAmMgvx4vVaQnU0ckvkJTbkwuMimZmYt5mwIuwYNSoNCSC+Ym
JrwO7YFjLQAxGJHyrKDR
=wxKJ
-----END PGP SIGNATURE-----

--G4wttjDTPgtSCp2V--