MIME-Version: 1.0
References: <CAFX4Q9LhPByLecCuHu7gkmmNnDeKwG2CmG01y5ChOwtLrPdE=g@mail.gmail.com>
In-Reply-To: <CAFX4Q9LhPByLecCuHu7gkmmNnDeKwG2CmG01y5ChOwtLrPdE=g@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 4 Mar 2026 01:57:55 -0500
Message-ID: <CANzqJaBDjss4xYaBrXcU+-1RF2bGD2x2W3vufab6DakNrkMNpg@mail.gmail.com>
Subject: =?UTF-8?Q?Re=3A_PostgreSQL_Archive_Log_Partition_Reaching_95=25_?=
	=?UTF-8?Q?=E2=80=93_Need_Automated_Cleanup?=
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000091cccd064c2d57eb"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBDjss4xYaBrXcU%2B-1RF2bGD2x2W3vufab6DakNrkMNpg%40mail.gmail.com>
Precedence: bulk

--00000000000091cccd064c2d57eb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 3, 2026 at 9:19=E2=80=AFAM loganathan P <plogandba@gmail.com> w=
rote:

> Dear Team,
>
> We have PostgreSQL 15 and PostgreSQL 17 databases running in separate
> environments on different servers. Each database is approximately 1.5 TB =
in
> size and highly active, generating around 500 GB of archive logs per day.
>

How many days (or weeks) of PITR backups do you need to retain.


> We have VM SRM replication configured.
>

That's probably not wise, given the size and volume.  Physical replication
via pg_basebackup is quite easy to set up.
This is the command I use:
pg_basebackup \
            --pgdata=3D$PGDATA \
            --dbname=3Dservice=3Dbasebackup \
            --verbose --progress \
            --checkpoint=3Dfast \
            --write-recovery-conf \
            --wal-method=3Dstream \
            --create-slot --slot=3D$SlotName \
            --compress=3Dserver-lz4


> The archive log partition reaches 95=E2=80=93100% utilization before back=
ups are
> taken. After the backups are completed, we must manually remove the
> archived log files to free up space.
>
> Could you please advise whether PostgreSQL has any built-in parameters or
> mechanisms to automatically delete archived log files
>

Lauren Albe is right: pgbackrest is probably the tool for you.  Besides
doing regular database backups, it manages WAL archives, encryption,
compression and can be configured to automatically purge the oldest saveset
(a full backup and its associated incremental backups plus archived WAL
files) after a certain number of days.

For example, I've got multiple 3-4TB databases and use pgbackrest to retain
28-35 days of PITR backups.  After the full backup on that 35th day,
pgbackrest automatically "expires" the oldest saveset and then we're back
down to 28 days of PITR backups.

It really is a wonderful do-everything tool.


> once they have been successfully backed up?
>

Where do you back them up to?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000091cccd064c2d57eb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 3, 2026 at 9:19=E2=80=AFAM lo=
ganathan P &lt;<a href=3D"mailto:plogandba@gmail.com">plogandba@gmail.com</=
a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Dear Team,=
</div><div><br></div><div>We have PostgreSQL 15 and PostgreSQL 17 databases=
 running in separate environments on different servers. Each database is ap=
proximately 1.5 TB in size and highly active, generating around 500 GB of a=
rchive logs per day.</div></div></blockquote><div><br></div><div>How many d=
ays (or weeks) of PITR backups do you need to retain.</div><div>=C2=A0</div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div> W=
e have VM SRM replication configured.<br></div></div></blockquote><div><br>=
</div><div>That&#39;s probably not wise, given the size and volume.=C2=A0 P=
hysical replication via pg_basebackup is quite easy to set up.</div><div>Th=
is is the command I use:</div><div><font face=3D"monospace">pg_basebackup \=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 --pgdata=3D$PGDATA \<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 --dbname=3Dservice=3Dbasebackup \<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 --verbose --progress \<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 --checkpoint=3Dfast \<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 --write-recovery-conf \<br>=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 --wal-method=3Dstream \<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 --create-slot --slot=3D$SlotName \<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 --compress=3Dserver-lz4</font></div><div>=C2=A0</d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>=
The archive log partition reaches 95=E2=80=93100% utilization before backup=
s are taken. After the backups are completed, we must manually remove the a=
rchived log files to free up space.<br><br>Could you please advise whether =
PostgreSQL has any built-in parameters or mechanisms to automatically delet=
e archived log files </div></div></blockquote><div><br class=3D"gmail-Apple=
-interchange-newline">Lauren Albe is right: pgbackrest is probably the tool=
 for you.=C2=A0 Besides doing regular database backups, it manages WAL arch=
ives, encryption, compression and can be configured to=C2=A0automatically p=
urge the oldest saveset (a full backup and its associated incremental backu=
ps plus archived WAL files) after a certain number of days.</div><div><br><=
/div><div>For example, I&#39;ve got multiple 3-4TB databases and use pgback=
rest to retain 28-35 days of PITR backups.=C2=A0 After the full backup on t=
hat 35th day, pgbackrest automatically &quot;expires&quot; the oldest saves=
et and then we&#39;re back down to 28 days of PITR backups.</div><div><br><=
/div><div>It really is a wonderful do-everything tool.</div><div>=C2=A0</di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>o=
nce they have been successfully backed up?<br></div></div></blockquote><div=
><br></div><div>Where do you back them up to?=C2=A0</div><div><br></div></d=
iv><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" cl=
ass=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and but=
ter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Red=
acted&gt; lobster!</div></div></div></div></div></div>

--00000000000091cccd064c2d57eb--