Обсуждение: logical Replication

Hi Anjul,

Did your query got resolved? If yes, could you say me how you got it sorted
out?  I wanted to know if I can monitor Logical Replication in PG10.4.

And want to have a clear picture of Replication Lag.  Looking forward to
hear from you!!


Regards,
Pavan



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

Hi Anjul,

I am on Postgres 10.4. But I'm still facing Replication Lag(don't know why).
I tried refreshing the publication  with data from the subscription side.
But still the data didn't got replicated.

But I tried to drop and recreate the subscription and then the data got
replicated within a fly. I really don't know what is the cause of this lag?

Am I running behind core concepts in Logical Replication?

Looking forward to hear from you!!


Regards,
Pavan



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

On 20/08/2018 09:24, pavan95 wrote:
> Hi Anjul,
>
> I am on Postgres 10.4. But I'm still facing Replication Lag(don't know why).
> I tried refreshing the publication  with data from the subscription side.
> But still the data didn't got replicated.
>
> But I tried to drop and recreate the subscription and then the data got
> replicated within a fly. I really don't know what is the cause of this lag?
How do you monitor ? Do you check for ERROR in the logs?
>
> Am I running behind core concepts in Logical Replication?
>
> Looking forward to hear from you!!
>
>
> 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 still the data didn't got replicated. 
>> 
>> But I tried to drop and recreate the subscription and then the data got 
>> replicated within a fly. I really don't know what is the cause of this
>> lag? 

  >How do you monitor ? Do you check for ERROR in the logs?

I do monitor the ERRORS from the log file itself. 

But When I insert data in the publisher, And perform a check on the
subscriber, it is not getting replicated(most of the times) until I drop and
recreate my SUBSCRIPTION.

But later that, it is replicating very fast. Hope I specified the answer for
your question. 

Thanks in Advance

Regards,
Pavan
 



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

On 20/08/2018 09:42, pavan95 wrote:
> Hi Achilleas,
>
>>> But still the data didn't got replicated.
>>>
>>> But I tried to drop and recreate the subscription and then the data got
>>> replicated within a fly. I really don't know what is the cause of this
>>> lag?
>    >How do you monitor ? Do you check for ERROR in the logs?
>
> I do monitor the ERRORS from the log file itself.
>
> But When I insert data in the publisher, And perform a check on the
> subscriber, it is not getting replicated(most of the times) until I drop and
> recreate my SUBSCRIPTION.

OS? (Linux, BSD, etc)
PostgreSQL Version?
Also give the current settings of wal_receiver_timeout , wal_sender_timeout , increasing those to '5 min' solved my
issues.



>
> But later that, it is replicating very fast. Hope I specified the answer for
> your question.
>
> 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

>> But When I insert data in the publisher, And perform a check on the 
>> subscriber, it is not getting replicated(most of the times) until I drop
>> and 
>> recreate my SUBSCRIPTION.

>OS? (Linux, BSD, etc) 
*Ubuntu 16.04.5 LTS*
>PostgreSQL Version? 
*PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit*
>Also give the current settings of wal_receiver_timeout , wal_sender_timeout
, increasing those to '5 min' solved my issues. 
*1) wal_receiver_timeout: 1min
2) wal_sender_timeout:   1min*

How increasing above params related to the replication lag? 

Looking forward hearing from you!!!


Regards,
Pavan



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

On 20/08/2018 10:04, pavan95 wrote:
>>> But When I insert data in the publisher, And perform a check on the
>>> subscriber, it is not getting replicated(most of the times) until I drop
>>> and
>>> recreate my SUBSCRIPTION.
>> OS? (Linux, BSD, etc)
> *Ubuntu 16.04.5 LTS*
>> PostgreSQL Version?
> *PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit*
>> Also give the current settings of wal_receiver_timeout , wal_sender_timeout
> , increasing those to '5 min' solved my issues.
> *1) wal_receiver_timeout: 1min
> 2) wal_sender_timeout:   1min*
>
> How increasing above params related to the replication lag?
It helps with the ERRORs you get (e.g.
worker process: logical replication worker for subscription 33650 sync 20258 (PID 32898) exited with exit code 1
or
worker process: logical replication worker for subscription 185231525 (PID 78166) exited with exit code 1

Now on the publisher side what does :
select * from pg_stat_replication ;
tell you?

>
> Looking forward hearing from you!!!
>
>
> 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,

>Now on the publisher side what does : 
>select * from pg_stat_replication ; 
>tell you? 
Please find the below output:
>*select * from pg_stat_replication ;*
   pid  | usesysid | usename  | application_name | client_addr  |
client_hostname | client_port |          backend_start           |
backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn
| write_lag | flush_lag | replay_lag | sync_priority | sync_state

-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |                
|       411111 | 2018-08-20 11:32:09.636622+05:30 |              | streaming
| 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |          
|            |             0 | async






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

On 20/08/2018 10:40, pavan95 wrote:
> Hi Achilleas,
>
>> Now on the publisher side what does :
>> select * from pg_stat_replication ;
>> tell you?
> Please find the below output:
>> *select * from pg_stat_replication ;*
>     pid  | usesysid | usename  | application_name | client_addr  |
> client_hostname | client_port |          backend_start           |
> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn
> | write_lag | flush_lag | replay_lag | sync_priority | sync_state
>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
>   32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |
> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              | streaming
> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |
> |            |             0 | async
>
So here you have zero lag. How do you experience the lag? What do you exactly measure?
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

> Hi Achilleas, 
> 
>> Now on the publisher side what does : 
>> select * from pg_stat_replication ; 
>> tell you? 
> Please find the below output: 
>> *select * from pg_stat_replication ;* 
>     pid  | usesysid | usename  | application_name | client_addr  | 
> client_hostname | client_port |          backend_start           | 
> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  |
> replay_lsn 
> | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------

>   32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx | 
> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              |
> streaming 
> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           | 
> |            |             0 | async 
>
>>So here you have zero lag. How do you experience the lag? What do you
exactly measure? 

Actually there are no running transactions in the database. When I insert
the data suppose 100 records in a table and after commit connect to the
subscriber database and issue row count from that particular table, I am
finding that the data didn't got replicated.

Later which I will proceed with :
ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)

Even then I can't see the data replicated in the subscriber side.

Then I will go with dropping and recreating the SUBSCRIPTION on the
subscriber side where I will see that inserted 100 records in the subscriber
side. 

Again will try inserting another 1000 records which will get replicated
within microsecond(I guess). 

This is what I will consider as lag. Hope I answered your question.

Looking forward to hear from you.

Regards,
Pavan



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

On 20/08/2018 10:59, pavan95 wrote:
>> Hi Achilleas,
>>
>>> Now on the publisher side what does :
>>> select * from pg_stat_replication ;
>>> tell you?
>> Please find the below output:
>>> *select * from pg_stat_replication ;*
>>      pid  | usesysid | usename  | application_name | client_addr  |
>> client_hostname | client_port |          backend_start           |
>> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  |
>> replay_lsn
>> | write_lag | flush_lag | replay_lag | sync_priority | sync_state
>>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
>>    32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |
>> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              |
>> streaming
>> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |
>> |            |             0 | async
>>
>>> So here you have zero lag. How do you experience the lag? What do you
> exactly measure?
>
> Actually there are no running transactions in the database. When I insert
> the data suppose 100 records in a table and after commit connect to the
> subscriber database and issue row count from that particular table, I am
> finding that the data didn't got replicated.
>
> Later which I will proceed with :
> ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)
>
> Even then I can't see the data replicated in the subscriber side.
>
> Then I will go with dropping and recreating the SUBSCRIPTION on the
> subscriber side where I will see that inserted 100 records in the subscriber
> side.
>
> Again will try inserting another 1000 records which will get replicated
> within microsecond(I guess).
This is far from normal. This isn't working for some reason.
Now do that :
a) increase the timeouts
b) restart both servers
c) drop / recreate the subscription
d) wait till all data are synced
e) start with a simple insert (1 row), and monitor the data on the subscriber. Then insert 100 rows. Always watch the
LOGfor ERRORs and also pg_stat_replication (publisher), pg_stat_subscription 
 
(subscriber), pg_replication_slots (publisher), pg_replication_origin_status (subscriber)
>
> This is what I will consider as lag. Hope I answered your question.
>
> Looking forward to hear from you.
>
> 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,

Will monitor and get back to you on this. I have concern about one more
error for which I have raised a question on this on the following link:
http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html
<http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html>  

Do you have any idea on the below error:

