Date: Mon, 3 Sep 2018 22:58:35 -0700 (MST)
From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1536040715349-0.post@n3.nabble.com>
In-Reply-To: <6bb2a6ba-0425-c81c-baa9-8e56b56072df@matrix.gatewaynet.com>
References: <1535792170623-0.post@n3.nabble.com>
 <5b79452e-cec6-c93c-76de-968be43be45e@matrix.gatewaynet.com>
 <1535961028810-0.post@n3.nabble.com>
 <ba731059-dfd7-2d34-b3a5-83a20b564a1d@matrix.gatewaynet.com>
 <1535963171985-0.post@n3.nabble.com>
 <6bb2a6ba-0425-c81c-baa9-8e56b56072df@matrix.gatewaynet.com>
Subject: Re: Heavy Logging in Subscriber side when configured Logical
 Replication in 10.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi Achilleas,

>>>Either you created the subscription while the subscriber already had
existing data in the tables

I had taken a backup(along with data) and restored on both publisher and
subscriber. Later which I have created Publication and Subscription on both
the nodes.

>>so as a first step, try to find the offending row(s) in the subscriber and
then try to figure out how it was inserted there and by whom. Then delete
the row or do as needed to overcome the conflict. 
>>There should be a detail entry in the LOG file like : 
>>[local] [70002] 5b8cf8e3.11172 2018-09-03 12:03:56.342 EEST psql
postgres@dynacom line:4 DETAIL:  >>Key (id)=(18) already exists. 
>>That'll tell you the exact id. 
Yes the error is exactly same as what you mentioned. And also I have
checked/compared that particular id on both publisher and subscriber and the
result is same data on both nodes.

>>If you don't want to do that because you are happier with the subscriber's
version of this id, then you should advance the position. 
In this case how to do it? Is it done by using
"pg_replication_origin_advance('' , '') "? I have to use it for the first
time. How does it work ??
>>But for starters, you better solve this by handling the row manually . 
There are around 40-70 lakh conflicts of the same type. How to cope with
it??


Looking forward for your response.

Regards,
Pavan


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html