From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nikhil Shetty <nikhil.dba04@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Recovery doesn't pause during PITR
Date: Wed, 31 Mar 2021 11:44:23 +0200
Message-ID: <52b6c6a64af984544fbc24f69b289e3b921d9577.camel@cybertec.at> (raw)
In-Reply-To: <CAFpL5VwQWMNzP-H4i3mvGFbgny34Vm41hPf9TDZ=RpjRyMpoDg@mail.gmail.com>
References: <CAFpL5VwQWMNzP-H4i3mvGFbgny34Vm41hPf9TDZ=RpjRyMpoDg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, nikhil.dba04@gmail.com
Subject: Re: Recovery doesn't pause during PITR
In-Reply-To: <52b6c6a64af984544fbc24f69b289e3b921d9577.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox