Does anyone know how to look up the transaction that’s associated with an LSN on a CloudSQL Postgres instance?

For example, let’s say I’m trying to find the transaction associated with LSN 842/E62E3F01. On a normal server, I can ssh into the instance, and run pg_waldump --start=842/E62E3F01 (or pg_xlogdump on Postgres < 10). But it doesn’t seem possible to ssh into a CloudSQL server. If so, how can I find the query/transaction associated with this LSN on CloudSQL?

I'm open to using Query Insights as well. Specifically, I'm having an issue where I'm trying to monitor transactions larger than 1GB. If there's a way to do this in Query Insights, that would be helpful. I can get the LSN's of these transactions, but not sure how to map them back to the actual transactions themselves.

  • How would you map them back using pg_waldump? Query texts aren't present there.
    – jjanes
    Sep 27, 2021 at 16:14
  • I'm not entirely sure yet, but since pg_waldump returns a transaction id, I figured I can use that to extract more information about the query texts. I'm also exploring using pg_logical_slot_peek_changes to get information about the transaction associated with an LSN - I'll update this post if I found out anything useful...
    – modulitos
    Sep 27, 2021 at 16:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.