Re: pg_basebackup fails: could not receive data from WAL stream: server closed the connection unexpectedly

On 4/12/18 2:48 μ.μ., AYahorau(at)ibagroup(dot)eu wrote:
> Hi,
> Thank you very much for the response.
>
> I reckon we can return to more conventional approach of postgres db synchronization:
> 1) SELECT pg_start_backup('label', true);
> 2) rsync/cp  $PGDATA directory;
> 3) SELECT pg_stop_backup();
>
> In my opinion this approach looks better because it does not depend on wal_sender_timeout value.
>

But it depends on wal archiving to a safe location. Do you have this enabled, working and tested?

> I have a question. What is your opinion about *pg_basebackup *utility and its behaviour for this condition?  Is it a bug? Should it be fixed?
>
> Best regards,
> Andrei
>
>
>
> From: Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com>
> To: AYahorau(at)ibagroup(dot)eu,
> Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>, MikalaiKeida(at)ibagroup(dot)eu
> Date: 03/12/2018 15:15
> Subject: Re: pg_basebackup fails: could not receive data from WAL stream: server closed the connection unexpectedly
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
> Hi Andrei Yahorau,
>
> As it is difficult to give an exact recommendation, however, We have enabled and tested your exact error with the same parameters in our testing environment, we received the same error and able to
> resolve this problem by increasing 'wal_sender_timeout' parameter value.
>
> I think you need to test and verify before implementing it on your server according to your business, configuration requirements.
>
>
>
>
> On Mon, Dec 3, 2018 at 2:17 PM <_AYahorau(at)ibagroup(dot)eu_ <mailto:AYahorau(at)ibagroup(dot)eu>> wrote:
> Hello PostgreSQL Community!
>
>
> Not so long ago I faced the problem of database synchronization using pg_basebackup utility on linux SLES 12 machine using PostgreSQL 10.4:
>
> pg_basebackup -h host01 -U dbuser  -D /var/PostgresDb   -w/
> LOG:  standby //"pg_basebackup"//is now a synchronous standby with priority 1
> pg_basebackup: could not receive data from WAL stream: server closed the connection unexpectedly
>                 This probably means the server terminated abnormally
>                 before or //while//processing the request.
> pg_basebackup: child process exited with error 1
> pg_basebackup: removing contents of data directory //"/var/RtpPostgresDb"/
>
>
> PostgreSQL log of master server contain the following error entry:/
> terminating walsender process due to replication timeout/
>
> Currently my configuration is the following:
> wal_sender_timeout = 1s
> wal_receiver_timeout = 1s
> wal_receiver_status_interval = 10s
>
> I see that some people faced this situation:_
> __https://www.postgresql.org/message-id/109968546.289.1460409481048.JavaMail.pbrunnen%40Station8.local_
> The suggestion was increasing *wal_sender_timeout *parameter.
>
> On the one hand I understand that  if my database gets bigger it will require bigger value of *wal_sender_timeout*. On the other hand I use *wal_sender_timeout = 1s* on purpose in  order to detect
> if network bad.
>
> Could you please suggest an appropriate configuration, sensible relations between configuration parameters or any other approach of database synchronization which overcomes this issue?
>
> Thank you in advance,
> Andrei Yahorau

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