Re: [GENERAL] pg_archivecleanup not deleting anything?

[Albe Laurenz]

Michael Paquier wrote:
>>> Something among those lines?
>>
>>> +  
>>> +   WAL file segments and WAL file segments with .partial
>>> +   are deleted, while timeline history files and backup history files are 
>>> not.
>>> +  
>>
>> "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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Michael Paquier]

On Wed, Nov 4, 2015 at 7:16 PM, Albe Laurenz  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?
>
>> +  
>> +   WAL file segments and WAL file segments with .partial
>> +   are deleted, while timeline history files and backup history files are 
>> not.
>> +  
>
> "WAL file segments with .partial" sounds strange.
> What about "WAL file segments (including partial ones) are deleted, while 
> ..."?

Does that look better?
-- 
Michael
diff --git a/doc/src/sgml/ref/pgarchivecleanup.sgml b/doc/src/sgml/ref/pgarchivecleanup.sgml
index 60a7fc4..c375a88 100644
--- a/doc/src/sgml/ref/pgarchivecleanup.sgml
+++ b/doc/src/sgml/ref/pgarchivecleanup.sgml
@@ -80,6 +80,10 @@ pg_archivecleanup:  removing file "archive/00010037000E"
archivelocation is a directory readable and writable by the
server-owning user.
   
+  
+   WAL file segments (including partial ones) are deleted, while timeline
+   history files and backup history files are not.
+  
  
 
  

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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Albe Laurenz]

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?

> +  
> +   WAL file segments and WAL file segments with .partial
> +   are deleted, while timeline history files and backup history files are 
> not.
> +  

"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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Michael Paquier]

On Tue, Nov 3, 2015 at 11:29 PM, Albe Laurenz  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
diff --git a/doc/src/sgml/ref/pgarchivecleanup.sgml b/doc/src/sgml/ref/pgarchivecleanup.sgml
index 60a7fc4..77ef886 100644
--- a/doc/src/sgml/ref/pgarchivecleanup.sgml
+++ b/doc/src/sgml/ref/pgarchivecleanup.sgml
@@ -80,6 +80,10 @@ pg_archivecleanup:  removing file "archive/00010037000E"
archivelocation is a directory readable and writable by the
server-owning user.
   
+  
+   WAL file segments and WAL file segments with .partial
+   are deleted, while timeline history files and backup history files are not.
+  
  
 
  

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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Albe Laurenz]

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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Michael Paquier]

On Tue, Nov 3, 2015 at 1:33 AM, Adrian Klaver  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: 000600BE0040.
>
>
> 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

[GENERAL] pg_archivecleanup not deleting anything?

[Paul Jungwirth]

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 
00010002.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
00010003.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
00010004.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
00010006.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
00010007.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
0001000B.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
0001000C.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
0001000E.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014 
0001000F.0028.backup


And even if I run the command by hand, nothing is actually deleted:

$ pg_archivecleanup -d /secure/pgsql/archive/ 
0001000F.0028.backup
pg_archivecleanup: keep WAL file 
"/secure/pgsql/archive//0001000F" and later


Despite the message, and the same files remain.

Does anyone have any idea why pg_archivecleanup isn't deleting anything?

Thanks,
Paul



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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Paul Jungwirth]

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: 000600BE0040.


Paul


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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Paul Jungwirth]

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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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: 000600BE0040.


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.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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 3 / ('2015-11-02'::date -'2014-02-01'::date);
 ?column?
--
   46



postgres@test=# select (3 / ('2015-11-02'::date 
-'2014-02-01'::date))/ 24.0;

  ?column?

 1.9167


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.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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 (3 / ('2015-11-02'::date 
-'2014-02-01'::date))/ 24.0;

  ?column?

 1.9167

actually is roughly twice an hour not every two hours.



Paul




--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Paul Jungwirth]

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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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
00010002.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010003.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010004.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010006.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010007.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000B.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000C.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000E.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000F.0028.backup

And even if I run the command by hand, nothing is actually deleted:

$ pg_archivecleanup -d /secure/pgsql/archive/
0001000F.0028.backup
pg_archivecleanup: keep WAL file
"/secure/pgsql/archive//0001000F" 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.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Adrian Klaver]

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
00010002.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010003.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010004.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010006.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
00010007.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000B.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000C.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000E.0028.backup
-rw--- 1 postgres postgres  300 Feb 15  2014
0001000F.0028.backup

And even if I run the command by hand, nothing is actually deleted:

$ pg_archivecleanup -d /secure/pgsql/archive/
0001000F.0028.backup
pg_archivecleanup: keep WAL file
"/secure/pgsql/archive//0001000F" 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 
000100370010:"


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.kla...@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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Albe Laurenz]

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
> 00010002.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 00010003.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 00010004.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 00010006.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 00010007.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 0001000B.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 0001000C.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 0001000E.0028.backup
> -rw--- 1 postgres postgres  300 Feb 15  2014
> 0001000F.0028.backup
> 
> And even if I run the command by hand, nothing is actually deleted:
> 
> $ pg_archivecleanup -d /secure/pgsql/archive/
> 0001000F.0028.backup
> pg_archivecleanup: keep WAL file
> "/secure/pgsql/archive//0001000F" 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

Re: [GENERAL] pg_archivecleanup not deleting anything?

[Paul Jungwirth]

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