Re: Wal files - Question | Postgres 9.2

On Wed, Nov 23, 2016 at 1:59 PM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:

>
>
> 2016-11-23 15:55 GMT+13:00 Venkata B Nagothi <nag1010(at)gmail(dot)com>:
>
>>
>>
>> On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbakerbr(at)gmail(dot)com>
>> wrote:
>>
>>> Hi guys,
>>>
>>> I currently have a slave02 server that is replicating from another
>>> slave01 via Cascading replication. The master01 server is shipping
>>> wal_files (via ssh) to both slaves.
>>>
>>>
>>> I'm doing some tests on slave02 to test the recovery via wal_files...
>>> The goal here is to stop postgres, wait few minutes, start postgres again,
>>> watch it recovering from wal_files, once it's done see the streaming
>>> replication start working again.
>>>
>>> 1 - Stop postgres on slave02(streaming replication + wal_files)
>>> 2 - Wait for 5 minutes
>>> 3 - Start postgres - The goal here is to tail the logs to see if the
>>> wal_files are being successfully recovered
>>>
>>> However, when doing step3 I get these messages:
>>>
>>> cp: cannot stat '/walfiles/0000000200001AF8000000A4': No such file or
>>> directory
>>>
>>> cp: cannot stat '/walfiles/0000000200001AF8000000A5': No such file or
>>> directory
>>>
>>> cp: cannot stat '/walfiles/0000000200001AF8000000A6': No such file or
>>> directory
>>> LOG: consistent recovery state reached at 1AF8/AB629F90
>>> LOG: database system is ready to accept read only connections
>>> LOG: streaming replication successfully connected to primary
>>>
>>>
>>>
>>> still on slave01: *Sometimes the log_delay time is bigger.. sometimes
>>> is lower*
>>>
>>> SELECT CASE WHEN pg_last_xlog_receive_location() =
>>> pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
>>> pg_last_xact_replay_timestamp()) END AS log_delay;
>>>
>>> log_delay
>>>
>>> -----------
>>>
>>> 0.386863
>>>
>>>
>>>
>>> On master01:
>>>
>>> select * from pg_current_xlog_location();
>>>
>>> pg_current_xlog_location
>>>
>>> --------------------------
>>>
>>> 1AF8/D3F47A80
>>>
>>>
>>>
>>> *QUESTION:*
>>>
>>> So.. I just wanna understand what's the risk of those errors... what's
>>> happening?
>>> *cp: cannot stat '/walfiles/0000000200001AF8000000A5': No such file or
>>> director*y - Means it didn't find the file. However, the file exists on
>>> the Master, but it didn't start shipping yet. What are the consequences of
>>> that?
>>>
>>
>> That is just saying that the slave cannot find the WAL file. That should
>> not be of big importance. Eventually, that will vanish when the log file
>> gets shipped from the master. Also "cp: cannot stat." errors have been been
>> fixed in 9.3 i believe.
>>
>
> Hi Venkata !
>
> Yeah that's fine.. the streaming replication is already working fine.
>
> But, as it didn't find/recover some of the wal_files, doesn't that mean
> that the DB isn't up-to-date?
>

Not necessarily. Standby periodically checks if the WAL file it is looking
for is available at restore_command location and generates that message if
the file is not available. These messages are not of any harm.

Below link might help you :

https://www.postgresql.org/message-id/4DDC9515.2000003%40enterprisedb.com

Regards,
Venkata B N
Database Consultant

Fujitsu Australia