Subject: Re: wal exist in slave but getting err requested WAL segment has
 already been removed
To: pgsql-admin@lists.postgresql.org
References: 
 <CA+t6e1kW7vihQesZhwToxa2HQ1zMj5Jr=mM=Rh0cXC+SofkKig@mail.gmail.com>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <9f931b88-725f-7020-ebb2-a2446b7ff9e6@matrix.gatewaynet.com>
Date: Wed, 11 Jul 2018 16:26:58 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1kW7vihQesZhwToxa2HQ1zMj5Jr=mM=Rh0cXC+SofkKig@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------8591CB29E91B01889A9E0E9A"
Content-Language: en-US
Precedence: bulk

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

On 11/07/2018 16:09, Mariel Cherkassky wrote:
> Hi,
> I have in my cluster 3 nodes (1 master version 9.6.3+ 2 slaves version 9.6.3). I configured repmgr (with repmgrd active) v 4.0.4.
>
> Suddenly today after a few good weeks I noticed that there is a lag in one of the slaves and the error in the log indicated that the slave didnt get the wal :
>
> could not receive data from WAL stream: ERROR: requested WAL segment 0000000900002E61000000BD has already been removed
>
> However, when I check if the wal was recieveed :
> postgres=# select pg_is_in_recovery(),pg_is_xlog_replay_paused(),pg_last_xlog_receive_location(),pg_last_xlog_replay_location();
>  pg_is_in_recovery | pg_is_xlog_replay_paused | pg_last_xlog_receive_location | pg_last_xlog_replay_location
> -------------------+--------------------------+-------------------------------+------------------------------
>  t                 | f                        | 2E61/BDF5C000                 | 2E61/BDF5B930
> (1 row)
>
> and  I checked in pg_xlog directory :
> ls -l ../pg_xlog/0000000900002E61000000BD
> -rw------- 1 postgres postgres 16777216 Jul 11 11:13 ../pg_xlog/0000000900002E61000000BD
>
> and the xlog is exist.

In which node did you check for the file?
If the file in the primary is still available, try to compare their md5sum .
If you have a working WAL shipping method in place, then add the appropriate line in the recovery.conf of your standby :

restore_command = 'rsync somemachine:/somepath/pitr/%f "%p" '


> Now is my question, why the wal wasnt replayed ?
> In my repmgr.conf I dont have any parameters regarding recovery just some basic things.  The recovery.conf file in the data directory :
>
> standby_mode = 'on'
> primary_conninfo = 'host=xxxxxxx user=repmgr application_name=''psgsqldb2'' connect_timeout=2'
> recovery_target_timeline = 'latest'
>
>
> any idea ?


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


--------------8591CB29E91B01889A9E0E9A
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 text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 11/07/2018 16:09, Mariel Cherkassky
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+t6e1kW7vihQesZhwToxa2HQ1zMj5Jr=mM=Rh0cXC+SofkKig@mail.gmail.com">
      <div dir="rtl">
        <div dir="ltr">Hi,</div>
        <div dir="ltr">I have in my cluster 3 nodes (1 master version
          9.6.3+ 2 slaves version 9.6.3). I configured repmgr (with
          repmgrd active) v 4.0.4.</div>
        <div dir="ltr"><br>
        </div>
        <div dir="ltr">Suddenly today after a few good weeks I noticed
          that there is a lag in one of the slaves and the error in the
          log indicated that the slave didnt get the wal : </div>
        <div dir="ltr"><br>
        </div>
        <div dir="ltr">could not receive data from WAL stream: ERROR: 
          requested WAL segment 0000000900002E61000000BD has already
          been removed<br>
        </div>
        <div dir="ltr"><br>
        </div>
        <div dir="ltr">However, when I check if the wal was recieveed : </div>
        <div dir="ltr">
          <div dir="ltr">postgres=# select
pg_is_in_recovery(),pg_is_xlog_replay_paused(),pg_last_xlog_receive_location(),pg_last_xlog_replay_location();</div>
          <div dir="ltr"> pg_is_in_recovery | pg_is_xlog_replay_paused |
            pg_last_xlog_receive_location |
            pg_last_xlog_replay_location </div>
          <div dir="ltr">-------------------+--------------------------+-------------------------------+------------------------------</div>
          <div dir="ltr"> t                 | f                        |
            2E61/BDF5C000                 | 2E61/BDF5B930</div>
          <div dir="ltr">(1 row)</div>
          <div dir="ltr"><br>
          </div>
          <div>and  I checked in pg_xlog directory : </div>
          <div>
            <div>ls -l ../pg_xlog/0000000900002E61000000BD</div>
            <div>-rw------- 1 postgres postgres 16777216 Jul 11 11:13
              ../pg_xlog/0000000900002E61000000BD</div>
          </div>
          <div><br>
          </div>
          <div>and the xlog is exist.</div>
        </div>
      </div>
    </blockquote>
    <br>
    In which node did you check for the file?<br>
    If the file in the primary is still available, try to compare their
    md5sum .<br>
    If you have a working WAL shipping method in place, then add the
    appropriate line in the recovery.conf of your standby :<br>
    <pre class="programlisting">restore_command = 'rsync somemachine:/somepath/pitr/%f "%p" '</pre>
    <br>
    <blockquote type="cite"
cite="mid:CA+t6e1kW7vihQesZhwToxa2HQ1zMj5Jr=mM=Rh0cXC+SofkKig@mail.gmail.com">
      <div dir="rtl">
        <div dir="ltr">
          <div>Now is my question, why the wal wasnt replayed ?</div>
          <div>In my repmgr.conf I dont have any parameters regarding
            recovery just some basic things.  The recovery.conf file in
            the data directory : </div>
          <div><br>
          </div>
          <div>
            <div>standby_mode = 'on'</div>
            <div>primary_conninfo = 'host=xxxxxxx user=repmgr
              application_name=''psgsqldb2'' connect_timeout=2'</div>
            <div>recovery_target_timeline = 'latest'</div>
          </div>
          <div><br>
          </div>
          <div><br>
          </div>
          <div>any idea ? </div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <pre class="moz-signature" cols="200">-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt</pre>
  </body>
</html>

--------------8591CB29E91B01889A9E0E9A--