Re: Recovery doesn't pause during PITR

On Wed, 2021-03-31 at 11:38 +0530, Nikhil Shetty wrote:
> I am trying to perform PITR using below settings in recovery.conf
>
> restore_command = 'cp /opt/arch_bkp/%f %p'
> recovery_target_time = '2021-03-31 05:49:10 UTC'
> recovery_target_action = 'pause'
>
> To test this I created a table after basebackup. I want the recovery to pause at the
> above specified time to check if the table is available. If the table is not available
> then I would like to extend the recovery_target_time and perform recovery again until my
> table is available.
>
> In my case the database is not paused and it opens for r/w connections as seen from below logs:
>
> 2021-03-31 05:52:56.433 UTC [4577] LOG: restored log file "000000010000000400000063" from archive
> cp: cannot stat ‘/opt/arch_bkp/000000010000000400000064’: No such file or directory
> 2021-03-31 05:52:56.593 UTC [4577] LOG: redo done at 4/63FFC750
> 2021-03-31 05:52:56.593 UTC [4577] LOG: last completed transaction was at log time 2021-03-31 05:49:07.634527+00
> 2021-03-31 05:52:56.619 UTC [4577] LOG: restored log file "000000010000000400000063" from archive
> cp: cannot stat ‘/opt/arch_bkp/00000002.history’: No such file or directory
> 2021-03-31 05:52:56.701 UTC [4577] LOG: selected new timeline ID: 2
> 2021-03-31 05:52:56.755 UTC [4577] LOG: archive recovery complete
> cp: cannot stat ‘/opt/arch_bkp/00000001.history’: No such file or directory
> 2021-03-31 05:52:56.762 UTC [4581] LOG: checkpoint starting: end-of-recovery immediate wait
> 2021-03-31 05:52:57.541 UTC [4581] LOG: checkpoint complete: wrote 16084 buffers (98.2%); 0 WAL file(s) added, 0 removed, 61 recycled; write=0.437 s, sync=0.273 s, total=0.779 s; sync files=20,
> longest=0.236 s, average=0.014 s; distance=1015800 kB, estimate=1015800 kB
> 2021-03-31 05:52:57.630 UTC [4575] LOG: database system is ready to accept connections
>
> PostgreSQL version: 11.11
>
> I don't see any issue with my configuration.

There are no WAL entries later than 2021-03-31 05:49:07.634527, so PostgreSQL
didn't reach the recovery target time.

You are probably surprised why PostgreSQL came up in that case. That was how v11
worked. The behavior has changed in v13; now you get an error message and recovery fails
if you don't reach the target time.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com