Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fdFLh-0005wV-Ev for pgsql-admin@arkaria.postgresql.org; Wed, 11 Jul 2018 13:40:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fdFLe-0001ZA-2k for pgsql-admin@arkaria.postgresql.org; Wed, 11 Jul 2018 13:40:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fdFLd-0001Yt-R1 for pgsql-admin@lists.postgresql.org; Wed, 11 Jul 2018 13:40:01 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fdFLZ-0005wh-Sm for pgsql-admin@lists.postgresql.org; Wed, 11 Jul 2018 13:40:01 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id w6BDdn0S017991 for ; Wed, 11 Jul 2018 16:39:54 +0300 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id w6BDdnOT095895 for ; Wed, 11 Jul 2018 16:39:49 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: wal exist in slave but getting err requested WAL segment has already been removed To: pgsql-admin@lists.postgresql.org References: <9f931b88-725f-7020-ebb2-a2446b7ff9e6@matrix.gatewaynet.com> From: Achilleas Mantzios Message-ID: <49a8b5fc-e181-ff57-ea51-f9a2753b4dbc@matrix.gatewaynet.com> Date: Wed, 11 Jul 2018 16:39:49 +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: Content-Type: multipart/alternative; boundary="------------AFA081BC6F92DE02CCAEE150" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.98.7 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-39.5 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_IT_PARTNERS,DYN_HAM_NAUTICAL,DYN_HAM_SHORT_BODY, HTML_MESSAGE autolearn=unavailable version=3.3.1 X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------AFA081BC6F92DE02CCAEE150 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 11/07/2018 16:32, Mariel Cherkassky wrote: > The wal is available on the standby, not on the primary. It is already in the pg_xlog directory of the slave... Ok but apparently this is not complete. Can you see its contents with pg_waldump (or pg_xlogdump) ? Do you have any backup mechanism in place? Any WAL shipping / archiving mechanism ? > > 2018-07-11 16:26 GMT+03:00 Achilleas Mantzios >: > > 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 > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------AFA081BC6F92DE02CCAEE150 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 11/07/2018 16:32, Mariel Cherkassky wrote:
The wal is available on the standby, not on the primary. It is already in the pg_xlog directory of the slave...
Ok but apparently this is not complete. Can you see its contents with pg_waldump (or pg_xlogdump) ?
Do you have any backup mechanism in place? Any WAL shipping / archiving mechanism ?

2018-07-11 16:26 GMT+03:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:
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


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