Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1lv7G7-0004RS-5v
	for pgsql-performance@arkaria.postgresql.org; Sun, 20 Jun 2021 23:53:47 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1lv7G5-0007F3-SW
	for pgsql-performance@arkaria.postgresql.org; Sun, 20 Jun 2021 23:53:45 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <postgresql@mailpen.com>)
	id 1lv7G5-0007Ev-KG
	for pgsql-performance@lists.postgresql.org; Sun, 20 Jun 2021 23:53:45 +0000
Received: from aws.mailpen.com ([52.43.43.172] helo=aws2.mailpen.com)
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <postgresql@mailpen.com>)
	id 1lv7Fy-0003gM-QI
	for pgsql-performance@lists.postgresql.org; Sun, 20 Jun 2021 23:53:45 +0000
Received: from [172.20.70.208] (unknown [50.35.114.95])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	by aws2.mailpen.com (Postfix) with ESMTPSA id 4F6D5C5F0E5
	for <pgsql-performance@lists.postgresql.org>;
 Sun, 20 Jun 2021 16:53:35 -0700 (PDT)
Subject: Re: Estimating wal_keep_size
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
To: pgsql-performance@lists.postgresql.org
References: <471a6e7d-928e-c899-bf9f-341ae79b04ea@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <9d4c8207-b05c-0d9a-30e8-1fab15d640a1@mailpen.com>
Date: Sun, 20 Jun 2021 16:53:35 -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: <471a6e7d-928e-c899-bf9f-341ae79b04ea@mailpen.com>
Content-Type: multipart/alternative;
 boundary="------------6B929B53335C4C24BC69A8E2"
Content-Language: en-US
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/9d4c8207-b05c-0d9a-30e8-1fab15d640a1%40mailpen.com>
Precedence: bulk

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

On 2021-06-16 17:36, 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.
>
> In my case, I get a complete replacement (in the form of "|"- 
> delimited ASCII files) of one of the SCHEMAs every Sunday.  The size 
> of that ASCII data is about 2GB, so I'm thinking of doubling that to 
> 4GB (256 WAL files) to protect me in the case of the standby being 
> unavailable during the update.  Note that a complete loss of both 
> servers is not catastrophic (I have backups);  it would just be annoying.
>

In the absence of any clear guidance, I temporarily set wal_keep_size to 
16GB & waited for the Sunday update.  That update today created just 
over 6GB of WAL files during the update, so I've set wal_keep_size to 
8GB (512 WAL files).

Oh, and wal_keep_size is NOT an upper limit restricting further WAL 
files. It's more like a minimum.

--------------6B929B53335C4C24BC69A8E2
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 17:36, Dean Gibson (DB
      Administrator) wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:471a6e7d-928e-c899-bf9f-341ae79b04ea@mailpen.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      Is this reasonable thinking?<br>
      <br>
      I'd think that one would want a <b>wal_keep_size</b> to cover the
      pending updates while the standby server might be unavailable,
      however long one might anticipate that would be.<br>
      <br>
      In my case, I get a complete replacement (in the form of "|"-
      delimited ASCII files) of one of the SCHEMAs every Sunday.  The
      size of that ASCII data is about 2GB, so I'm thinking of doubling
      that to 4GB (256 WAL files) to protect me in the case of the
      standby being unavailable during the update.  Note that a complete
      loss of both servers is not catastrophic (I have backups);  it
      would just be annoying.<br>
      <br>
    </blockquote>
    <br>
    In the absence of any clear guidance, I temporarily set
    wal_keep_size to 16GB &amp; waited for the Sunday update.  That
    update today created just over 6GB of WAL files during the update,
    so I've set wal_keep_size to 8GB (512 WAL files).<br>
    <br>
    Oh, and wal_keep_size is NOT an upper limit restricting further WAL
    files. It's more like a minimum.<br>
  </body>
</html>

--------------6B929B53335C4C24BC69A8E2--