Thread: pg_archivecleanup not deleting anything?
Hello, I'm running Postgres 9.3 in a warm standby configuration, and the slave has this setting in recovery.conf: archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup /secure/pgsql/archive/ %r' But I noticed that the archive directory had files going back to February 2014: $ ls -ltr archive | head total 9126292 -rw------- 1 postgres postgres 300 Feb 15 2014 000000010000000000000002.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 000000010000000000000003.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 000000010000000000000004.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 000000010000000000000006.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 000000010000000000000007.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 00000001000000000000000B.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 00000001000000000000000C.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 00000001000000000000000E.00000028.backup -rw------- 1 postgres postgres 300 Feb 15 2014 00000001000000000000000F.00000028.backup And even if I run the command by hand, nothing is actually deleted: $ pg_archivecleanup -d /secure/pgsql/archive/ 00000001000000000000000F.00000028.backup pg_archivecleanup: keep WAL file "/secure/pgsql/archive//00000001000000000000000F" and later Despite the message, and the same files remain. Does anyone have any idea why pg_archivecleanup isn't deleting anything? Thanks, Paul
On 11/02/2015 07:44 AM, Paul Jungwirth wrote: > Hello, > > I'm running Postgres 9.3 in a warm standby configuration, and the slave > has this setting in recovery.conf: > > archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup > /secure/pgsql/archive/ %r' > > But I noticed that the archive directory had files going back to > February 2014: > > $ ls -ltr archive | head > total 9126292 > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000002.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000003.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000004.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000006.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000007.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000B.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000C.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000E.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000F.00000028.backup > > And even if I run the command by hand, nothing is actually deleted: > > $ pg_archivecleanup -d /secure/pgsql/archive/ > 00000001000000000000000F.00000028.backup > pg_archivecleanup: keep WAL file > "/secure/pgsql/archive//00000001000000000000000F" and later > > Despite the message, and the same files remain. It is doing what you ask. See here: http://www.postgresql.org/docs/current/static/pgarchivecleanup.html "When used as a standalone program all WAL files logically preceding the oldestkeptwalfile will be removed from archivelocation. In this mode, if you specify a .backup file name, then only the file prefix will be used as the oldestkeptwalfile. This allows you to remove all WAL files archived prior to a specific base backup without error. For example, the following example will remove all files older than WAL file name 000000010000003700000010:" From what I can see you picked the oldest backup and told pg_archivecleanup to keep everything newer, which is what it did. So if you want to remove older files start at a newer backup. > > Does anyone have any idea why pg_archivecleanup isn't deleting anything? As to why it is not cleaning up as part of archive_cleanup_command, that will need more information, namely how are the WALs being archive? Or to put it another way what is the archive_command? > > Thanks, > Paul > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/02/2015 07:44 AM, Paul Jungwirth wrote: > Hello, > > I'm running Postgres 9.3 in a warm standby configuration, and the slave > has this setting in recovery.conf: > > archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup > /secure/pgsql/archive/ %r' > > But I noticed that the archive directory had files going back to > February 2014: > > $ ls -ltr archive | head > total 9126292 > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000002.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000003.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000004.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000006.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000007.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000B.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000C.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000E.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000F.00000028.backup > > And even if I run the command by hand, nothing is actually deleted: > > $ pg_archivecleanup -d /secure/pgsql/archive/ > 00000001000000000000000F.00000028.backup > pg_archivecleanup: keep WAL file > "/secure/pgsql/archive//00000001000000000000000F" and later > > Despite the message, and the same files remain. Aarg, missed the -r on the ls above. Ignore previous comment. Is there anything else beside *.backup files in the directory? > > Does anyone have any idea why pg_archivecleanup isn't deleting anything? > > Thanks, > Paul > > > -- Adrian Klaver adrian.klaver@aklaver.com
Paul Jungwirth wrote: > I'm running Postgres 9.3 in a warm standby configuration, and the slave > has this setting in recovery.conf: > > archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup > /secure/pgsql/archive/ %r' > > But I noticed that the archive directory had files going back to > February 2014: > > $ ls -ltr archive | head > total 9126292 > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000002.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000003.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000004.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000006.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 000000010000000000000007.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000B.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000C.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000E.00000028.backup > -rw------- 1 postgres postgres 300 Feb 15 2014 > 00000001000000000000000F.00000028.backup > > And even if I run the command by hand, nothing is actually deleted: > > $ pg_archivecleanup -d /secure/pgsql/archive/ > 00000001000000000000000F.00000028.backup > pg_archivecleanup: keep WAL file > "/secure/pgsql/archive//00000001000000000000000F" and later > > Despite the message, and the same files remain. > > Does anyone have any idea why pg_archivecleanup isn't deleting anything? It seems like pg_archivecleanup does not delete *.backup files. These files are rather small and are only created during a backup. They contain information about the backup, so I guess it was decided that they are important enough to be kept. Yours, Laurenz Albe
> Is there anything else beside *.backup files in the directory? There were a few *.history files, and a few files with no extension, like this: 00000006000000BE00000040. Paul
On 11/02/2015 08:17 AM, Paul Jungwirth wrote: >> Is there anything else beside *.backup files in the directory? > > There were a few *.history files, and a few files with no extension, > like this: 00000006000000BE00000040. So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not the auxiliary files. The WAL files would be the ones with no extension and a size of 16 MB(unless someone changed the compile settings). > > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, > not the auxiliary files. The WAL files would be the ones with no > extension and a size of 16 MB(unless someone changed the compile settings). Okay, thank you both for the explanation! I'm glad to hear that it's not a misconfiguration on our side. I guess we should just make sure we remove older *.backup files by some other means, because thousands of 16MB files add up eventually. We had more than 30k of them! Paul
On 11/02/2015 08:41 AM, Paul Jungwirth wrote: >> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, >> not the auxiliary files. The WAL files would be the ones with no >> extension and a size of 16 MB(unless someone changed the compile >> settings). > > Okay, thank you both for the explanation! I'm glad to hear that it's not > a misconfiguration on our side. I guess we should just make sure we > remove older *.backup files by some other means, because thousands of > 16MB files add up eventually. We had more than 30k of them! > The *.backup files should not be 16MB and from your original post they looked to be 300 bytes. Now if you have 30K of 16MB files then something else is going on. > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/02/2015 09:11 AM, Adrian Klaver wrote: > The *.backup files should not be 16MB and from your original post they > looked to be 300 bytes. Now if you have 30K of 16MB files then something > else is going on. Ah, you are right! Sorry for the misunderstanding. Paul
On 11/02/2015 09:21 AM, Paul Jungwirth wrote:
> On 11/02/2015 09:11 AM, Adrian Klaver wrote:
>> The *.backup files should not be 16MB and from your original post they
>> looked to be 300 bytes. Now if you have 30K of 16MB files then something
>> else is going on.
>
> Ah, you are right! Sorry for the misunderstanding.
Got to thinking about this. If you see 30K *.backup from February 2014
to now that is(rough calculations) :
postgres@test=# select 30000 / ('2015-11-02'::date -'2014-02-01'::date);
?column?
----------
46
postgres@test=# select (30000 / ('2015-11-02'::date
-'2014-02-01'::date))/ 24.0;
?column?
--------------------
1.9166666666666667
So something is doing a base backup roughly every two hours.
Is that what you would expect?
To put it another way, what is driving the archiving?
>
> Paul
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/02/2015 08:41 AM, Paul Jungwirth wrote:
>> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files,
>> not the auxiliary files. The WAL files would be the ones with no
>> extension and a size of 16 MB(unless someone changed the compile
>> settings).
>
> Okay, thank you both for the explanation! I'm glad to hear that it's not
> a misconfiguration on our side. I guess we should just make sure we
> remove older *.backup files by some other means, because thousands of
> 16MB files add up eventually. We had more than 30k of them!
Aright backwards disease in affect, so:
postgres@test=# select (30000 / ('2015-11-02'::date
-'2014-02-01'::date))/ 24.0;
?column?
--------------------
1.9166666666666667
actually is roughly twice an hour not every two hours.
>
> Paul
>
--
Adrian Klaver
adrian.klaver@aklaver.com
> So something is doing a base backup roughly every two hours. > > Is that what you would expect? No. :-) Sounds like I need to do some archeology. This is a system I inherited, so I haven't yet explored all the dark corners. Paul
On Tue, Nov 3, 2015 at 1:33 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 11/02/2015 08:17 AM, Paul Jungwirth wrote: >>> >>> Is there anything else beside *.backup files in the directory? >> >> >> There were a few *.history files, and a few files with no extension, >> like this: 00000006000000BE00000040. > > > So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not > the auxiliary files. The WAL files would be the ones with no extension and a > size of 16 MB(unless someone changed the compile settings). The docs mention that "all WAL files" preceding a given point are removed, personally I understand that as "all 16MB-size segments shall die", hence excluding backup and history files from the stack. But one may understand that "WAL files" means everything in pg_xlog, so as something that includes backup and history files. Perhaps we would gain in clarity by saying "WAL file segments, including .partial segments" in the docs, and not just "WAL files". Thoughts? -- Michael
Michael Paquier wrote: >> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >> the auxiliary files. The WAL files would be the ones with no extension and a >> size of 16 MB(unless someone changed the compile settings). > > The docs mention that "all WAL files" preceding a given point are > removed, personally I understand that as "all 16MB-size segments shall > die", hence excluding backup and history files from the stack. But one > may understand that "WAL files" means everything in pg_xlog, so as > something that includes backup and history files. Perhaps we would > gain in clarity by saying "WAL file segments, including .partial > segments" in the docs, and not just "WAL files". Thoughts? It might help to add: "Timeline history files and backup history files are not deleted." Yours, Laurenz Albe
On Tue, Nov 3, 2015 at 11:29 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Michael Paquier wrote: >>> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >>> the auxiliary files. The WAL files would be the ones with no extension and a >>> size of 16 MB(unless someone changed the compile settings). >> >> The docs mention that "all WAL files" preceding a given point are >> removed, personally I understand that as "all 16MB-size segments shall >> die", hence excluding backup and history files from the stack. But one >> may understand that "WAL files" means everything in pg_xlog, so as >> something that includes backup and history files. Perhaps we would >> gain in clarity by saying "WAL file segments, including .partial >> segments" in the docs, and not just "WAL files". Thoughts? > > It might help to add: > "Timeline history files and backup history files are not deleted." Something among those lines? -- Michael
Attachment
Michael Paquier wrote: >>> The docs mention that "all WAL files" preceding a given point are >>> removed, personally I understand that as "all 16MB-size segments shall >>> die", hence excluding backup and history files from the stack. But one >>> may understand that "WAL files" means everything in pg_xlog, so as >>> something that includes backup and history files. Perhaps we would >>> gain in clarity by saying "WAL file segments, including .partial >>> segments" in the docs, and not just "WAL files". Thoughts? >> >> It might help to add: >> "Timeline history files and backup history files are not deleted." > > Something among those lines? > + <para> > + WAL file segments and WAL file segments with <literal>.partial</literal> > + are deleted, while timeline history files and backup history files are not. > + </para> "WAL file segments with .partial" sounds strange. What about "WAL file segments (including partial ones) are deleted, while ..."? Yours, Laurenz Albe
On Wed, Nov 4, 2015 at 7:16 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Michael Paquier wrote: >>>> The docs mention that "all WAL files" preceding a given point are >>>> removed, personally I understand that as "all 16MB-size segments shall >>>> die", hence excluding backup and history files from the stack. But one >>>> may understand that "WAL files" means everything in pg_xlog, so as >>>> something that includes backup and history files. Perhaps we would >>>> gain in clarity by saying "WAL file segments, including .partial >>>> segments" in the docs, and not just "WAL files". Thoughts? >>> >>> It might help to add: >>> "Timeline history files and backup history files are not deleted." >> >> Something among those lines? > >> + <para> >> + WAL file segments and WAL file segments with <literal>.partial</literal> >> + are deleted, while timeline history files and backup history files are not. >> + </para> > > "WAL file segments with .partial" sounds strange. > What about "WAL file segments (including partial ones) are deleted, while ..."? Does that look better? -- Michael
Attachment
Michael Paquier wrote: >>> Something among those lines? >> >>> + <para> >>> + WAL file segments and WAL file segments with <literal>.partial</literal> >>> + are deleted, while timeline history files and backup history files are not. >>> + </para> >> >> "WAL file segments with .partial" sounds strange. >> What about "WAL file segments (including partial ones) are deleted, while ..."? > > Does that look better? That looks fine to me, thanks! Yours, Laurenz Albe