Re: Wal files - Question | Postgres 9.2

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?
Otherwise what's the purpose of the wal_files if not be responsible to
contain the essential data to the DB be up-to-date?

Thanks!