Re: Old WAL files under pg_xlog not getting delete

The archive_command is only for making secondary copies of the WAL files
and doesn't control the files in the pg_xlogs folder itself (pg_wal in
PG10+). Secondary uses could be backups or non-streaming replicas that just
replay the WAL instead of connecting back to the primary.

What they were saying is that if you do have an archive_command set and it
fails to run, postgresql will not remove the WAL files in pg_xlog until the
archive_command successfully runs or is removed. It does this so that your
backups or replicas don't lose WAL files in the time period that the
archive_command is failing.

Keith

On Thu, Jan 31, 2019 at 1:52 PM rammohan ganapavarapu <
rammohanganap(at)gmail(dot)com> wrote:

> Jay,
>
> If there is no max limit on how many files can be on FS at some point it
> will fill up disk space right? so you are saying if we dont have correct
> archive_command , postgres will do nothing? in my case i did not set
> archive_command. But from postgres documentation *"T*he number of WAL
> segment files in pg_xlog directory depends on min_wal_size, max_wal_size* and
> the amount of WAL generated in previous checkpoint cycles. When old log
> segment files are no longer needed, they are removed or recycled (that is,
> renamed to become future segments in the numbered sequence)." * This
> statement is little confusing me with what you have said: *"**If that’s
> not correct, nothing will remove the WAL files.".*
>
> Sorry if i am asking silly questions :(
>
> Thanks,
> Ram
>
> On Thu, Jan 31, 2019 at 8:37 AM John Scalia <jayknowsunix(at)gmail(dot)com>
> wrote:
>
>> Ram,
>>
>> Remember that keep_wal_segments is a minimum setting, not a maximum one.
>> It’s telling the database to maintain at least that many segments. Plus,
>> check how you’ve set the archive_command. If that’s not correct, nothing
>> will remove the WAL files.
>> —
>> Jay
>>
>> Sent from my iPhone
>>
>> On Jan 31, 2019, at 11:22 AM, rammohan ganapavarapu <
>> rammohanganap(at)gmail(dot)com> wrote:
>>
>> Ron,
>>
>> I have changed the wal_keep_segments=100 and restarted the postgresql
>> processes but i still see 500+ files under pg_xlog, i had to run checkpoint
>> manually to clean.
>>
>> Ram
>>
>> On Wed, Jan 30, 2019 at 7:19 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>>
>>> On 1/30/19 9:09 PM, rammohan ganapavarapu wrote:
>>>
>>> Thank you all, but irrespective of count i have why is it keeping old
>>> files?
>>>
>>>
>>> Because you're telling it to!!
>>>
>>> max_wal_size= 1GB
>>> wal_keep_segments=500
>>>
>>> and how is it using those files? Is there any processes that can flush
>>> or remove those files?
>>>
>>>
>>> Change the parameter and restart the server...
>>>
>>>
>>> On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <
>>> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>>>
>>>> On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <
>>>> alvherre(at)2ndquadrant(dot)com> wrote:
>>>> > Hmm ... I would recommend to keep the xlog files in a subdir, not the
>>>> > root of the filesystem.
>>>>
>>>> They are in a subdir (under /opt/pgdata), and the OP seems likely to
>>>> have omitted the self/parent hidden directories during his check as
>>>> well. Plus per the docs we actually expect 501 so there isn't a need
>>>> to account for 6 extra, just 5.
>>>>
>>>> Dave
>>>>
>>>>
>>> --
>>> Angular momentum makes the world go 'round.
>>>
>>