Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m71kQ-00032h-Fc for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 20:26:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m71kM-0007AI-9a for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 20:26:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m71kL-00078f-Vf for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 20:26:14 +0000 Received: from sonic303-22.consmr.mail.ne1.yahoo.com ([66.163.188.148]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1m71kF-0005wh-Rn for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 20:26:11 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1627071965; bh=3mVdRSxQcfS3xfoG8HSYJ8KASbxZqetSyHrl63a/ddA=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=DKsLQXATsg8W1IAGlAXxiYDARIVCSXpLaWa8vOKJxJe3TqIVv/tvr3zY1OZhpacUrM/HIWeTVbD4aDeGtOsyjmHjV1hWkGujr1gysmVMQe//MJo01p3N4YX2jAW2SDf8WVnallFaFbFTEysLlNKo6PYqt+8yo9ri6Uv16eQoOWsWDj5UtEe99mvWDaTzji7FS6ID4aA2fvGlCuOkZHvuly01MLgp0BJ9cTQE9e5F4s//3iDxBavG5H4MaB1wMndlRuTtgPxSG4uNN86ul7spAxqPpluVrELq61Wdmo6MelHxOrOhgzZ5IH1knQFqp4ehixvy+Kc8ic7aXzkzTKOvkw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1627071965; bh=hKZAWPCBYBMG3WKQaiFSD780mRdRFbSndBCOt2QEktW=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=ZP50LXxCOJCB/7rMESPEpD+LGqs4C0v0ogN5U4spYeFxhWinpzvoRiYUrGjirKmaqZQXmQsvPcb4o4boi59TfvubKYaA9yXxyyaNC10v2rhDT1vKjbhAUGo05acom0Xgm/pqfOAGj7vryj+Ed0zUtjmmNkT8MZagx63nC1KLVyOfOthat+Dt1aLkKNUoR0umeBvWEgiLUNXVc+OQEQl5rLYiQvWOBLBIU3Il4R1/5zOhkTpqKOf7c3zhavAg0grIKqBh0AHExiPzAlMoj8+t2hcUdPsfD2eOsv1leSFu68G35Oh7dyR0Hoqe44QyZhFLtoo8wCi6MsdKhTkmIVTMtg== X-YMail-OSG: Vz7Jix0VM1nY.Jd66jDE6BY_xcNyONMABo9j5IBdAjuyu05T2QPuA_Y052XVTsC 0RtYb_ycZ3zGTn.iYAfeNZa5ucUY4OvF7HmS23WqnsHZCBgzS.R86W749C4pc2dfarwKh5loZ_2J Pc2SBbFyyZW0bUHWI4pAchM3IcvRoUXXaLbiB7uVlgQdvXevyU5ee.zCYdPnsHU8yTQy_rrjKdra vjow_Jc2r2wWdZxkVvpJ3PJB8lafMHMOU3jZp5A2cb6BUQe4S0Wi3bgWBu5fcw9ivpDKydIamTa1 tIrQA.MArh4nsvbKDNAG.zn0qL9JJ09MpxmSQXcszDFGPgCpKiK3oK0dhCeWWiL_MUV7XNBw_Sfk x6NeAF1aX8XiZuzWn.96bAAsJnAQaAGxvaUBCaY12M2ic6mozjykX0qmcv8H_J.cYfV2CwRVEzsr 1p9ydBWONsKuKuqnol3C32FcGpWesoRFA7jjDejJLgfY_W5uGo.UFrJXfGJuGbQb1V9WXjc9juPm U7_iMJ88HKxQSTaVwz9UlsuuPfOyduBWB83phjhjlNlXS7ub63WnFT3oOT5hZVhlgSNRovuW3Ohu 1n5EEmsmoVS.xggYESjGnC3KvSyIo3XJ1CVXtnp8sZcjc3g4SD7xZcGjvPGogxERT5gbq8n_B0R0 yyl.KERmG.g9EpqSawJ15glFoX..AseYe39AvBFsGe8xMSAmv9G3OaBdIjAWHgWZJ.ehdO3KFxVe kx_8GoTQ.ZDCBQOdV0BYLdsAZC4eYx6x5VbivLj3DNcH0vi1GEq0GNmFOYNKjF9klzbXFgYy.QZP EXoPv617SnU_CEh0OZ65HuvOx0jcu7O84MLEKxXfGWcgVHsv7gN.hvCE8koG4xh.UApn89ezL1.F s6UFs.Qorpy6cm.1wznzQ6Th.EI06KSJ3FgbAQI8yUx8_mvYUMiz760wQncijLwY3_B1KmArUKDs LfXyejKA4gI9NM09bxFPEYy4t0FNmbQH4VhzArFbvcnJH.2fWNMNpcDk4cKdphY8EDNvb3v63ny0 2sAdm4Hw5BULeSS4fcF.C64gosADdHqLFfR6HqEk8SxH4Z7cnGna.kPMlYg8n9VysuUJyFC4NFmv uWXeuL6SWWypeDggONBrdD7kd1cDXNIwLXwElhwGVWdLDk.TKmeuxt.UjXKewfSobk4HoKlJUU9P gaAAvWzUoOv827ErUHL9zKS0ECONSno1z3JSnNFyuIgdy70Z4yJWrzytZHQNkNo8nUV2nrBkDURU yxw0e2anIzWH06znvbM9ag9aFzbQItWzCLhqc6PKceJunZej1ezoEJZ0QxESrOB3tTWV_X06JW15 M08MxB_9_4eAoTPaC6aJWiAvXxIMxhQ9REKCUyCevolYP94n1mQEg2dsSF0aDl_bf1P22tBoSzkL R7osDdPpeSOwe8TL5v6WXkL2uhcG6Rxaj.uP71T9_9PghMIdU27HO9GfW26fKsBNvg6ANQX9IOGo cuw1Ez3wJC0zfn5Nmta4DXmHMsPjCpOpDClHtK1CUFjWp.kK3eAkj5DJ4TomzImZxb8OnodMHY40 LBCxQ5JxW7KX8MDopHkRiyE1L7wFznpjQ.bvY_01m4HVvEB0N.2wbnUY8mxQ4gvNEXocQH4oKdhn aKO.RRXhOML27k.Ffi7C71ANcb7MVwOw.xD07epdD7g_Fl4g5VCSjU_4KNxByOIHLqzS6B_1sSlK 2VoNyW11M4uRo.YLZCGh.TiZz.fvkN73q5CRf1IKejWiJvLr7yHOTqRNfPISRLZgYQaTIYuPXAN9 aF7rec9suAo1lVrI3UieVWoDn5M41DIzWy2zfiwI.mF1nz3Vmp4KTcAMSoDphJmxWKNd8KlK.uXG GcmrHlD7NWkjbiNwbO1jqf53yxeWQACotMXFBUSGELpFuIw7qDbbOYYcGGFL.EsZQjnNfaaHg80A qymrTHkjXXXEAIe.a6c3tfy5Ys7_jo8SZ4Ue43BtjOVnzyH236dD0SNQVpXSaasW6wd0NMHZKClD 2M9OA5l.wkXIrj4r6iuIRC5WM0dL0DfSUGjudLenq2wgPC0QNQSDThOjbM0M1g5RrpABT5jT2NnN 67PwGkP255T3tLsnDCsGyHW2ANCFo1g_GRIqdqWh4sZmigLNdwSTykuGO1YW8xBFQoGr4PJBMFmq LvaLgCJ7Pv6QWHpttIAjrWGE4AhemcDDAdf_QpFSNZX96eWakmKjK6fdf4C1QJA5qLmN5EfkWPeG .Euyf2.5bQ2XJdcS8dJpPI609CumQcMPXW3L_HYs.ELZdeDF5Nf1dYbkHZuXNhffI8Sqt92mob5V pfHQ40CVJfhfusAswPf3YhFdjzDiz2RbHuDNrpeOkjr.x9.QGywwYCaiyCVQ3vV65xH0aUIkR7yU KvNXCA8ZzZKYX3VE56mQCeFYxL2FCPpShIoJlXoTEjJyg63ICfTzdk.Dtm1ib6g6D7.SiUKx6H_m FtMmjRscgpHCW8ECnfJq2t2roMLGvFQK5Ue28PSi3yn0HT7Sx87_y7sjyKaV4fyrovmtVjOWCSnh Lg82f4wP3YRKzgEPGPHRWVsfI1luzViqM.0CqJZxgbzvh.E1y2nnJowyjTrI4bb.4HETN6KX4kqF 7_ABmsz7A1FIFno0TOqMZiocejH3I1D_7_JA- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic303.consmr.mail.ne1.yahoo.com with HTTP; Fri, 23 Jul 2021 20:26:05 +0000 Date: Fri, 23 Jul 2021 20:26:01 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Vijaykumar Jain Cc: Alvaro Aguayo , "pgsql-admin@lists.postgresql.org" Message-ID: <1375604206.639870.1627071961896@mail.yahoo.com> In-Reply-To: References: <1396983439.574027.1627062741652.ref@mail.yahoo.com> <1396983439.574027.1627062741652@mail.yahoo.com> <940238887.612531.1627066701703@mail.yahoo.com> Subject: Re: database lagging MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_639869_164095365.1627071961893" X-Mailer: WebService/1.1.18736 YMailNorrin Content-Length: 12023 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_639869_164095365.1627071961893 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable thank you for information urlboth primary_conninfo on master and slave are = not setmax_wal_senders =3D 10max_replication_slots =3D 1's=C2=A0when I run = 'select * from pg_stat_replication/pg_replication_slots;'0 rows archive_command=3D'/opt/rh/rh-postgresql12/root/bin/syncwal.sh %p %f' syncwal.sh contains:#!/bin/bashscp $1 slave_ip:/var/opt/rh/rh-postgres12/li= b/pgsql/walarchive/$2if [ $? !=3D 0 ]then=C2=A0echo "Archiver error:"exit 1= fiexit 0 do I need to set the restore_command and archive_cleanup_command in slave's= postgresql.conf only? another issue is when I restart postgresql.service in master, it takes a li= ttle longer as usual but in slave, prompt out as soon as I hit enter to res= tart the postgresql.service after modify the postgresql.conf with archive_cleanup_command and restore_c= ommand on slave, master is still lagging for write. Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love, and forgive more.To cal= l him a dog hardly seems to do him justice though in as much as he had four= legs, a tail, and barked, I admit he was, to all outward appearances. But = to those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success =20 On Friday, July 23, 2021, 03:44:07 PM EDT, Vijaykumar Jain wrote: =20 =20 yeah, ignore what I asked. Glad others jumped in before it was deviating.a= s others said, it would be the replica is lagging or is not reachable/down = in synchronous replication.select * from pg_stat_replication/pg_replication= _slots would help show the same. PostgreSQL: Documentation: 13: 26.2.=C2=A0Log-Shipping Standby Servers On Sat, 24 Jul 2021 at 00:33, Vijaykumar Jain wrote: On Sat, 24 Jul 2021 at 00:29, Pepe TD Vo wrote: I'm running rh-postgresql12=C2=A0psql version 12.5OS RHEL 7I can run select= query but lagging to create role, table, both in interactive and non interactive mode, it is hanging? all write base= d queries but not read?if only writes hang, but not reads, do you see any i= ssues/ slowdown with writes on your storage where pg_wal resides?is it netw= ork storage or local? =C2=A0 --=20 Thanks,VijayMumbai, India =20 ------=_Part_639869_164095365.1627071961893 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
thank you for information url
both primary_conninfo on master an= d slave are not set
max_wal_sen= ders =3D 10
max_replication_slo= ts =3D 1
's 
when I run 'select * from pg_stat_replicatio= n/pg_replication_slots;'
0 rows=

archive_command=3D'/opt/rh/rh-postgresql12/root/bin/sync= wal.sh %p %f'

syncwal.sh contains:
#!/bin/bash
scp $1 slave_ip:/var/opt/rh/rh-postgres12/lib/pgsql/walarchive/$2
<= div dir=3D"ltr" data-setdir=3D"false">if [ $? !=3D 0 ]
then 
echo "Archiver error:"
exit= 1
fi
exit 0

do I need to set the restore_command and archive_cleanup_command= in slave's postgresql.conf only?

another issue is when I= restart postgresql.service in master, it takes a little longer as usual bu= t in slave, prompt out as soon as I hit enter to restart the postgresql.ser= vice

after modify the postgresql.conf with archive_cleanu= p_command and restore_command on slave, master is still lagging for write.<= /div>


Bach-Nga

No one in this world is pure and perfect.  If = you avoid people for their mistakes you will be alone. So judge less, love,= and forgive more.3D"Emoji"3D"Emoji"3D"Emoji"
To call him a dog hardly seems to do him justice th= ough in as much as he had four legs, a tail, and barked, I admit he was, to= all outward appearances. But to those who knew him well, he was a perfect = gentleman (Hermione Gingold)

<= span style=3D"color:rgb(128, 0, 255);" id=3D"ydpf8dd9bc5yui_3_13_0_rc_1_1_1= 381789421038_7867">**Live simply **Love generously **Care deeply **Speak ki= ndly.
*** Genuinely rich *** Faithful talen= t *** Sharing success




=20
=20
On Friday, July 23, 2021, 03:44:07 PM EDT, Vijaykumar J= ain <vijaykumarjain.github@gmail.com> wrote:


yeah, ignore what I asked. Glad others jumped in before it was d= eviating.
as others said, it would be the replica is lagging = or is not reachable/down in synchronous replication.
= select * from pg_stat_replication/pg_replication_slots would help show the = same.



On Sat, 24 Jul 2021 at 00:33, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


On Sat, 24 Jul 2021 at 00:29, Pepe TD Vo <pepevo@yahoo.com> wr= ote:
I'm running rh-postgresql12 
psql version 12.5
OS RHEL 7
I = can run select query but lagging to create role, table,

both in interactive and non interac= tive mode, it is hanging? all write based queries but not read?
i= f only writes hang, but not reads, do you see any issues/ slowdown with wri= tes on your storage where pg_wal resides?
is it network storage o= r local?


<= div>

 


--=
Thanks,
Vijay
Mumbai, India
------=_Part_639869_164095365.1627071961893--