Subject: Re: Hick ups in Postgresql Logical Replication
To: pgsql-admin@lists.postgresql.org
References: <1539447741900-0.post@n3.nabble.com>
 <20427d4e-98a5-0cda-bddc-070989015cca@catalyst.net.nz>
 <1539598263562-0.post@n3.nabble.com>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <81cfab86-c6dc-89a1-5685-7589e4f81f9d@catalyst.net.nz>
Date: Tue, 16 Oct 2018 14:35:08 +1300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <1539598263562-0.post@n3.nabble.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Hi


On 15/10/18 23:11, pavan95 wrote:
> Hi Mark,
>
> Thanks for your response.
>
>> We'd probably need to see how you actually did your set up, and also how
>> you are deducing that there is a lag (e.g pg_stat_subscription)?
>    
> *Setup*:
>
> *Publisher *:
>
> Out of n number of tables n-1 tables are opted for replication of DML
> changes.
>
> *Subscriber *:
>
> Subscription is created on the subscriber for the corresponding publication.
>
> *At times, I find(developer complains) about difference in records between
> some of the published tables.*
>
> For example, after successful configuration of replication, say it was
> configured successfully on Oct 1'st 2018 and verified that it is
> replicating. Then some n days in future, a developer complains a table named
> abc is not getting replicated(meaning the data inserted/updated/deleted for
> that table abc on publisher is not getting synced with subscriber).
>
> *Findings*:
>
> Here I found that for 1 of the published table the row count is not same on
> both publisher and subscriber:
>
> Publisher :  select count(*) from table_A;       No.of Rows= 3598
>
> Subscriber: select count(*) from table_A:       No.of Rows= 3590
>

I suggest trying to make a self contained test case illustrating this 
problem (maybe just a single table replicated). By that I mean an exact 
sequence of commands (so we can try it too and see if you have found a bug)!

With respect to the question about wal files and LSN, see the function 
pg_walfile_name(), e.g:

postgres=# SELECT pg_current_wal_lsn();
  pg_current_wal_lsn
--------------------
  0/51EB3BA8
(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
      pg_walfile_name
--------------------------
  000000010000000000000051
(1 row)


The other 2 questions will be more easily tackled when we know the exact 
set of commands you are using to do set things up.


regards
Mark