Greetings,
* Thorsten Schöning (tschoening@am-soft.de) wrote:
> Guten Tag Stephen Frost,
> am Freitag, 23. Juli 2021 um 17:24 schrieben Sie:
>
> > [...]Similar, once you reach the BACKUP_END record of
> > a backup you know the database is consistent- but only if all of the
> > files that PG was writing to were copied between the start checkpoint
> > of the backup and the BACKUP_END record in the WAL.
>
> So I am correct that the recovery process starts at what is documented
> as CHECKPOINT LOCATION in the file backup_label? If the data directory
> looks like a more current checkpoint might be available, that is NOT
> used? Instead, all available WALs are replayed?
Yes, the recovery has to start from the checkpoint in the backup label.
The fact that pg_control might have a more recent checkpoint is exactly
the problem- any files which were copied before the checkpoint in
pg_control wouldn't have the correct contents and wouldn't be fixed by
the recovery process. Furthermore, if PG were to stop replaying before
getting to the correct backup-end location then the database might not
be consistent at that point because some records had been written into
the WAL as committed but hadn't made it to the heap yet. There's risk
on both sides.
> But how come the state of other data files into the recovery process?
> that's what Laurenz Albe's example is about: A data file which is more
> current than expected because that file is only copied, but the
> associated changes are stored in WALs after pg_stop_backup and
> therefore need to be considered lost.
I'm not sure that you're quite understanding the example because we
always write to WAL first and sync that before writing to the heap, so
there isn't a case of a "more recent" file which is somehow ahead of
what's in the WAL.
> Why is the data file with that additional row in B used at all? The
> additional row is associated with a transaction number not available
> when restoring based on CHECKPOINT LOCATION + WALs of pg_stop_backup.
The data file is copied because it's part of the database..? If you
didn't copy it, then you'd be missing a lot of data. As for the
question about if that row would be visible, it certainly might be- but
it depends on when the transaction log files were copied, or if perhaps
the tuple ended up being frozen somehow.
> So why is that row used at all instead of simply ignored? That would
> actually revert the cluster to the state of pg_stop_backup, but as
> said, backup outdate anyway. I still don't get why the result is not
> as consistent as e.g. a crash at the same point in time instead of
> doing a backup.
How would you know to ignore it if that tuple was frozen?
A crashed PG cluster isn't consistent until WAL replay from the last
checkpoint to the ending point of WAL has completed. That's exactly the
same with a backup, but the checkpoint you have to start from is the one
stored in the backup label and the point in the WAL that has to be
reached for consistency is the one that's at the backup-end location in
the WAL. Otherwise they're both the same. The reason you have to start
from the start-backup checkpoint is exactly because the data files are
being copied over a period of time and not all at once- unlike with a
crash where everything stops at the same time (and if that doesn't
happen, you definitely do have the risk of having corruption from such a
partial-crash, though we've tried our best to detect such cases and deal
with them, but we need the kernel to actually tell us when there's a
problem and that's been something of an interesting challenge..).
> Mit freundlichen Grüßen
>
> Thorsten Schöning
Seriously, when your signature is twice the size of your email, it's too
much. Please cut it down when posting to these lists, we certainly
don't need hundreds of copied of it in our archives or everyone's mail
boxes.
Thanks,
Stephen