Date: Wed, 29 Dec 2021 08:57:10 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Euler Taveira <euler@eulerto.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Subject: Re: pg_archivecleanup - add the ability to detect, archive and
 delete the unneeded wal files on the primary
Message-ID: <20211229135710.GH15820@tamriel.snowman.net>
References: 
 <CALj2ACWrryWEbzn3gU9yqyarY5ptsUd8wrLwNgfv+Xn4tepRJQ@mail.gmail.com>
 <66f58359-6241-47fd-bf25-4d105a00523c@www.fastmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="LG0Ll82vYr46+VA1"
Content-Disposition: inline
In-Reply-To: <66f58359-6241-47fd-bf25-4d105a00523c@www.fastmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20211229135710.GH15820%40tamriel.snowman.net>
Precedence: bulk


--LG0Ll82vYr46+VA1
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* Euler Taveira (euler@eulerto.com) wrote:
> On Thu, Dec 23, 2021, at 9:58 AM, Bharath Rupireddy wrote:
> > pg_archivecleanup currently takes a WAL file name as input to delete
> > the WAL files prior to it [1]. As suggested by Satya (cc-ed) in
> > pg_replslotdata thread [2], can we enhance the pg_archivecleanup to
> > automatically detect the last checkpoint (from control file) LSN,
> > calculate the lowest restart_lsn required by the replication slots, if
> > any (by reading the replication slot info from pg_logical directory),
> > archive the unneeded (an archive_command similar to that of the one
> > provided in the server config can be provided as an input) WAL files
> > before finally deleting them? Making pg_archivecleanup tool as an
> > end-to-end solution will help greatly in disk full situations because
> > of WAL files growth (inactive replication slots, archive command
> > failures, infrequent checkpoint etc.).

The overall idea of having a tool for this isn't a bad idea, but ..

> pg_archivecleanup is a tool to remove WAL files from the *archive*. Are you
> suggesting to use it for removing files from pg_wal directory too? No, thanks.

We definitely shouldn't have it be part of pg_archivecleanup for the
simple reason that it'll be really confusing and almost certainly will
be mis-used.  For my 2c, we should just remove pg_archivecleanup
entirely.

> WAL files are a key component for backup and replication. Hence, you cannot
> deliberately allow a tool to remove WAL files from PGDATA. IMO this issue
> wouldn't occur if you have a monitoring system and alerts and someone to keep
> an eye on it. If the disk full situation was caused by a failed archive command
> or a disconnected standby, it is easy to figure out; the fix is simple.

This is perhaps a bit far- PG does, in fact, remove WAL files from
PGDATA.  Having a tool which will do this safely when the server isn't
able to be brought online due to lack of disk space would certainly be
helpful rather frequently.  I agree that monitoring and alerting are
things that everyone should implement and pay attention to, but that
doesn't happen and instead people end up just blowing away pg_wal and
corrupting their database when, had a tool existed, they could have
avoided that happening and brought the system back online in relatively
short order without any data loss.

Thanks,

Stephen

--LG0Ll82vYr46+VA1
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJhzGk2AAoJEO1sijiDR2RVopQP/0T21pqMzvOIUfYyAkLN82su
clpcbIoySq/Z13phvUN94uzHNCjPAwD8r3Ta4Y3nILwg+HFEm3nN5N9WMKZKHLp2
asfko0r+24zDSUsgGCrOqhW7q188oK1uYOM1i1NAR5o7eEjTnzxnPpfc9dIy9xJI
wxMG31FnJ6etPqkECiqo2JfpRQScUiXA+fNNWoOYPUzBKDMfjkTlw2PF5j3RvnTa
59+FKz1x/ZtUvysWq87mB+2hjiFWvoPBPO/Nz8QPTHadTlEh2/EiJfxB2PrIzrJk
8hSKelFm238dw6DIKaQK+3G9YUymyywOVCmGgh17StWsMkGTRw5GBvbTAWb12U9k
Npfk74GUAk+DYdlbpEh15L3rZS++x6gXgf5j/ITGYlorAMtUsSkW20s9FLJMf4Fu
elaFhTHmuTrcQk+bbLLk+Ad65dqBL3VFeK0jFeExqKc09HKWNscM7iADD+8tDDGO
9zhVcxjn4Bvs6CLV202G+KjmXHCaKd1j+o+5pOnzk1RLhtOeAeQCOF8ScKeqBU3m
tIw+Isra+KN55s24hTj37Hw/38pfrHpFBxGh/iBxpsbySVAcpEZ2Mg7QhP77yT5x
KDxyNwJCqEx94pGjbC1i+toW/vAXOmQhibRkilDRJHXrV4bJU68J/QghXaQt7C1N
wgei6rE8MumJpJZjOP8S
=2bsp
-----END PGP SIGNATURE-----

--LG0Ll82vYr46+VA1--