Subject: Re: PostgreSQL 10.5 : Strange pg_wal fill-up, solved with the
 shutdown checkpoint
To: pgsql-admin@lists.postgresql.org
References: <0d25bb8c-9cc7-10bc-71ba-6a8a8c33ec49@matrix.gatewaynet.com>
 <4b18bbdd50bc58d36087e79c2768943361df3505.camel@cybertec.at>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <79cdddc4-ab2f-c663-2e1b-5af38ddd172a@matrix.gatewaynet.com>
Date: Tue, 6 Nov 2018 16:53:13 +0200
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101
 Thunderbird/60.0.1
MIME-Version: 1.0
In-Reply-To: <4b18bbdd50bc58d36087e79c2768943361df3505.camel@cybertec.at>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 6/11/18 2:41 μ.μ., Laurenz Albe wrote:
> Achilleas Mantzios wrote:
>
> If you set log_min_messages to DEBUG2, you should get a log message like
>
>     attempting to remove WAL segments older than log file ...
>
> during each checkpoint.  That would help to determine what is going on.
Thank you Laurenz, is DEBUG2 cause a great deal of excessive log lines written to the log? or is there any way to set log_min_messages to DEBUG2 only for the checkpoints (by e.g. kill -HUP the 
checkpointer )?
> The traditional things to hold back WAL segment removal are wal_keep_segments and
> replication slots, but that should also hold for a shutdown checkpoint.
wal_keep_segments is unchanged. We have only one replication slot running for the subscriber (testsmadb) (which is usually ultra verbose about replication problems) and there was nothing in the logs 
prior to the incident (or many hours before the incident) in the primary (smadb) or the subscriber to indicate anything regarding those two.
Moreover, we have monitoring in place to take metrics on the replication lag. No mail alert relevant to this.
No emails/log msgs indicating anything abnormal on smadb2 either (the non-replication-slot physical standby) .

But the connection from smadb2 to the barman host went downhill this morning, I don't know if this was still the case in Sunday or if the two incidents are connected. Today we had a much more serious 
situation. Our traffic increased by almost 10 and the net speed fell also by 10.  But at least, today I explained everything (I hope).

>
> Yours,
> Laurenz Albe


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt