Thread: pg_archivecleanup not deleting anything?

pg_archivecleanup not deleting anything?

From
Paul Jungwirth
Date:
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



Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Albe Laurenz
Date:
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

Re: pg_archivecleanup not deleting anything?

From
Paul Jungwirth
Date:
> 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


Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Paul Jungwirth
Date:
> 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



Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Paul Jungwirth
Date:
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



Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Adrian Klaver
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Paul Jungwirth
Date:
> 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



Re: pg_archivecleanup not deleting anything?

From
Michael Paquier
Date:
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


Re: pg_archivecleanup not deleting anything?

From
Albe Laurenz
Date:
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

Re: pg_archivecleanup not deleting anything?

From
Michael Paquier
Date:
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

Re: pg_archivecleanup not deleting anything?

From
Albe Laurenz
Date:
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

Re: pg_archivecleanup not deleting anything?

From
Michael Paquier
Date:
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

Re: pg_archivecleanup not deleting anything?

From
Albe Laurenz
Date:
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