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
