Re: Recovery doesn't pause during PITR

Hi Laurenz,

Thank you for your email. I got it now how recovery works for Postgres 11
with the mentioned recovery parameters.

I will also test this scenario for Postgres 13.

Thanks and Regards,
Nikhil

On Wed, Mar 31, 2021, 15:14 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> 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
>
>