Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1prDFq-0005P4-IF for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2023 07:38:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1prDFo-0007kT-Fz for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2023 07:38:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1prDFo-0007kG-5x for pgsql-hackers@lists.postgresql.org; Tue, 25 Apr 2023 07:38:24 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1prDFk-001lzT-Vp for pgsql-hackers@postgresql.org; Tue, 25 Apr 2023 07:38:22 +0000 Received: from oss.nttdata.com (localhost [127.0.0.1]) by oss.nttdata.com (Postfix) with ESMTPA id 5C7EB6064A for ; Tue, 25 Apr 2023 16:38:16 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.8 at oss.nttdata.com MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=_5385f463765c47059110ca2da633e640" Content-Transfer-Encoding: 7bit Date: Tue, 25 Apr 2023 16:38:16 +0900 From: torikoshia To: Pgsql Hackers Subject: Allow pg_archivecleanup to remove backup history files User-Agent: Roundcube Webmail/1.4.11 Message-ID: X-Sender: torikoshia@oss.nttdata.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_5385f463765c47059110ca2da633e640 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Hi, Currently pg_archivecleanup doesn't remove backup history files even when they're older than oldestkeptwalfile. Of course the size of backup history files are smaller than WAL files and they wouldn't consume much disk space, but a lot of backup history files(e.g. daily backup for years) whose backups data have been already removed are unnecessary and I would appreciate if pg_archivecleanup has an option to remove them. Attached a PoC patch, which added new option -b to remove files including backup history files older than oldestkeptwalfile. $ ls archivedir 000000010000000000000001 000000010000000000000003 000000010000000000000006 000000010000000000000008 000000010000000000000002 000000010000000000000004 000000010000000000000007 000000010000000000000009 000000010000000000000002.00000028.backup 000000010000000000000005 000000010000000000000007.00000028.backup 00000001000000000000000A.partial $ pg_archivecleanup -b archivedir 000000010000000000000009 $ ls archivedir 000000010000000000000009 00000001000000000000000A.partial Any thoughts? -- Regards, -- Atsushi Torikoshi NTT DATA CORPORATION --=_5385f463765c47059110ca2da633e640 Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0001-Allow-pg_archivecleanup-to-remove-backuphistoryfile.patch Content-Disposition: attachment; filename*0*=UTF-8''v1-0001-Allow-pg_archivecleanup-to-remove-backuphistor; filename*1*=yfile.patch; size=2522 RnJvbSBhZDg3MjI0ZWM1YmE2ZWUxM2NjZjkzNGJmM2U1YWRlZmI3ZTY3MjEyIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBdHN1c2hpIFRvcmlrb3NoaSA8dG9yaWtvc2hpYUBvc3MubnR0 ZGF0YS5jb20+CkRhdGU6IE1vbiwgMjQgQXByIDIwMjMgMjM6Mjg6MDYgKzA5MDAKU3ViamVjdDog W1BBVENIIHYxXSBBbGxvdyBwZ19hcmNoaXZlY2xlYW51cCB0byByZW1vdmUgYmFja3VwIGhpc3Rv cnkgZmlsZXMKCkFkZCBuZXcgb3B0aW9uIC1iIHRvIHJlbW92ZSBmaWxlcyBpbmNsdWRpbmcgYmFj a3VwIGhpc3RvcnkgZmlsZXMgb2xkZXIKdGhhbiBvbGRlc3RrZXB0d2FsZmlsZS4KCi0tLQogc3Jj L2Jpbi9wZ19hcmNoaXZlY2xlYW51cC9wZ19hcmNoaXZlY2xlYW51cC5jIHwgMTEgKysrKysrKysr LS0KIDEgZmlsZSBjaGFuZ2VkLCA5IGluc2VydGlvbnMoKyksIDIgZGVsZXRpb25zKC0pCgpkaWZm IC0tZ2l0IGEvc3JjL2Jpbi9wZ19hcmNoaXZlY2xlYW51cC9wZ19hcmNoaXZlY2xlYW51cC5jIGIv c3JjL2Jpbi9wZ19hcmNoaXZlY2xlYW51cC9wZ19hcmNoaXZlY2xlYW51cC5jCmluZGV4IDc3MjZk MDUxNDkuLjViYzkwZmJhZGQgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4vcGdfYXJjaGl2ZWNsZWFudXAv cGdfYXJjaGl2ZWNsZWFudXAuYworKysgYi9zcmMvYmluL3BnX2FyY2hpdmVjbGVhbnVwL3BnX2Fy Y2hpdmVjbGVhbnVwLmMKQEAgLTIzLDYgKzIzLDggQEAgY29uc3QgY2hhciAqcHJvZ25hbWU7CiAK IC8qIE9wdGlvbnMgYW5kIGRlZmF1bHRzICovCiBib29sCQlkcnlydW4gPSBmYWxzZTsJCS8qIGFy ZSB3ZSBwZXJmb3JtaW5nIGEgZHJ5LXJ1biBvcGVyYXRpb24/ICovCitib29sCQlyZW1vdmVCYWNr dXBIaXN0b3J5RmlsZSA9IGZhbHNlOwkvKiByZW1vdmUgZmlsZXMgaW5jbHVkaW5nCisJCQkJCQkJ CQkJCQkgKiBiYWNrdXAgaGlzdG9yeSBmaWxlcyAqLwogY2hhcgkgICAqYWRkaXRpb25hbF9leHQg PSBOVUxMOwkvKiBFeHRlbnNpb24gdG8gcmVtb3ZlIGZyb20gZmlsZW5hbWVzICovCiAKIGNoYXIJ ICAgKmFyY2hpdmVMb2NhdGlvbjsJLyogd2hlcmUgdG8gZmluZCB0aGUgYXJjaGl2ZT8gKi8KQEAg LTExOCw3ICsxMjAsOCBAQCBDbGVhbnVwUHJpb3JXQUxGaWxlcyh2b2lkKQogCQkJICogZmlsZS4g Tm90ZSB0aGF0IHRoaXMgbWVhbnMgZmlsZXMgYXJlIG5vdCByZW1vdmVkIGluIHRoZSBvcmRlcgog CQkJICogdGhleSB3ZXJlIG9yaWdpbmFsbHkgd3JpdHRlbiwgaW4gY2FzZSB0aGlzIHdvcnJpZXMg eW91LgogCQkJICovCi0JCQlpZiAoKElzWExvZ0ZpbGVOYW1lKHdhbGZpbGUpIHx8IElzUGFydGlh bFhMb2dGaWxlTmFtZSh3YWxmaWxlKSkgJiYKKwkJCWlmICgoKElzWExvZ0ZpbGVOYW1lKHdhbGZp bGUpIHx8IElzUGFydGlhbFhMb2dGaWxlTmFtZSh3YWxmaWxlKSkgfHwKKwkJCQkocmVtb3ZlQmFj a3VwSGlzdG9yeUZpbGUgJiYgSXNCYWNrdXBIaXN0b3J5RmlsZU5hbWUod2FsZmlsZSkpKSAmJgog CQkJCXN0cmNtcCh3YWxmaWxlICsgOCwgZXhjbHVzaXZlQ2xlYW51cEZpbGVOYW1lICsgOCkgPCAw KQogCQkJewogCQkJCWNoYXIJCVdBTEZpbGVQYXRoW01BWFBHUEFUSCAqIDJdOyAvKiB0aGUgZmls ZSBwYXRoCkBAIC0yNTIsNiArMjU1LDcgQEAgdXNhZ2Uodm9pZCkKIAlwcmludGYoXygiVXNhZ2U6 XG4iKSk7CiAJcHJpbnRmKF8oIiAgJXMgW09QVElPTl0uLi4gQVJDSElWRUxPQ0FUSU9OIE9MREVT VEtFUFRXQUxGSUxFXG4iKSwgcHJvZ25hbWUpOwogCXByaW50ZihfKCJcbk9wdGlvbnM6XG4iKSk7 CisJcHJpbnRmKF8oIiAgLWIgICAgICAgICAgICAgcmVtb3ZlIGZpbGVzIGluY2x1ZGluZyBiYWNr dXAgaGlzdG9yeSBmaWxlc1xuIikpOwogCXByaW50ZihfKCIgIC1kICAgICAgICAgICAgIGdlbmVy YXRlIGRlYnVnIG91dHB1dCAodmVyYm9zZSBtb2RlKVxuIikpOwogCXByaW50ZihfKCIgIC1uICAg ICAgICAgICAgIGRyeSBydW4sIHNob3cgdGhlIG5hbWVzIG9mIHRoZSBmaWxlcyB0aGF0IHdvdWxk IGJlIHJlbW92ZWRcbiIpKTsKIAlwcmludGYoXygiICAtViwgLS12ZXJzaW9uICBvdXRwdXQgdmVy c2lvbiBpbmZvcm1hdGlvbiwgdGhlbiBleGl0XG4iKSk7CkBAIC0yOTQsMTAgKzI5OCwxMyBAQCBt YWluKGludCBhcmdjLCBjaGFyICoqYXJndikKIAkJfQogCX0KIAotCXdoaWxlICgoYyA9IGdldG9w dChhcmdjLCBhcmd2LCAiZG54OiIpKSAhPSAtMSkKKwl3aGlsZSAoKGMgPSBnZXRvcHQoYXJnYywg YXJndiwgImJkbng6IikpICE9IC0xKQogCXsKIAkJc3dpdGNoIChjKQogCQl7CisJCQljYXNlICdi JzoJCQkvKiBSZW1vdmUgYmFja3VwIGhpc3RvcnkgZmlsZXMgdG9vICovCisJCQkJcmVtb3ZlQmFj a3VwSGlzdG9yeUZpbGUgPSB0cnVlOworCQkJCWJyZWFrOwogCQkJY2FzZSAnZCc6CQkJLyogRGVi dWcgbW9kZSAqLwogCQkJCXBnX2xvZ2dpbmdfaW5jcmVhc2VfdmVyYm9zaXR5KCk7CiAJCQkJYnJl YWs7CgpiYXNlLWNvbW1pdDogYzVlNGVjMjkzZWE1MjczOTRmYzFkMDAwNmFiODgwNDdiM2NlNTgw ZgotLSAKMi4yNS4xCgo= --=_5385f463765c47059110ca2da633e640--