Re: logical Replication

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 LOG for 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