Subject: Re: logical Replication
To: pgsql-admin@lists.postgresql.org
References: <1534224580242-0.post@n3.nabble.com>
 <emeb045759-a361-420c-a956-011d96e0e4f4@iboss01081>
 <1534746282900-0.post@n3.nabble.com>
 <b4aa40be-d9af-de0e-c9dd-e4e42da0b0df@matrix.gatewaynet.com>
 <1534747341264-0.post@n3.nabble.com>
 <d6ed4da6-2cde-77f2-77bd-8e0d921fc61f@matrix.gatewaynet.com>
 <1534748661624-0.post@n3.nabble.com>
 <17b9b7a5-5377-b904-0e8a-e72e72efe1ef@matrix.gatewaynet.com>
 <1534750828102-0.post@n3.nabble.com>
 <11f06cb4-8e48-7090-76e0-438b54c70660@matrix.gatewaynet.com>
 <1534751989163-0.post@n3.nabble.com>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <af7bd442-70e4-c55e-e069-0fbf14e3a88c@matrix.gatewaynet.com>
Date: Mon, 20 Aug 2018 11:27:33 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: <1534751989163-0.post@n3.nabble.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

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