Thread: streaming replication timeout error

On 10/08/2013 07:58 PM, 高健 wrote:
> Hello:
>
> My customer encountered some connection timeout, while using one
> primary-one standby streaming replication.
>
> The original log is japanese, because there are no error-code like
> oracle's ora-xxx,
> I tried to translate the japanese information into English, But that
> might be not correct English for PG.
>
>
> The most important part is:
>
> 2013-09-22 09:52:47 JST[28297][51d1fbcb.6e89-2][0][XX000]FATAL:  Could
> not receive data from WAL stream: could not receive data from server:
> connection timeout
> scp: /opt/PostgresPlus/9.2AS/data/arch/000000AC000001F10000004A: No such
> file or directory
>
> I was asked about:
> In what occasion will the above fatal error occur?
>
> I looked into the postgresql.conf file for the primary and standby server.
> And made some experiences.
>
> I found:
> Senario I:
>
> If the wal file wanted is removed manually：
>
> Both in primary and standby, log will be like this:
> FATAL:  could not receive data from WAL stream: FATAL:  requested WAL
> segment 000000010000000000000011 has already been removed
>
>

>
> But I haven't found a  good explanation fitting the logs' FATAL error.
> Can anybody give me some info?

Would seem to me the interesting part is:

  scp: /opt/PostgresPlus/9.2AS/data/arch/000000AC000001F10000004A: No
such  file or directory

Are using scp to move WAL files to an archive directory?

If so, it seems scp is having issues, either network interruption or the
file is disappearing under it.

>
> Thanks in advance
>
> jian gao
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 10/09/2013 05:51 PM, 高健 wrote:
> Hello:
>
> Thanks for replying.
>
> The recovery.conf file on standby(DB2) is like that:
>
> standby_mode             = 'on'
> primary_conninfo         = 'host=DB1 port=5432 application_name=testpg
> user=postgres connect_timeout=10 keepalives_idle=5 keepalives_interval=1'
> recovery_target_timeline = 'latest'
> restore_command          = 'scp -o "ConnectTimeout 5" -i
> /opt/PostgresPlus/9.2AS/.ssh/id_edb
> DB1:/opt/PostgresPlus/9.2AS/data/arch/%f %p'
>
>
> I  am not familiar with the scp command,  I think that here scp is used
> to copy archive wal log files from primary  to standby...
>
> Maybe the ConnectionTimeout is too small, And sometimes when network is
> not very well,
> the restore_command will fail and return FATAL error?
>
> In fact I am a little confused about restore_command, we are using
> streaming replication, but why restore_command is still needed to copy
> archive wal log, isn't it  the old warm standby (file shipping)?

Best explanation is in the docs:

http://www.postgresql.org/docs/9.3/static/warm-standby.html
"
At startup, the standby begins by restoring all WAL available in the
archive location, calling restore_command. Once it reaches the end of
WAL available there and restore_command fails, it tries to restore any
WAL available in the pg_xlog directory. If that fails, and streaming
replication has been configured, the standby tries to connect to the
primary server and start streaming WAL from the last valid record found
in archive or pg_xlog. If that fails or streaming replication is not
configured, or if the connection is later disconnected, the standby goes
back to step 1 and tries to restore the file from the archive again.
This loop of retries from the archive, pg_xlog, and via streaming
replication goes on until the server is stopped or failover is triggered
by a trigger file.
"

Basically by having a restore_command and primary_conninfo you are
telling the standby to do both, following the sequence described above.

FYI ConnectTimeout is a SSH option passed to scp.

man ssh_config will get you more information.

Would seem both your streaming and archiving are using the same network,
is that correct?

If so you have a single point of failure, the network.


>
> Best Regards
> jian gao
>
>

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Oct 9, 2013 at 9:58 AM, 高健 <luckyjackgao@gmail.com> wrote:

> The most important part is:
>
> 2013-09-22 09:52:47 JST[28297][51d1fbcb.6e89-2][0][XX000]FATAL:  Could not
> receive data from WAL stream: could not receive data from server: connection
> timeout
> scp: /opt/PostgresPlus/9.2AS/data/arch/000000AC000001F10000004A: No such
> file or directory
>
> I was asked about:
> In what occasion will the above fatal error occur?

It is a network error. The TCP/IP socket died somehow, and the FATAL
error logged. PostgreSQL then fell back to using the restore_command
defined in your recovery.conf file, but the WAL file had not yet been
shipped and the output logged. I imagine streaming replication happily
reconnected soon after. This is all quite normal.



--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/