Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCIW9-00034X-DY for pgsql-admin@arkaria.postgresql.org; Tue, 16 Oct 2018 06:07:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gCIW7-0000bb-EZ for pgsql-admin@arkaria.postgresql.org; Tue, 16 Oct 2018 06:07:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCIW7-0000bT-7G for pgsql-admin@lists.postgresql.org; Tue, 16 Oct 2018 06:07:43 +0000 Received: from cat-porwal-prod-mail1.catalyst.net.nz ([2404:130:4080::4]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCIW2-0007y6-Ip for pgsql-admin@lists.postgresql.org; Tue, 16 Oct 2018 06:07:41 +0000 Received: from [IPv6:2406:e001:b7c:1:9193:85d9:8b71:5c47] (unknown [IPv6:2406:e001:b7c:1:9193:85d9:8b71:5c47]) (Authenticated sender: mark.kirkwood@catalyst.net.nz) by cat-porwal-prod-mail1.catalyst.net.nz (Postfix) with ESMTPSA id 7464480C8C for ; Tue, 16 Oct 2018 19:07:30 +1300 (NZDT) 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> <81cfab86-c6dc-89a1-5685-7589e4f81f9d@catalyst.net.nz> <1539667567051-0.post@n3.nabble.com> From: Mark Kirkwood Message-ID: <0eccff47-a5d8-56d3-8474-610378ad96d0@catalyst.net.nz> Date: Tue, 16 Oct 2018 19:07:29 +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: <1539667567051-0.post@n3.nabble.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US Authentication-Results: ORIGINATING; auth=pass smtp.auth=mark.kirkwood@catalyst.net.nz smtp.mailfrom=mark.kirkwood@catalyst.net.nz List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi again, On 16/10/18 18:26, pavan95 wrote: > > Hi Mark, > > My question was like how to get all the LSN's that are available in a WAL > segment. For example, take a segment named "000000010000000000000051". > > So in the above archive log segment, how to get all the LSN's of all txn's > which belong to that segment ? > > *The idea for asking this is, I will get the remote_lsn on the subscriber > which was successfully applied from the view "pg_replication_origin_status". > > Based on the LSN, I will check in which archive segment that particular LSN > is present(as you said above). > > And will find all the later LSN's(later to remote_lsn). > > So I can say that these many transactions are yet to be applied to the > target.* > > Well I think you are going to have to do a bit of work here to determine when the file name changes (e.g my system): bench=# SELECT pg_walfile_name('0/51EB3BAE');      pg_walfile_name --------------------------  000000010000000000000051 (1 row) bench=# SELECT pg_walfile_name('0/50000001');      pg_walfile_name --------------------------  000000010000000000000050 (1 row) However, I'm not sure you are chasing the right problem. It seems to me that you need to grapple with what is (possibly) wrong with the streaming wal protocol used by logical rep, not the wal file business. Again, I recommend you provide a precise test case that shows the problem, we can surely help you then! regards Mark