public inbox for [email protected]
help / color / mirror / Atom feedFrom: Gianfranco Cocco <[email protected]>
To: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: R: PostgreSQL Archive Log Partition Reaching 95% – Need Automated Cleanup
Date: Wed, 4 Mar 2026 08:32:39 +0000
Message-ID: <PAVPR02MB913472485EDA7E5F01ABDC37F47CA@PAVPR02MB9134.eurprd02.prod.outlook.com> (raw)
In-Reply-To: <CANzqJaBDjss4xYaBrXcU+-1RF2bGD2x2W3vufab6DakNrkMNpg@mail.gmail.com>
References: <CAFX4Q9LhPByLecCuHu7gkmmNnDeKwG2CmG01y5ChOwtLrPdE=g@mail.gmail.com>
<CANzqJaBDjss4xYaBrXcU+-1RF2bGD2x2W3vufab6DakNrkMNpg@mail.gmail.com>
Hi,
usually you could use a similar script that will delete archived WALs after a pg_basebackup
#!/bin/bash
# I'm looking for the latest backup so I can clean up the previous archive logs.
DATE=$(date +"%Y-%m-%d-%I-%M-%p");
ARCHIVEDIR='/pg_archive'
cd $ARCHIVEDIR
CHKPOINT=$(ls *.backup -Art | tail -n 1)
/usr/pgsql-15/bin/pg_archivecleanup -d $ARCHIVEDIR $CHKPOINT > /var/log/postgresql/RotateWAL-$DATE.log 2>&1
Regards.
Gianfranco
________________________________
Da: Ron Johnson <[email protected]>
Inviato: Mercoledì, 04 Marzo, 2026 07:57
A: pgsql-general <[email protected]>
Oggetto: Re: PostgreSQL Archive Log Partition Reaching 95% – Need Automated Cleanup
On Tue, Mar 3, 2026 at 9:19 AM loganathan P <[email protected]<mailto:[email protected]>> wrote:
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=$PGDATA \
--dbname=service=basebackup \
--verbose --progress \
--checkpoint=fast \
--write-recovery-conf \
--wal-method=stream \
--create-slot --slot=$SlotName \
--compress=server-lz4
The archive log partition reaches 95–100% utilization before backups 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?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: R: PostgreSQL Archive Log Partition Reaching 95% – Need Automated Cleanup
In-Reply-To: <PAVPR02MB913472485EDA7E5F01ABDC37F47CA@PAVPR02MB9134.eurprd02.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox