Обсуждение: Wal files - Question | Postgres 9.2

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 directory - 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?

On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbakerbr@gmail.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 directory - 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.

2016-11-23 15:55 GMT+13:00 Venkata B Nagothi <nag1010@gmail.com>:

On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbakerbr@gmail.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 directory - 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?

 

On Wed, Nov 23, 2016 at 1:59 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-11-23 15:55 GMT+13:00 Venkata B Nagothi <nag1010@gmail.com>:

On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbakerbr@gmail.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 directory - 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

On 11/24/16 8:05 PM, Patrick B wrote:

> hmm.. I really don't get it.
>
>
>
> If I get messages like:
>
> *cp: cannot stat '/walfiles/0000000200001AF8000000A5': No such file or
> director*y
>
> In my head, it's saying that it was unable to recover that file and,
> because of that, there is missing data.
> Even if the server is able to connect to tbe master via streaming
> replication, there might be missing data. There might be data that is
> into master but not into slave.
>
> Am I wrong? If so, I don't understand why are the wal_files for, then.

This is normal as far as it goes.  Postgres keeps checking for the WAL
file that it needs to become consistent and cp keeps throwing errors
when it can't find the file (yet).

This simply demonstrates how unsuitable cp is as a recovery command.  I
recommend you look at doing backup/archive with a professional tool such
as pgBackRest (http://www.pgbackrest.org) or Barman
(http://www.pgbarman.org/).

Ho

​[w]

 is that even possible?? I don't understand!

On Mon, Nov 28, 2016 at 8:22 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Ho

​[w]

 is that even possible?? I don't understand!

​https://www.postgresql.org/docs/9.2/static/warm-standby.html

"""​

 

If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up.

​"""

Basically you did just that when you destroyed the archive.  Apparently the master doesn't churn through WAL quickly enough to have had to discard the segments from the prior two hours.

David J.

​

2016-11-29 16:36 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>:

On Mon, Nov 28, 2016 at 8:22 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Ho

​[w]

 is that even possible?? I don't understand!

​https://www.postgresql.org/docs/9.2/static/warm-standby.html

"""​

 

If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up.

​"""

Basically you did just that when you destroyed the archive.  Apparently the master doesn't churn through WAL quickly enough to have had to discard the segments from the prior two hours.

David J.

​

That was really helpful! Thanks David!

Patrick 

https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html

wal_keep_segments is the parameter responsible for streaming replication be able to recover itself without using wal_files, is that right?

On Wed, Nov 30, 2016 at 6:05 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html

wal_keep_segments is the parameter responsible for streaming replication be able to recover itself without using wal_files, is that right?

[...] ​without using wal_files[sic] [from an external archive location]

David J.

​