Re: PostgreSQL logical replication slot LSN values

Hi,

(please don't send HTML only emails to this list)

On 2019-03-12 11:08:56 +0000, Rashmi V Bharadwaj wrote:
> We have an application that uses the PostgreSQL logical replication API to read
> the changes made to the PostgreSQL database and applies it to a different
> database (like Db2 etc). We are using logical replication slots for
> this.

Cool.

> Currently I am facing an issue where the replication slot is pointing to an
> older restart_lsn and confirmed_flush_lsn (like 10 days back) and the
> corresponding WAL file is already deleted from the pg_wal directory. Ideally
> this should not happen, right?

Well, did you consume the logical data, and if so how? When you use the
streaming interface - HIGHLY recommended - you need to send feedback
messages as to where you've received the data.

> since the slot is holding this LSN the wal file
> should not have been deleted. Now when I try to use query like
> select * from pg_logical_slot_get_changes(<slot_name>,<LSN>, NULL)
>

> or use the logical replication API with a start position as any newer LSN, I
> get the following error:
>
> ERROR: requested WAL segment pg_wal/000000010000000000000036 has already been
> removed
> SQL state: 58P01.

Hm, that should not happen. Did you by any chance externally (manually
or by script) delete WAL files?

> How do I get past this issue? I have not enabled log archiving. I would also
> like to know how I can modify the restart_lsn and confirmed_flush_lsn positions
> of the slot?

You need to send feedback messages confirming up to wher eyou've
consumed the data when using the streaming protocol. When using the SQL
functions the _get_ function confirms when it returns, the _peek_
function never does so. It's recommended to limit the size of the
resultset a bit using the nchanges paramter so you can call it in
smaller increments.

Greetings,

Andres Freund