2018-08-20 10:34:55.801 IST [24955] user@db ERROR:  could not create 
directory "pg_replslot/mysub_14211111_sync_111111.tmp": No space left on 
device 
2018-08-20 10:34:56.036 IST [34222] user@db ERROR:  could not create file 
"pg_replslot/mysub/state.tmp": No space left on device
2018-08-20 10:34:56.053 IST [23444] user@db WARNING:  could not create 
relation-cache initialization file "global/pg_internal.init.11331": No space 
left on device
2018-08-20 10:34:56.053 IST [23444] user@db DETAIL:  Continuing anyway, but 
there's something wrong.


Looking forward to hear from you regarding the lifecycle of snapshots in the
publisher side..

Thanks in advance!!

Regards,
Pavan



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

On 20/08/2018 11:58, pavan95 wrote:
> Hi Achilleas,
>
> Will monitor and get back to you on this. I have concern about one more
> error for which I have raised a question on this on the following link:
> http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html
> <http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html>
>
> Do you have any idea on the below error:
>
> 2018-08-20 10:34:55.801 IST [24955] user@db ERROR:  could not create
> directory "pg_replslot/mysub_14211111_sync_111111.tmp": No space left on
> device
> 2018-08-20 10:34:56.036 IST [34222] user@db ERROR:  could not create file
> "pg_replslot/mysub/state.tmp": No space left on device
> 2018-08-20 10:34:56.053 IST [23444] user@db WARNING:  could not create
> relation-cache initialization file "global/pg_internal.init.11331": No space
> left on device
> 2018-08-20 10:34:56.053 IST [23444] user@db DETAIL:  Continuing anyway, but
> there's something wrong.
Man, your system's state is one step from disaster.

Increase the space ASAP.

>
> Looking forward to hear from you regarding the lifecycle of snapshots in the
> publisher side..
>
> 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,

In order to be more specific, today morning I faced this issue. At that
particular point of time, pg_logical directory is containing snaps worth
23GB. 

Suddenly after sometime exactly 5 mins after my check all the snaps got
deleted and the logical replication continued to function as earlier..


Can we infer something(logical kind of concept) from this??


Regards,
Pavan



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

On 20/08/2018 13:07, pavan95 wrote:
> Hi Achilleas,
>
> In order to be more specific, today morning I faced this issue. At that
> particular point of time, pg_logical directory is containing snaps worth
> 23GB.
>
> Suddenly after sometime exactly 5 mins after my check all the snaps got
> deleted and the logical replication continued to function as earlier..
>

If you are marginal on space available, the slightest delay can cause the replication slot to run out of space.
So increase your space.

> Can we infer something(logical kind of concept) from this??
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

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

>If you are marginal on space available, the slightest delay can cause the
replication slot to run out of space. 
>So increase your space. 

How can I judge myself that marginal space is left available. But the fact
is I do still have 50 GB of additional disk space available(on the mount
point where the postgres 10.4 data directory runs) when I issued "df -kh" .

So I need to have a valid justification in order to ask the systems team for
space getting added. But still I'm unable to find any reason from our
conversation. 

But the point I wonder is the snaps are getting automatically deleted and
the error stops producing for a period of time. Looking forward to touch the
pain area(if at all the server feels).

Looking forward to hear from you. Thanks in Advance.

Regards,
Pavan





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

On 20/08/2018 13:37, pavan95 wrote:
>> If you are marginal on space available, the slightest delay can cause the
> replication slot to run out of space.
>> So increase your space.
> How can I judge myself that marginal space is left available. But the fact
> is I do still have 50 GB of additional disk space available(on the mount
> point where the postgres 10.4 data directory runs) when I issued "df -kh" .
>
> So I need to have a valid justification in order to ask the systems team for
> space getting added. But still I'm unable to find any reason from our
> conversation.
"No space left on device" is a big reason. This is alarming. Your sysadms should have had monitoring in place and
shouldhave addressed and resolved this already.
 
>
> But the point I wonder is the snaps are getting automatically deleted and
> the error stops producing for a period of time. Looking forward to touch the
> pain area(if at all the server feels).
>
> Looking forward to hear from you. 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

Actually there are no running transactions in the database. When I insert
the data suppose 100 records in a table and after commit connect to the
subscriber database and issue row count from that particular table, I am
finding that the data didn't got replicated.

Later which I will proceed with :
ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)

Even then I can't see the data replicated in the subscriber side.

Then I will go with dropping and recreating the SUBSCRIPTION on the
subscriber side where I will see that inserted 100 records in the subscriber
side.

Again will try inserting another 1000 records which will get replicated
within microsecond(I guess).