Thread: Wals not recycling

Hi All, 

I am facing an issue with wal recycling in one of my Postgresql servers running ina rhel box and the PG version is 10. The issue is that wal files are not recycling at all. Please note that its a qa server and the load is quite high on it. It is running in no archive mode with fsync on, max_wal_size is 8GB with checkpoint_timeout of 5mins and default checkpoint_completion_target of 0.5. Could you please give me some pointers what could be wrong, if it is hardware that is problem here, how can I collect some facts about it so that 8 can take this matter with system guys. 
Average wal generation is approximately 9k per day
I have enabled log_checkpoint in my server and I can see it says checkpoint complete: wrote ###buffers, 0 wal files added, 0 removed, 0 recycled,,,, LogCheckpointEnd
I have no active replication slots in this database cluster.Also the wal_keep_segments is set to 0. Appreciate your help! 

Thanks, 

Rijo Roy 

2018年12月20日(木) 21:18 Rijo Roy <rjo_roy@yahoo.com>:
>
> Hi All,
>
> I am facing an issue with wal recycling in one of my Postgresql servers running ina rhel box and the PG version is
10.The issue is that wal files are not recycling at all. Please note that its a qa server and the load is quite high on
it.It is running in no archive mode with fsync on, max_wal_size is 8GB with checkpoint_timeout of 5mins and default
checkpoint_completion_targetof 0.5. Could you please give me some pointers what could be wrong, if it is hardware that
isproblem here, how can I collect some facts about it so that 8 can take this matter with system guys. 
> Average wal generation is approximately 9k per day
> I have enabled log_checkpoint in my server and I can see it says checkpoint complete: wrote ###buffers, 0 wal files
added,0 removed, 0 recycled,,,, LogCheckpointEnd 
> I have no active replication slots in this database cluster.Also the wal_keep_segments is set to 0. Appreciate your
help!

You say you have no *active* replication slots - does that imply you
have at least one *inactive* replication slot? If so, that will cause
WAL files to be retained indefinitely.


Regards

Ian Barwick

--
   Ian Barwick                   http://www.2ndQuadrant.com/
   PostgreSQL Development, 24x7 Support, Training & Services

2018年12月20日(木) 21:58 Rijo Roy <rjo_roy@yahoo.com>:
>
> Thanks sk for replying Ian.. I have one inactive replication slot in the database. I suspected that could be the
causebut was not sure of it. Could you please tell me what I should be doing in this case. Can I drop the replication
slotsince it is no more used and delete old wal files or will PG be able to recycle them by itself. 

Yes, just drop the replication slot and the excess files will be removed.

Regards

Ian Barwick

--
  Ian Barwick                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services