CODEWITHSUNDEEP
postgresqldatabase-replicationpostgresql-9.5wal
Ondra Žižka
Ondra Žižka

Reputation: 46904

PostgreSQL replication - how to update LSN after reading with pg_recvlogical and wal2json? (without superuser)

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,

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?

Upvotes: 5

Views: 2854

Answers (1)

Ondra Žižka
Ondra Žižka

Reputation: 46904

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.

Upvotes: 1

Related Questions