Обсуждение: Old WAL files under pg_xlog not getting delete

On Wed, Jan 30, 2019 at 3:58 PM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> I am trying to understand why postgresql not removing WAL logs under pg_xlog in 9.6, below are my settings
>
> wal_keep_segments=500
>
> pg_xlog/|wc -l
> 506

You told PostgreSQL to keep 500 files and it has (the number kept
apparently fluctuates a little bit).

https://www.postgresql.org/docs/11/wal-configuration.html

David J.

On 1/30/19 5:10 PM, David G. Johnston wrote:
> On Wed, Jan 30, 2019 at 3:58 PM rammohan ganapavarapu
> <rammohanganap@gmail.com> wrote:
>> I am trying to understand why postgresql not removing WAL logs under pg_xlog in 9.6, below are my settings
>>
>> wal_keep_segments=500
>>
>> pg_xlog/|wc -l
>> 506
> You told PostgreSQL to keep 500 files and it has (the number kept
> apparently fluctuates a little bit).
>
> https://www.postgresql.org/docs/11/wal-configuration.html

Plus the ".", ".." and archive_status directories, and lost+found if pg_xlog 
is it's own partition.  That's four of the unknown six.  Add a .backup file 
or two and all 506 files are accounted for.

-- 
Angular momentum makes the world go 'round.

On 2019-Jan-30, Ron wrote:

> Plus the ".", ".." and archive_status directories, and lost+found if pg_xlog
> is it's own partition.  That's four of the unknown six.  Add a .backup file
> or two and all 506 files are accounted for.

Hmm ... I would recommend to keep the xlog files in a subdir, not the
root of the filesystem.  So the lost+found would be, at the most, a
sibling of pg_xlog, not inside it.  Otherwise, it seems too easy to get
in trouble if for whatever reason the filesystem is not mounted, your
database starts writing files in it, and the filesystem is mounted
later.  Whatever files got written before mounting would be misteriously
"missing", and few people think to unmount the FS to see if they are in
the underlying filesystem.

Then again, if you read this advice then you're safe because next time
this happens to you, you'll remember to unmount to check, so you can
safely ignore me.  Go figure.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.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

Thank you all, but irrespective of count i have why is it keeping old files?

and how is it using those files? Is there any processes that can flush or remove those files?

On Wed, Jan 30, 2019, 4:11 PM David G. Johnston <david.g.johnston@gmail.com wrote:

On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.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

> On Jan 30, 2019, at 8:09 PM, rammohan ganapavarapu <rammohanganap@gmail.com> wrote:
>
> why is it keeping old files?

Because you configured it to:

wal_keep_segments=500

On Wed, Jan 30, 2019 at 8:09 PM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> Thank you all, but irrespective of count i have why is it keeping old files? and how is it using those files? Is
thereany processes that can flush or remove those files?
 

Others are answering these questions but I have one of my own.  Is
this database being backed up?

David J.

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@gmail.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.g.johnston@gmail.com wrote:

On Wed, Jan 30, 2019 at 5:00 PM Alvaro Herrera <alvherre@2ndquadrant.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

On Thu, Jan 31, 2019 at 11:52 AM rammohan ganapavarapu
<rammohanganap@gmail.com> wrote:
> But from postgres documentation "The number of WAL segment files in pg_xlog directory depends on min_wal_size,
max_wal_sizeand the amount of WAL generated in previous checkpoint cycles.
 

You are quoting from the v10+ documentation but using 9.6

You also need to read about archiving[1] since when it is enabled the
rules for WAL retention are modified to meet the needs of archiving,
specifically WAL cannot be removed from the host system until the
host's archiving system knows that the same file exists in the
archive.

[1] https://www.postgresql.org/docs/9.6/continuous-archiving.html#BACKUP-ARCHIVING-WAL

David J.