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
