Обсуждение: number of wal file is not reduced.

Hi,

I have postgres 9.6 running server on centos 7, the number of wal file
in pg_xlog directory is above 4000 so to save some disk space, I
thought of reducing to 100.

but there is no reduction in numbers.

Please not that there is no replication is configured, the server is
independent.

What I am missing here.

•    max_wal_size = '3GB'
•    checkpoint_timeout = '15min'
•    checkpoint_completion_target=0.8
•    log_checkpoints=on
•    wal_keep_segments=100 (initially it was set to 4000)




Please suggest when the number of wal files will be reduced to approx 100.





regards,
Atul

On Mon, Jul 05, 2021 at 03:18:14PM +0530, Atul Kumar wrote:
> I have postgres 9.6 running server on centos 7, the number of wal file
> in pg_xlog directory is above 4000 so to save some disk space, I
> thought of reducing to 100.
...
> Please suggest when the number of wal files will be reduced to approx 100.

It's hard to say for sure. There are potential issues that might cause
pg to keep more wal files than configured.

Generally these kinds of things are easier solved in more interactive
medium - like IRC, or Slack.

But, to give you some start, check if:
1. archiving doesn't fail
2. archiving doesn't lag
3. there are no prepared transactions
4. there are no lagging replication slots

Best regards,

depesz

Hi ,

As I mentioned in my mail that there is no replication configured and
no archival is lagging.

my query is simple.

How to to reduce the number of wal file from 4000 to 100 in an
independent server ?



Regards.

On 7/5/21, hubert depesz lubaczewski <depesz@depesz.com> wrote:
> On Mon, Jul 05, 2021 at 03:18:14PM +0530, Atul Kumar wrote:
>> I have postgres 9.6 running server on centos 7, the number of wal file
>> in pg_xlog directory is above 4000 so to save some disk space, I
>> thought of reducing to 100.
> ...
>> Please suggest when the number of wal files will be reduced to approx
>> 100.
>
> It's hard to say for sure. There are potential issues that might cause
> pg to keep more wal files than configured.
>
> Generally these kinds of things are easier solved in more interactive
> medium - like IRC, or Slack.
>
> But, to give you some start, check if:
> 1. archiving doesn't fail
> 2. archiving doesn't lag
> 3. there are no prepared transactions
> 4. there are no lagging replication slots
>
> Best regards,
>
> depesz
>
>