pg_archivecleanup not deleting anything?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general