postgresql - recovery_target_action not working with recovery_target_time

Question

For the recovery, I am using the following block:

restore_command = 'cp /mnt/backup/%f "%p"'
recovery_target_time = '2023-08-18 10:01:46'
recovery_target_action = 'pause'

But the recovery is not paused:

teste=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

teste=# SELECT pg_is_wal_replay_paused();
ERROR:  recovery is not in progress
HINT:  Recovery control functions can only be executed during recovery.

The log:

2023-08-18 11:41:27.933 -03 [1755] LOG:  database system was interrupted; last known up at 2023-08-18 10:01:07 -03
2023-08-18 11:41:28.317 -03 [1755] LOG:  restored log file "00000006.history" from archive
2023-08-18 11:41:28.319 -03 [1755] LOG:  restored log file "00000007.history" from archive
2023-08-18 11:41:28.321 -03 [1755] LOG:  restored log file "00000008.history" from archive
2023-08-18 11:41:28.322 -03 [1755] LOG:  restored log file "00000009.history" from archive
cp: cannot stat ‘/mnt/backup/0000000A.history’: No such file or directory
2023-08-18 11:41:28.324 -03 [1755] LOG:  starting point-in-time recovery to 2023-08-18 10:01:46-03
2023-08-18 11:41:28.325 -03 [1755] LOG:  restored log file "00000009.history" from archive
2023-08-18 11:41:28.518 -03 [1755] LOG:  restored log file "000000050000000000000068" from archive
2023-08-18 11:41:28.530 -03 [1755] LOG:  restored log file "00000005.history" from archive
2023-08-18 11:41:28.534 -03 [1755] LOG:  restored log file "00000006.history" from archive
2023-08-18 11:41:28.537 -03 [1755] LOG:  restored log file "00000007.history" from archive
2023-08-18 11:41:28.541 -03 [1755] LOG:  restored log file "00000008.history" from archive
2023-08-18 11:41:28.546 -03 [1755] LOG:  redo starts at 0/68000028
2023-08-18 11:41:28.547 -03 [1755] LOG:  consistent recovery state reached at 0/68000138
2023-08-18 11:41:28.547 -03 [1751] LOG:  database system is ready to accept read only connections
2023-08-18 11:41:28.583 -03 [1755] LOG:  restored log file "000000050000000000000069" from archive
2023-08-18 11:41:28.627 -03 [1755] LOG:  restored log file "00000008000000000000006A" from archive
2023-08-18 11:41:28.671 -03 [1755] LOG:  restored log file "00000009000000000000006B" from archive
cp: cannot stat ‘/mnt/backup/00000009000000000000006C’: No such file or directory
2023-08-18 11:41:28.682 -03 [1755] LOG:  redo done at 0/6B0000D8
2023-08-18 11:41:28.682 -03 [1755] LOG:  last completed transaction was at log time 2023-08-18 10:01:45.5765-03
2023-08-18 11:41:28.689 -03 [1755] LOG:  restored log file "00000009000000000000006B" from archive
cp: cannot stat ‘/mnt/backup/0000000A.history’: No such file or directory
2023-08-18 11:41:28.701 -03 [1755] LOG:  selected new timeline ID: 10
2023-08-18 11:41:28.716 -03 [1755] LOG:  archive recovery complete
2023-08-18 11:41:28.717 -03 [1755] LOG:  restored log file "00000009.history" from archive
2023-08-18 11:41:28.728 -03 [1751] LOG:  database system is ready to accept connections
sending incremental file list
0000000A.history

sent 256 bytes  received 35 bytes  582.00 bytes/sec
total size is 405  speedup is 1.39
2023-08-18 11:41:59.079 -03 [1790] ERROR:  recovery is not in progress
2023-08-18 11:41:59.079 -03 [1790] HINT:  Recovery control functions can only be executed during recovery.
2023-08-18 11:41:59.079 -03 [1790] STATEMENT:  SELECT pg_is_wal_replay_paused();

Why isn't it paused? I need to check the PITR before opening the database for new connections, and if necessary, continue the recovery.

Answer 1

The problem is twofold:

1. the archived WAL does not contain a transaction that is more recent than recovery_target_time

2. you are using a PostgreSQL version older than v13

Since the recovery target is never reached, the recovery_target_action does not apply. In PostgreSQL v12 and below, this causes the server to report success and promote. From PostgreSQL v13 on, you get an error message saying that the recovery target was not reached.