Date: Wed, 10 May 2023 08:06:15 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Dhirendra Singh <dhirendraks@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Question about pg_wal.tar.gz generated by pg_basebackup
Message-ID: <ZFuIt3T38oaYfxHE@tamriel.snowman.net>
References: 
 <CAEyh4sb3SnH6Mf88R98C3LBOdV_rW-sBu=1YoycVSqDYA+BA9A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="EC17ql0Ms9OCU1j/"
Content-Disposition: inline
In-Reply-To: 
 <CAEyh4sb3SnH6Mf88R98C3LBOdV_rW-sBu=1YoycVSqDYA+BA9A@mail.gmail.com>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/ZFuIt3T38oaYfxHE%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Dhirendra Singh (dhirendraks@gmail.com) wrote:
> I am taking backup using pg_basebackup for the purpose of point in time
> recovery in case something goes wrong with the data.

pg_basebackup works decently for this but it's pretty basic.

> The backup generates pg_wal.tar.gz which contains the wal file generated
> during the backup.

Yes, those are the WAL files needed to restore that backup.

> Is it of any use in the point in time recovery? do i have to archive it?

Yes and no ... if you're independently archiving your WAL then you don't
really need the WAL from pg_basebackup, but if you're not also archiving
your WAL then you absolutely must keep the WAL from the pg_basebackup.
There's an option in pg_basebackup which allows you to choose if you get
the WAL or not with pg_basebackup.

> According to the documentation, any wal files in pg_wal directory has to be
> deleted after the base backup is restored and before the recovery starts.

Strictly speaking the thing to do would be to put the WAL from
pg_basebackup somewhere and then have a restore command written that is
able to pull that WAL when PG asks for it.  While the documentation says
that, we will still look in the WAL dir as the location of last restore
when doing replay so putting the WAL from pg_wal.tar.gz in there will
work too.

> So if i understand it correctly, pg_wal.tar.gz is not required for point in
> time recovery...but just want to confirm if my understanding is correct.

Archiving with archive_command (or using pg_receivewal) is required for
PITR.

> My sole purpose of taking the backup is for point in time recovery. I am
> not going to use it to create a standby server.

Have you configured an archive_command...?  If not, you'll need to, to
get any proper PITR.  What pg_basebackup is giving you is really a
single, but complete, backup but that will only restore to the end of
that backup, to go any farther you'll need the WAL that was written
after that backup was taken and to get that you need to set up an
archive command or use pg_receivewal.

Note that with pg_basebackup and pg_receivewal or some hand written
archive_command, you still need to write your own backup and WAL
retention code, when doing a restore figure out which backup to use for
the restore to get to the point in time you want, deal with
pg_basebackup and pg_receivewal being single-threaded, and a bunch of
other things that bespoke tooling like pgBackRest was written to
explicitly deal with.

You might save a lot of time by checking out pgBackRest instead,
especially for PITR.

Thanks,

Stephen

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

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmRbiLUACgkQ7WyKOINH
ZFWEoA//b7L7mEa18Me0RK5C2H5gQb0Je6Nu45MzHDSXyS9uigMg+cFjL5sAaHT/
cLzSoWhU1rYUYe3q3XeJMu/24YU2YmlnNXtJAV2+y/4JNCZSBqT4J8ydNfj6n8d8
W8hAMjkvKt7p9IFltuuUHAhGpHCqaYoFHQLr6/WFfR+ugsHZijwAAXxNe4fHkipn
03VPAp47CuXEoiPv2zx1wVo8a8KUHbglx9M2m6ISgZKWA+OD1j8ttcwblMVnTeb0
/6xlHgGfrnQHbAHbWcapINYgnzWjIEa4uuu6LhRFikSIH5BRMDRAEYpjQRxmHxh7
PVwB60qwG0s32trHMDO+A9nILajztiYZUcw1M+vVF+VC690IikgpDDspIraBpqGy
io+deBn50CKbJPSy2Awfb38+cDmbotpksTUcSpK1bE7E0eN5LxqpRnHXNOJkzV0q
hzGDZtJ8cFwzE08nYxFbr0caOcuuiQ2Xv4yTT4Fg3BlbbKAfhT1RM+Y1X1qgfwfK
eH6Vlz5aPugojSsuETFBP/9Q/yp+CkWsQuZYe0bgBLYAh5CPnZ3cDXP8y3gkMe6N
4oDe55NtPuSgHuJRe1QPghZk5IwrdI9opKTAR6K+gjM/9YJGSVaslbqB2Fq9UFIi
WwsGyaVKfsy8rYLnE4Ckzy/uZjP8c5xZ5m1gF3ts4x+8TxkLdNM=
=umsW
-----END PGP SIGNATURE-----

--EC17ql0Ms9OCU1j/--