Date: Mon, 11 Apr 2016 17:18:03 -0400 (EDT)
From: Peter =?utf-8?Q?Brunnengr=C3=A4ber?= <pbrunnen@bccglobal.com>
Reply-To: Peter =?utf-8?Q?Brunnengr=C3=A4ber?= <pbrunnen@bccglobal.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>, pgsql-admin@postgresql.org
Message-ID: <109968546.289.1460409481048.JavaMail.pbrunnen@Station8.local>
In-Reply-To: 
 <A737B7A37273E048B164557ADEF4A58B5383C01D@ntex2010i.host.magwien.gv.at>
References: <1469200611.1054.1460051752072.JavaMail.pbrunnen@Station8.local>
 <2112705921.1067.1460052846289.JavaMail.pbrunnen@Station8.local>
 <A737B7A37273E048B164557ADEF4A58B5383C01D@ntex2010i.host.magwien.gv.at>
Subject: Re: postgres wal sender replication timeout during
 pg_basebackup
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: postgres wal sender replication timeout during pg_basebackup
Thread-Index: AdGRdYTHMmTvSrjyRD2BZoDw7jPvO3/0RObj
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hello Mr. Albe,

> What PostgreSQL version are you running?
  9.2

> The server error message means that the client did not send a status
> update within "wal_sender_timeout" milliseconds

  So if I understand this correctly, the wal sender must receive a message =
back from the receiver in this preset time or else think that the transmiss=
ion failed...

  9.2 doesn't seem to have the "wal_sender_timeout" parameter, and it appea=
rs than "replication_timeout" may be the name of the parameter prior to v9.=
3 so this is what I am tweaking.  I originally had "replication_timeout =3D=
 5s", and I verified that "wal_receiver_status_interval =3D 2s" per the doc=
umentation.

  You were correct that setting this value to 0 did allow the pg_basebackup=
 to complete without an error.  I plan to also try setting this value to 15=
s to see if the pg_basebackup completes in that time frame.


> Is there a firewall between client and server that could swallow such mes=
sages?
  None that I am aware of, but I will check with the Xen Hypervisor admin t=
o make sure there isn't something setup here which could also cause trouble=
 down the road.


> Avoiding SSL will also greatly speed up pg_basebackup.
  Ok.  I will give this a try as well.


Thank you ever so much for your reply and solution, it was greatly apprecia=
ted!

With kind regards. -Peter


----- Original Message -----
From: "Albe Laurenz" <laurenz.albe@wien.gv.at>
To: "Peter Brunnengr=C3=A4ber" <pbrunnen@bccglobal.com>, pgsql-admin@postgr=
esql.org
Sent: Friday, April 8, 2016 5:03:29 AM
Subject: Re: [ADMIN] postgres wal sender replication timeout during pg_base=
backup

Peter Brunnengr=C3=A4ber wrote:
>    I brought the database files over from the current single production p=
ostgres server. By this I
> mean I shutdown postgres and tar-ed up the data directory and copied it o=
ver the the cluster's Master
> node. I put the files in place, set the permissions, and was able to star=
t-up postgres on the Master
> via corosync just fine.
>=20
>    In preparing the slave, I used the pg_basebackup tool to bring the dat=
abase over from the Master
> and this is where I keep having issues. As it is transferring, at about 5=
7% I see the error:
>=20
> >  $ pg_basebackup -h db-master -U u_repl -D /db/data/postgresql/9.2/main=
/ -X stream -P
> >  pg_basebackup: could not receive data from WAL stream: SSL connection =
has been closed unexpectedly
> >  176472/176472 kB (100%), 1/1 tablespace
> >  pg_basebackup: child process exited with error 1`
>=20
> And on the server, I see:
>=20
> >  2016-04-06 21:05:31 UTC LOG:  terminating walsender process due to rep=
lication timeout
>=20
>   But the transfer doesn't stop and keeps going to completion.
>=20
>   I found this [http://dba.stackexchange.com/questions/59916/streaming-re=
plication-log-is-puzzling-me]
> question on stackexchange about setting "ssl_renegotiation_limit" to 0, b=
ut this didn't make much
> difference.
>=20
>   Anyone have any ideas? I didn't find any reference to this problem in t=
he mailing list archives. I
> am completely baffled as to why this would error, but keep on going. Mayb=
e this isn't a problem at
> all?  It is the same procedure I used in the lab setup... the only differ=
ence is that the production
> database is much bigger in size.

ssl_renegotiation_limit would also have been my first guess.
What PostgreSQL version are you running?

The server error message means that the client did not send a status update
within "wal_sender_timeout" milliseconds, see
http://www.postgresql.org/docs/current/static/runtime-config-replication.ht=
ml#GUC-WAL-SENDER-TIMEOUT

Does pg_basebackup succeed if you set "wal_sender_timeout" to zero?

Is there a firewall between client and server that could swallow such messa=
ges?

Could you try without SSL (e.g. set the environment variable PGSSLMODE to "=
disable")
an see if that makes the problem go away?
Avoiding SSL will also greatly speed up pg_basebackup.

Yours,
Laurenz Albe

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin