Обсуждение: Re: Heavy Logging in Subscriber side when configured LogicalReplication in 10.4

Hi Team,

We have successfully configured and tested Logical Replication between
Publisher and Subscriber. But on the Subscriber side we are getting
generated a number of error log files(around 20-30 GB). 

The most common log messages found in the log(/var/log/postgresql/abc.log)
are:
2018-08-26 06:32:09.659 IST [9385] ERROR:  duplicate key value violates
unique constraint "table_pkey"
2018-08-26 06:32:09.637 IST [13919] LOG:  worker process: logical
replication worker for subscription 4858103 sync 189974 (PID 9384) exited
with exit code 1
2018-08-26 06:32:09.638 IST [13919] LOG:  worker process: logical
replication worker for subscription 4858103 sync 188985 (PID 9383) exited
with exit code 1


Some 72 lakh times the above errors are occurring. How to prevent this
behaviour? Kindly assist me!!

Thanks in Advance.

Regards,
Pavan






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

Community,

Looking forward to hear your advices.




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

Hi Soumitra,

Thanks for your timely response.

>>This may be due to several reason , so you have to remove that conflict
first . 

Could you suggest me a way to do it. I am unable to figure out why this data
integrity came into picture(like why it is failing). In order to reduce this
what are measures to cope with??

Thanks in Advance.

Regards,
Pavan



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

Hi Soumitra,

Actually speaking there is no duplicacy of the records. This error is
occurring on the subscriber side of the logical replication.

And the data on publisher is same as the data in subscriber. So there is no
point of mismatch in data integrity. 

And I think this error is caused because logical replication worker is
trying the sync the already synced rows from publisher to subscriber. In a
day it is occurring approximately 5264718 times.

This is causing heavy logging at the subscriber. I think you got my problem.

Environment:

The environment is two ubuntu boxes configured with Logical Replication on
Postgres 10.4. And the error flooding is occurring on the subscriber side.
Thanks in Advance.

Regards,
Pavan



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

P {margin-top:0;margin-bottom:0;}

Hi Pavan , 

From Log it is evident that data integrity is failing both at Publisher and subscriber end .

This may be due to several reason , so you have to remove that conflict first . 

2018-08-26 06:32:09.659 IST [9385] ERROR:  duplicate key value violates 
unique constraint "table_pkey" 

Thanks ,

Soumitra 

---

From: pavan95 <pavan.postgresdba@gmail.com>
Sent: Monday, September 3, 2018 10:58 AM
To: pgsql-admin@postgresql.org
Subject: Re: Heavy Logging in Subscriber side when configured Logical Replication in 10.4

 

Community,

Looking forward to hear your advices.




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

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

On 01/09/2018 11:56, pavan95 wrote:
> Hi Team,
>
> We have successfully configured and tested Logical Replication between
> Publisher and Subscriber. But on the Subscriber side we are getting
> generated a number of error log files(around 20-30 GB).
>
> The most common log messages found in the log(/var/log/postgresql/abc.log)
> are:
> 2018-08-26 06:32:09.659 IST [9385] ERROR:  duplicate key value violates
> unique constraint "table_pkey"
> 2018-08-26 06:32:09.637 IST [13919] LOG:  worker process: logical
> replication worker for subscription 4858103 sync 189974 (PID 9384) exited
> with exit code 1
> 2018-08-26 06:32:09.638 IST [13919] LOG:  worker process: logical
> replication worker for subscription 4858103 sync 188985 (PID 9383) exited
> with exit code 1

This is severe and will eventually break your Publisher side too (the replication slot will grow forever).
So, you did something wrong, I hope - don't get me wrong I'd wish the same for myself :)
Either you created the subscription while the subscriber already had existing data in the tables, or you are
inserting/updatingdataI or Ds/keys on the subscriber that cause the problem .
 

>
>
> Some 72 lakh times the above errors are occurring. How to prevent this
> behaviour? Kindly assist me!!
>
> Thanks in Advance.
>
> Regards,
> Pavan
>
>
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

Hi Achilleas,

But the logical Replication is working fine. I mean to say that the data is
replicating between Publisher and Subscriber. I have problem only with my
logs. Most common errors found on my Subscriber are:

1. duplicate key value violates unique constraint
2. worker process: logical replication worker for subscription 6992680 sync
694478 (PID 13459) exited with exit code 1
3. logical replication table synchronization worker for subscription
""subscription_name"", table ""tbl_A"" has started

>>Either you created the subscription while the subscriber already had
existing data in the tables, or you are inserting/updating dataI or Ds/keys
on the subscriber that cause the problem . 

Even if I did that it will face this error the very first time it is trying
to establish sync between publisher and subscriber but not every
time(correct me if I am wrong)

The 2'nd &  3'rd are not actually ERRORS. They are LOG messages. Please
suggest me if I went wrong somewhere. 



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

On 03/09/2018 10:50, pavan95 wrote:
> Hi Achilleas,
>
> But the logical Replication is working fine. I mean to say that the data is
> replicating between Publisher and Subscriber. I have problem only with my
> logs. Most common errors found on my Subscriber are:
>
> 1. duplicate key value violates unique constraint
> 2. worker process: logical replication worker for subscription 6992680 sync
> 694478 (PID 13459) exited with exit code 1
> 3. logical replication table synchronization worker for subscription
> ""subscription_name"", table ""tbl_A"" has started
>
>>> Either you created the subscription while the subscriber already had
> existing data in the tables, or you are inserting/updating dataI or Ds/keys
> on the subscriber that cause the problem .
>
> Even if I did that it will face this error the very first time it is trying
> to establish sync between publisher and subscriber but not every
> time(correct me if I am wrong)
it will go like this forever.
So you must try to find the offending rows on both sides and explain how did it happen.
I repeat, this is serious, and not supposed to happen.
The system expects from you to resolve the problem manually and advance the position as needed to overcome the error.
Until you do this, you'll keep getting this ERROR.
Read the docs.
>
> The 2'nd &  3'rd are not actually ERRORS. They are LOG messages. Please
> suggest me if I went wrong somewhere.
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

>>The system expects from you to resolve the problem manually and advance the
position as needed to overcome the error. 

Could you please suggest the apt measures to be followed to do the same.
Thanks in Advance.


Regards,
Pavan



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

On 03/09/2018 11:26, pavan95 wrote:
>>> The system expects from you to resolve the problem manually and advance the
> position as needed to overcome the error.
>
> Could you please suggest the apt measures to be followed to do the same.
> Thanks in Advance.
https://www.postgresql.org/docs/10/static/logical-replication-conflicts.html

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
thereand 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.
If you don't want to do that because you are happier with the subscriber's version of this id, then you should advance
theposition.
 
But for starters, you better solve this by handling the row manually .

>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

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

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.

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.

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

Hi Achilleas/David,

So like what you have said, making the subscriber table structure(only)
ready will sort out this issue?

If yes, I will drop the existing replication and start from the scratch.  Or
else like what David had suggested is truncating all the tables sufficient??
Please confirm.

Looking forward to hear from you!!.

Finding my way interesting in this logical replication. And thanks team for
your wonderful support.

Regards,
Pavan



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

On 04/09/2018 10:12, pavan95 wrote:
> Hi Achilleas/David,
>
> So like what you have said, making the subscriber table structure(only)
> ready will sort out this issue?
>
> If yes, I will drop the existing replication and start from the scratch.  Or
> else like what David had suggested is truncating all the tables sufficient??
> Please confirm.
Start from scratch:
- drop the subscription
- truncate all tables on the subscriber side (or drop the database and recreate with --schema-only)
- recreate the subscription

In the mid of the summer we went through deploying logical replication for the first time, and it took a considerable
amountof testing, and planning, e.g. which tables you want, which you dont want, 
 
from the ones you want do they have unique keys, etc, and still I didn't get it right from the first go.
>
> Looking forward to hear from you!!.
>
> Finding my way interesting in this logical replication. And thanks team for
> your wonderful support.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

Dear Achilleas,

>Start from scratch: 
>- drop the subscription 
>- truncate all tables on the subscriber side (or drop the database and
recreate with --schema-only) 
>- recreate the subscription 

Done with this. Will check for any duplicate key violation errors. But have
one small doubt. Think we have a table named "country" where in which 1000
records are present and it is replicated till date(all the 1000 rows are
same on both publisher and subscriber).

Now when an application user tries to insert another 50 records will the
replication for "country" table now starts for the differential part, that
is 50 records or it starts from the scratch?? How is this behaving at this
juncture? Eagerly waiting for your response.

Regards,
Pavan



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

On 04/09/2018 13:23, pavan95 wrote:
> Dear Achilleas,
>
>> Start from scratch:
>> - drop the subscription
>> - truncate all tables on the subscriber side (or drop the database and
> recreate with --schema-only)
>> - recreate the subscription
> Done with this. Will check for any duplicate key violation errors. But have
> one small doubt. Think we have a table named "country" where in which 1000
> records are present and it is replicated till date(all the 1000 rows are
> same on both publisher and subscriber).
>
> Now when an application user tries to insert another 50 records will the
> replication for "country" table now starts for the differential part, that
> is 50 records or it starts from the scratch?? How is this behaving at this
> juncture? Eagerly waiting for your response.
 From the deferential part. It will replicate from the last successful position onward.
It is all in the docs.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

Now when an application user tries to insert another 50 records will the
replication for "country" table now starts for the differential part, that
is 50 records or it starts from the scratch?? 

Hello community,

Thank you so much(Achilleas, David) for your timely responses. 

After all the suggestions and inputs I was finally able to cope up with the
reducing the duplicacy errors.


Regards,
Pavan



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