Date: Sun, 9 Dec 2018 14:10:35 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pgbackrest periodic WAL backups?
Message-ID: <20181209191035.GG3415@tamriel.snowman.net>
References: <df843a29-494a-7e72-e95a-647941979f1a@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="KRL0S51mf8StHmC/"
Content-Disposition: inline
In-Reply-To: <df843a29-494a-7e72-e95a-647941979f1a@gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--KRL0S51mf8StHmC/
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> I've got a full pgbackrest backup in crontab that runs weekly, and a diff
> backup that runs nightly.=A0 However, I can't find any way to do hourly W=
AL
> backups.
>=20
> Or does "archive_command =3D 'pgbackrest --stanza=3Ddemo archive-push %p'=
" just
> keep things automatically caught up, obviating the need for periodic WAL
> backups (as are needed in other RDBMSs)?

Yes and no.  With the archive command, every WAL archived will be pushed
to the archive more-or-less immediately (or, at least, as fast as your
system can get it there, if you enable parallel archiving in
pgbackrest), however, WAL files have a size (by default 16MB) and if you
have a very slowly changing system then it could possibly be hours
between WALs being pushed to the archive by archive_command.

You can address that risk by enabling archive_timeout, which will make
PostgreSQL archive the WAL file even if it isn't full after a certain
amount of time, reducing the potential data loss window.  pgBackRest
will compress the WAL file, but that might not be as effective as you'd
like on older versions of PG because WAL files used to have either
potentially a bunch of garbage at the end (due to being recycled) or
possibly WAL headers through the end of the archive (which is better
than garbage but still not free).  We've finally, I believe, got it such
that the end of WAL is zero'd out, leading to very little overhead from
having a low archive_timeout value set, but I don't recall offhand if
that got into v11 or if it's coming in v12.

We're looking at improving pgBackRest to actually scan the WAL file
internals itself to identify the end-of-WAL and possibly eliminate the
garbage or WAL headers at the end of a not-full WAL segment but it isn't
very high on the list currently.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJcDWirAAoJEO1sijiDR2RVLicP/1jRy46xDs7tUbW0xqqnJR88
sLb3wuO7npIY1hlSR5rvqjpt17ny9Rog1r4OdEU/JXqKWq2iPpLurHqebcFYCwYi
hAqXbJJU0Bd+5itaTCNx6D7ZYdORlUGDyZcu1rgjvSVvMqYhj4Jm7TLU7gKbgX8M
XpXO0FBXaEv2dixvftmbAasbYf3TylkLpYjypngBgKfJuVX/iXWhZjPSQPZPYnQ7
j0oeIKb+bU6jfBuETaNp6Bi4J2pAC6yPYS9/v4A82zB79ZqxPrmrMR+C4mEB3G+0
xkOrFSCdQr3y/iyVJhe/DsdUeyMwGqTYqI5j/9TKFl6TP4DDoSINqS901+HYfBy3
iOLv9j/Lol+LLI9zrXRPZ9vFt4jUlb12BdqsCpxd297HVzlkSDeHow+sVqbiH/yy
W6hPFvYyI+2tVPjfC37cp8I4Rea3fDgazZHHd+gDyC7cigNrlIiGlnMVRqzNOTyB
DmpUBmC4yj6HD82mgAU2odQCRvoVL7HIY6UxOLStytgXUE5GYYk2FVWIDOkNvLva
XSuNfzxvGZJiBCelSSvErTTi/WzXpd/vKBr8pWLRk4AAR+hhMKab94C6nDSe3gXS
wJ7rpqnVVUVqxt699urQ9lFOMsy8Z4YIrWJSNsS8c6DhFrqrkcBgu3uvNeaFYs21
s3r6BjllWI37Rd3WRmwB
=R1R5
-----END PGP SIGNATURE-----

--KRL0S51mf8StHmC/--