public inbox for [email protected]help / color / mirror / Atom feed
pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary 4+ messages / 3 participants [nested] [flat]
* pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary @ 2021-12-23 12:58 Bharath Rupireddy <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Bharath Rupireddy @ 2021-12-23 12:58 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> Hi, 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.). Thoughts? [1] - When used as a standalone program all WAL files logically preceding the oldestkeptwalfile will be removed from archivelocation. https://www.postgresql.org/docs/devel/pgarchivecleanup.html [2] - https://www.postgresql.org/message-id/CAHg%2BQDc9xwN7EmuONT3T91pCqFG6Q-BCe6B-kM-by7r1uPEicg%40mail.g... Regards, Bharath Rupireddy. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary @ 2021-12-23 14:52 Euler Taveira <[email protected]> parent: Bharath Rupireddy <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Euler Taveira @ 2021-12-23 14:52 UTC (permalink / raw) To: Bharath Rupireddy <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> 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.). 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. 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. -- Euler Taveira EDB https://www.enterprisedb.com/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary @ 2021-12-29 13:57 Stephen Frost <[email protected]> parent: Euler Taveira <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Stephen Frost @ 2021-12-29 13:57 UTC (permalink / raw) To: Euler Taveira <[email protected]>; +Cc: Bharath Rupireddy <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> Greetings, * Euler Taveira ([email protected]) 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 Attachments: [application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary @ 2021-12-29 14:36 Bharath Rupireddy <[email protected]> parent: Stephen Frost <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Bharath Rupireddy @ 2021-12-29 14:36 UTC (permalink / raw) To: Stephen Frost <[email protected]>; +Cc: Euler Taveira <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> On Wed, Dec 29, 2021 at 7:27 PM Stephen Frost <[email protected]> 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. +1 > > 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. Yes, the end-to-end tool is helpful in rather eventual situations and having it in the core is more helpful instead of every postgres vendor developing their own solution and many times it's hard to get it right. Also, I agree to not club this idea with pg_archviecleanup. How about having a new tool like pg_walcleanup/pg_xlogcleanup helping the developers/admins/users in eventual situations? Regards, Bharath Rupireddy. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-12-29 14:36 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-12-23 12:58 pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary Bharath Rupireddy <[email protected]> 2021-12-23 14:52 ` Euler Taveira <[email protected]> 2021-12-29 13:57 ` Stephen Frost <[email protected]> 2021-12-29 14:36 ` Bharath Rupireddy <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox