Re: pg_wal folder high disk usage

Good morning Koen,

Highly appreciate your response on this.

This has clarified a little bit on the WAL files. Your insights made the 
whole thing a little bit more clear.


Kind Regards,

Paul B.

On 03/11/2024 13:59, Koen De Groote wrote:
> A possible reason for pg_wal buildup is that there is a sort of 
> replication going on(logical or physical replication) and the 
> receiving side of the replication has stopped somehow.
>
> This means: a different server that has a connection to your server 
> and is expecting to receive data. And your server is then expecting to 
> have to send data(this is the important bit). There could be multiple 
> of these connections.
>
> If even 1 of these receiving servers is down, or the network is out, 
> or there is some other reason that it is no longer requesting data 
> from your server, your server will notice it isn't getting 
> confirmation from that other side, that they have received the data. 
> As such, your postgres server will keep this data locally, expecting 
> this situation to be solved in the future, and at that point in time, 
> send all the data the other side hasn't gotten yet.
>
> This is 1 option. As long as your server is configured to expect that 
> other server to be there, and to be receiving, the buildup will 
> continue. Taking the other server offline won't help, in fact it is 
> likely the cause of the issue. The official documentation explains how 
> to get rid of replication slots, ideally your DBA should handle this.
>
> Laurenz's blogpost lays out all the options, for instance it can also 
> happen that your system is generating data so fast, the writing of the 
> WAL files cannot keep up. Or your setup also does WAL archiving and 
> the compression on that is slow.
>
> The post offers some ways to verify things, I suggest checking them out.
>
> And of course, if your DBA is back, have them look at it too.
>
> Regards,
> Koen De Groote
>
>
> On Fri, Nov 1, 2024 at 2:10 PM Greg Sabino Mullane 
> <htamfids@gmail.com> wrote:
>
>     On Fri, Nov 1, 2024 at 2:40 AM Muhammad Usman Khan
>     <usman.k@bitnine.net> wrote:
>
>         For immediate space, move older files from pg_Wal to another
>         storage but don't delete them.
>
>
>     No, do not do this! Figure out why WAL is not getting removed by
>     Postgres and let it do its job once fixed. Please recall the
>     original poster is trying to figure out what to do because they
>     are not the database admin, so having them figure out which WAL
>     are "older" and safe to move is not good advice.
>
>     Resizing the disk is a better option. Could also see if there are
>     other large files on that volume that can be removed or moved
>     elsewhere, esp. large log files.
>
>     Hopefully all of this is moot because their DBA is back from
>     leave. :)
>
>     Cheers,
>     Greg
>
>