How to restore particular missing wal files

[Rajasekar Arumugam]

Hi Team,

My slave went out of sync due to missing wal files in master. I have streaming replication and barman has all the wal files. I need to restore only the missing wal files from barman server to db-server. do we have any command for that. 

Thanks Raj

[Luca Ferrari]

On Thu, May 2, 2024 at 5:25 PM 'Rajasekar Arumugam' via Barman, Backup
and Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
> My slave went out of sync due to missing wal files in master. I have streaming replication and barman has all the wal files. I need to restore only the missing wal files from barman server to db-server. do we have any command for that.

I don't think there is such an option.
The only way for a standby to get out of sync is by not using
replication slots, which in my opinion are mostly recommended, but you
probably have good reasons to not use them. Therefore I would either
perform a recover (from barman) or make the standby from scratch
(after all you want the standby to be aligned, right?).

Luca

[Rajasekar Arumugam]

yes, I see some option in barman to setup on standby recovery.conf to fix this.  I haven't tried it as no connectivity.

restore_command = 'ssh barman@pgbackup barman get-wal SERVER %f > %p'

[Luca Ferrari]

On Thu, May 2, 2024 at 6:47 PM 'Rajasekar Arumugam' via Barman, Backup
and Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
>
> yes, I see some option in barman to setup on standby recovery.conf to fix this. I haven't tried it as no connectivity.
>
> restore_command = 'ssh barman@pgbackup barman get-wal SERVER %f > %p'
>

Uhm... restore_command should be what PostgreSQL wil use to get _new_
WALs once it has finished processing the current one in recovery mode.
Therefore, restore_command cannot be used out of the box to reocever a
few WAL files, it can be used as an inspiration to force the standby
to get the missing wals, or better, to make WALs copy local to the
machine that need them. I would not recommend this approach, however.

Luca