Thread: Re: Way to access LSN (for each transaction) by directly talking to postgres?

On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuabay93@gmail.com> wrote:
> Could you please let me know if there is a way to get LSN of each
> transaction by directly communicating with Postgres server and NOT by
> accessing logs.

Logical decoding is one way.
-- 
Michael

On Wed, Aug 3, 2016 at 3:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuabay93@gmail.com> wrote:
>> Could you please let me know if there is a way to get LSN of each
>> transaction by directly communicating with Postgres server and NOT by
>> accessing logs.
>
> Logical decoding is one way.

And I just saw your other message... What I just meant here is that if
you use a decoder plugin that just emits information at transaction
begin/commit you can directly get this information. There is no need
to directly look at the WAL logs, the server does it for you. And it
offers a good cover regarding the information that has already been
consumed or not.

(Btw, avoid sending emails across multiple mailing lists, particularly
pgsql-committers which is not aimed for that).
-- 
Michael

On Thu, Aug 4, 2016 at 3:02 AM, Joshua Bay <joshuabay93@gmail.com> wrote:
> Could you please tell me how I can get LSN of each transaction at decoder
> plugin?

Craig already gave you hints, but here are more. You will need to hack
your own plugin. You could just use the one in contrib/test_decoding,
remove most of its code, and use the commit callback to issue the LSN
you are interested in. Note as well that when using
pg_logical_slot_peek_changes or pg_logical_slot_get_changes, you can
get a LSN location. Using test_decoding as a base, that's not a
complicated effort.
-- 
Michael

<div dir="ltr">Sorry I forgot to reply. Thanks! using decoding plugins works great</div><div class="gmail_extra"><br
/><divclass="gmail_quote">On Wed, Aug 3, 2016 at 8:37 PM, Michael Paquier <span dir="ltr"><<a
href="mailto:michael.paquier@gmail.com"target="_blank">michael.paquier@gmail.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On Thu, Aug 4,
2016at 3:02 AM, Joshua Bay <<a href="mailto:joshuabay93@gmail.com">joshuabay93@gmail.com</a>> wrote:<br /> >
Couldyou please tell me how I can get LSN of each transaction at decoder<br /> > plugin?<br /><br /></span>Craig
alreadygave you hints, but here are more. You will need to hack<br /> your own plugin. You could just use the one in
contrib/test_decoding,<br/> remove most of its code, and use the commit callback to issue the LSN<br /> you are
interestedin. Note as well that when using<br /> pg_logical_slot_peek_changes or pg_logical_slot_get_changes, you
can<br/> get a LSN location. Using test_decoding as a base, that's not a<br /> complicated effort.<br /><span
class="HOEnZb"><fontcolor="#888888">--<br /> Michael<br /></font></span></blockquote></div><br /></div>