Re: A question about possible recovery inconsistency

>But why do you want to do that, if all that you have to do is specify
"recovery_target = 'immediate'" to recover to the end of the backup?

Because automation scripts do not know if transactions are available
after some point in time or not. But automation scripts know that
backup was completed successfully at that point.
For example:
We want to provide time to recover the database.
1. Base backup restored, wal files are applied successfully if there
is a transaction.
2. Base backup restored, wal files are not applied successfully, even
if we have the correct wal file after target time.
eg. this wal file was created with help: pg_create_restore_point / pg_switch_wal

It looks inconsistent, because we can restore save archive by name,
but we can not restore it by time, even if this time is less when the
named point was created.

As workaround we just insert a fake record into database, but this
looks very questionable: Why do we need to insert more records into
database after successful backup??

On Wed, Oct 11, 2023 at 3:45 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Tue, 2023-10-10 at 11:46 -0400, Eugen Konkov wrote:
> > [wants to avoid
> > FATAL: recovery ended before configured recovery target was reached
> > that is issued in v13 and later]
> >
> > 1. Why here (in experiment2.txt) redo done at 0/7000028 when recovery
> > target name "2023-10-10 15:07:37" is at 0/7000090?
> > I suppose 0/7000090 should be included, because
> > `recovery_target_inclusive=true` by default.
>
> Because there was no transaction at 0/7000090.
>
> > 2. Is there any way to include a label into a base backup which I can
> > use as `recoverty_target_name`?
> > This is not clear from documentation
> > https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
> > Is 'label' the name for `recovery_target_name` /
> > `pg_create_restore_point` is called by `pg_backup_start`?
>
> No.
>
> > 3. Is there any way to get the latest time from a base backup which is
> > reachable and could be used as the value for `recovery_target_time`?
> > As a workaround for XX000 error I inserted one additional record into
> > the database, so a new WAL file is generated. Then I can use the t3
> > value for `recovery_target_time`.
> > This only works when archive_command/restore_command was configured.
> > But without them it seems I can not use the `recovery_target_time`
> > option. Is this true?
>
> Perhaps you could use the time from the "backup" file in the WAL archive,
> not sure.
>
> But why do you want to do that, if all that you have to do is specify
> "recovery_target = 'immediate'" to recover to the end of the backup?
>
> Yours,
> Laurenz Albe

--

Eugen Konkov

DevOps Engineer, Planitar Inc.

M. 416-276-1715

ekonkove(at)planitar(dot)com | goiguide.com

560 Parkside Drive, Unit 401

Waterloo, ON, Canada N2L 5Z4