Re: PostgreSQL logical replication slot LSN values

Hi,

> 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.
     Yes, I am consuming data using the PGReplicationStream.readPending() method in my program.


> Hm, that should not happen. Did you by any chance externally (manually
> or by script) delete WAL files?
        No, I am not deleting the WAL files externally.


> 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.
      I am sending feedback messages using method PGReplicationStream.setAppliedLSN(<LSN>). I cannot do
PGReplicationStream.setFlushedLSN(<LSN>)as I want the option to go back and read data in case of data loss. I have a
separateutility that can be used to set the flush_lsn position periodically. 


Currently since the WAL file was deleted by postgresql, I am not able to move forward at all - replication thru the SQL
orJava API is not happening. I tried doing a PGReplicationStream.setAppliedLSN and PGReplicationStream.setFlushedLSN
(tocurrent LSN) thru another Java program for the same replication slot, but that didn't work. It still gives the WAL
segmentalready removed error. 

Could you please suggest a solution for this? Is there a way to set the restart_lsn and flush_lsn of slot? Or is
recreatingthe slot the only possible solution? 


Thanks,
Rashmi



-----Andres Freund <andres@anarazel.de> wrote: -----
To: Rashmi V Bharadwaj <rvbharad@in.ibm.com>
From: Andres Freund <andres@anarazel.de>
Date: 12/03/2019 09:07PM
Cc: pgsql-general@postgresql.org
Subject: 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