Re: logical Replication - Mailing list pgsql-admin

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