5

I am trying to implement a regular incremental changes dump for our PostgreSQL 9.5 database.

I have opted for WAL, the wal2json plugin and pg_recvlogical. All works somehow, except for one thing:

When all data are read, pg_recvlogical simply goes silent. There is no way to tell it to quit after some time without changes, right? So I terminate it with kill -2 (SIGINT), which gives me

pg_recvlogical: unexpected termination of replication stream:

When I want next batch of changes, the replication slot starts at the same position as before, i.e. the entries are sent again.

How can I tell PostgreSQL that for some slot, I want set the replication position to the current position of the client?

There are 2 functions that seem to do that,

  • SELECT * FROM pg_replication_origin_advance(node_name text, pos pg_lsn)
  • SELECT * FROM pg_replication_origin_progress(node_name text, flush bool)

However, I am not sure what to pass as parameters. The manual doesn't tell. What's node_name? And for the position, all that wal2json gives me is xid.

Update: I can use pg_replication_origin_advance("sas2json", "28/160E2250"), see my answer. However, when I try it:

ERROR: only superusers can query or manipulate replication origins

And this is in Amazon RDS. And I can't have SUPERUSER - that's an AWS policy.

Any other way to set the LSN position?

Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277

1 Answers1

1

I figured out that I can enable -o include-lsn=true for wal2json. With that, it starts giving me 

{"xid":48311,"nextlsn":"28/160E2250", ...

So I can use nextlsn to call 

SELECT * FROM pg_replication_origin_advance("sas2json", "28/160E2250")

where the first parameter is the replication slot.

While this anwsers my original question, I need a solution that doesn't need SUPERUSER - if there's any.

Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277
  • Hey Ondra, did you encounter any problems while advancing the LSN by yourself? I'm mainly asking about situations where there are long transactions still in progress while you're moving the pointer (although I'm not entirely sure if this is a likely scenario). – martez Jul 10 '23 at 20:08
  • @martez, sorry - I don't remember after 5 years :) I'm now on CockroachDB which works differently. – Ondra Žižka Jul 13 '23 at 20:36
  • hehe, no worries. thanks for coming back to this comment! – martez Jul 14 '23 at 21:10