recovery_target discrepancy?

From: Thomas Rosenstein <thomas(at)thoro(dot)at>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: recovery_target discrepancy?
Date: 2025-12-13 12:18:59
Message-ID: VE1P194MB08790DE4292C588C1E426F7AA3AFA@VE1P194MB0879.EURP194.PROD.OUTLOOK.COM
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I'm trying to restore from an online backup and stop exactly after the backup is done and a consistent state is reached.

I'm running still Postgres 16, but documentation says it's still the same:

recovery_target = 'immediate'

This parameter specifies that recovery should end as soon as a consistent state is reached, i.e., as early as possible. When restoring from an online backup, this means the point where taking the backup ended.

Technically, this is a string parameter, but 'immediate' is currently the only allowed value.

When I specify this in the postgresql.auto.conf, the recovery stops at the start of the backup (consistent state) and then immediately fails because the BACKUP_END was not reached:

2025-12-13 11:48:19.964 UTC,,,9203,,693d5283.23f3,5,,2025-12-13 11:48:19 UTC,,0,LOG,00000,"recovered replication state of node 1 to 1FE5B/DB6541E0",,,,,,,,,"","startup",,0
2025-12-13 11:48:19.974 UTC,,,9203,,693d5283.23f3,6,,2025-12-13 11:48:19 UTC,1/0,0,LOG,00000,"consistent recovery state reached at 78/68000028",,,,,,,,,"","startup",,0
2025-12-13 11:48:19.974 UTC,,,9203,,693d5283.23f3,7,,2025-12-13 11:48:19 UTC,1/0,0,LOG,00000,"redo starts at 78/68000028",,,,,,,,,"","startup",,0
2025-12-13 11:48:19.974 UTC,,,9203,,693d5283.23f3,8,,2025-12-13 11:48:19 UTC,1/0,0,LOG,00000,"recovery stopping after reaching consistency",,,,,,,,,"","startup",,0
2025-12-13 11:48:19.974 UTC,,,9203,,693d5283.23f3,9,,2025-12-13 11:48:19 UTC,1/0,0,LOG,00000,"redo done at 78/68000028 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s",,,,,,,,,"","startup",,0
2025-12-13 11:48:19.999 UTC,,,9203,,693d5283.23f3,10,,2025-12-13 11:48:19 UTC,1/0,0,FATAL,XX000,"WAL ends before end of online backup",,"All WAL generated while online backup was taken must be available at recovery.",,,,,,,"","startup",,0
2025-12-13 11:48:20.002 UTC,,,9199,,693d5283.23ef,6,,2025-12-13 11:48:19 UTC,,0,LOG,00000,"startup process (PID 9203) exited with exit code 1",,,,,,,,,"","postmaster",,0

The wal dump looks like this for the specific file:

pg_waldump 0000002E0000007800000068
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 78/68000028, prev 78/67002940, desc: RUNNING_XACTS nextXid 1899065 latestCompletedXid 1899064 oldestRunningXid 1899065
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 78/68000060, prev 78/68000028, desc: CHECKPOINT_ONLINE redo 78/68000028; tli 46; prev tli 46; fpw true; xid 0:1899065; oid 189303; multi 459; offset 1171; oldest xid 717 in DB 16635; oldest multi 1 in DB 16635; oldest/newest commit timestamp xid: 0/0; oldest running xid 1899065; online
rmgr: XLOG len (rec/tot): 34/ 34, tx: 0, lsn: 78/680000D8, prev 78/68000060, desc: BACKUP_END 78/68000028
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 78/68000100, prev 78/680000D8, desc: SWITCH

When specifying recovery_target_lsn = '78/68000100' instead, it works fine.

What am I doing wrong? Am I reading the documentation wrong? Is this a bug?

Thanks for clarification!

Regards
Thomas

Browse pgsql-bugs by date

  From Date Subject
Next Message Álvaro Herrera 2025-12-13 12:44:38 Re: Re: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.
Previous Message Tender Wang 2025-12-13 10:01:51 Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list