Subject: Re: Estimating wal_keep_size
To: pgsql-performance@lists.postgresql.org
References: <471a6e7d-928e-c899-bf9f-341ae79b04ea@mailpen.com>
 <20210617010222.lho6zrgnkkkqyyxp@nol>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <0df7b2f6-411a-6d9b-873d-6150fcfcc2f4@mailpen.com>
Date: Fri, 18 Jun 2021 11:13:14 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <20210617010222.lho6zrgnkkkqyyxp@nol>
Content-Type: multipart/alternative;
 boundary="------------EFFF8B1AC88E69DAA3BF9232"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/0df7b2f6-411a-6d9b-873d-6150fcfcc2f4%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------EFFF8B1AC88E69DAA3BF9232
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2021-06-16 18:02, Julien Rouhaud wrote:
> On Wed, Jun 16, 2021 at 05:36:24PM -0700, Dean Gibson (DB Administrator) wrote:
>> Is this reasonable thinking?
>>
>> I'd think that one would want a *wal_keep_size* to cover the pending updates while the standby server might be unavailable, however long one might anticipate that would be.
> It's usually a better approach to use a replication slot, to keep all the required WAL files, and only when needed.  See https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS for more details.
>
> Note that a replication slot will keep all WAL files, which might eventually lead to an outage if the standby doesn't come back before the filesystem containing the logs get full.  You can cap the maximum amount of retained WAL files since pg 13 using max_slot_wal_keep_size, see
> https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE.

Granted, but the same question arises about the value for 
max_slot_wal_keep_size.  Setting either too low risks data loss, & 
setting either too high results in unnecessary disk space used.  The 
question was, is the estimated VALUE reasonable under the circumstances?

--------------EFFF8B1AC88E69DAA3BF9232
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2021-06-16 18:02, Julien Rouhaud
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20210617010222.lho6zrgnkkkqyyxp@nol">
      <pre class="moz-quote-pre" wrap="">On Wed, Jun 16, 2021 at 05:36:24PM -0700, Dean Gibson (DB Administrator) wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Is this reasonable thinking?

I'd think that one would want a *wal_keep_size* to cover the pending updates while the standby server might be unavailable, however long one might anticipate that would be.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
It's usually a better approach to use a replication slot, to keep all the required WAL files, and only when needed.  See <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS">https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS</a> for more details.

Note that a replication slot will keep all WAL files, which might eventually lead to an outage if the standby doesn't come back before the filesystem containing the logs get full.  You can cap the maximum amount of retained WAL files since pg 13 using max_slot_wal_keep_size, see
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE">https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE</a>.
</pre>
    </blockquote>
    <br>
    Granted, but the same question arises about the value for
    max_slot_wal_keep_size.  Setting either too low risks data loss,
    &amp; setting either too high results in unnecessary disk space
    used.  The question was, is the estimated VALUE reasonable under the
    circumstances?<br>
  </body>
</html>

--------------EFFF8B1AC88E69DAA3BF9232--