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


--gubR/AMv6tbKMdmr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Tim (timfosho@gmail.com) wrote:
> I'm attempting to create a long term, stand alone backup archive script f=
or
> a pretty active and large database (3TB) which will store backups going
> back a few months.
> Using the combination of pg_basebackup in TAR format, with the `Xn` flag,
> and running pg_receivewal in the background, I'm ending up with a backup
> that is missing quite a few WAL files.
>=20
> Trying to figure out where my understanding of the process is incorrect,
> and what I need to do to correct this.
>=20
> *Backup *process:
>=20
>     * Start pg_receivewal to archive location
>     * Then run: `pg_basebackup -h {host} -p {port} -D - -Ft -Xn
> --checkpoint=3Dfast | ...`

What's wrong with using -Xs instead..?  Basically does exactly what you
seem to be trying to do here.

>     * This backup is taken from a *standby node*

Where is the pg_receivewal run?  Against the primary or the replica?

>     * Once pg_basebackup finishes, send SIGINT to pg_receivewal
>=20
> *Recovery* Process:
>=20
>     * Untar basebackup to data directory
>     * Copy archived WAL files to a separate directory on the same drive
>     * settings: restore_command =3D 'cp /var/lib/pgsql/wal_archive/%f %p'
>                      recovery_target =3D immediate
>=20
>=20
> Once I start the DB, I immediately get
>=20
> cp: cannot stat '/var/lib/pgsql/wal_archive/0000001200003CF80000003F': No
> such file or directory
>=20
>=20
>=20
> *WAL FILES: *
>=20
> 0000001200003CF80000003F << First WAL file the DB looks for
> 0000001200003CF700000075 << The next WAL file
> ----------------------------------------  << Missing WAL files from
> pg_receivewal archive
> ...
> 0000001200003CF8000000C8 << First WAL file in pg_receivewal archive
>=20
> *backup_label *file:
>=20
> START WAL LOCATION: 3CF7/75974400 (file 0000001200003CF700000075)
> CHECKPOINT LOCATION: 3CF8/3F1139C0
> BACKUP METHOD: streamed
> BACKUP FROM: standby
> START TIME: 2022-06-08 12:51:52 EDT
> LABEL: pg_basebackup base backup
> START TIMELINE: 18
>=20
>=20
> I can see that START WAL LOCATION & CHECKPOINT LOCATION are where the
> basebackup starts to recover from, but how come these are not in my
> pg_receivewal archive, if I'm starting it before starting the pg_baseback=
up
> process? How can I ensure they are part of this archive?

Well, using -Xs should do that.

> Since this is a recent backup, I have a separate WAL archive from where I
> can just recover those using a different restore_command, and once that
> missing gap of WAL files is recovered from this archive, stopping the
> server
> and switching back to `restore_command =3D 'cp /var/lib/pgsql/wal_archive=
/%f
> %p'` and recovering the rest of the WAL files from the pg_receivewal
> archive works fine. The DB recovers and accepts connections shortly after.
>=20
> The whole point of this is to have a several months old, stand alone
> backup, I need to have all the WAL files available in that original WAL
> archive created with pg_receivewal.

The use-case generally makes sense, in pgbackrest we have --archive-copy
for more-or-less the same kind of thing, though you can now just use a
separate newly created repo that you back up the standalone backup to
(when multiple repos are configured, archive-push will archive to all of
them) and then archive that copy.  The latter is what we'd generally
recommend these days, but with archive-copy and a full backup, you can
just grab the full backup directory from inside the repo and it'll have
everything.

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJioj25AAoJEO1sijiDR2RVRC0QAJ2wyDbaUS0/xW/CqpfHzWqF
NJT7SDygxQAwaTB8/ZRimrIEy9/EDLLeZCz+mdrfkvvi4vCzIF+sZRIFRyaLP/mp
Jg8mvz1JygBaALf3aQdF0FKjQ6dxByU9G+69DEIBkSR5BbpLcEZ7L2W3kQ6BvL1H
Dnauax69w6Ug4sb4wIsDH2P0Q8kIhxC3tVTDtZRRqtoJL1dKGzTmB84euK3Tm/yr
TZeKFvHdd1n10Z3un1FxYqj6RNuvPZGB8vcfOhCeWCclTxeJGfgp0EAoTp+wnmJ1
8BfdHLslIio3QTMYBH1nUbEVMRki/7fieh12/U+WLFpR9CpWoFInX1qHDOFBV5vA
wJtiGrbQVx767nZ1Xwd6FFwt74EVYM9ffg3GP87KOIbZBCvrkgh2a+EZPU79iXFN
iXVn4Q5IGHQfd2KndH3Tl5ZJ5gCelxCyK8LN0mOB2TvO6aRqW4Djs1KzXUPHz9yi
4FcRJjsnGsZL4YPWGQR12gBSqC7F4hcheKMjqHqkMxgDew8R02SMhm3Rq4aRdZCU
6j0zXFnlLee+CfOQpqXQ/fBeLk0Vz19Td6iUk4dZanlu3S7tC9tEYnbzogRDRcsd
sHkIGRbVqNvylFtuucNDdQbFG/CEpitBp1EjLvoADMLWHjxYA3Q63Jo03Ng1dzGU
emjLL2hfidrahVcenTpM
=I0ta
-----END PGP SIGNATURE-----

--gubR/AMv6tbKMdmr--