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.
From:
Laurenz Albe Date: Subject:
Re: Performance bottleneck. High active sessions but postmaster kernel threads are in a sleep state, low CPU utilization
