From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: logical Replication
Date: Mon, 20 Aug 2018 11:27:33 +0300
Message-ID: <af7bd442-70e4-c55e-e069-0fbf14e3a88c@matrix.gatewaynet.com> (raw)
In-Reply-To: <1534751989163-0.post@n3.nabble.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: logical Replication
In-Reply-To: <af7bd442-70e4-c55e-e069-0fbf14e3a88c@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox