>>>>> "Thorsten" == Thorsten Schöning <tschoening@am-soft.de> writes:
Thorsten> "since the last checkpoint": Missing WAL-segments mean a loss
Thorsten> of data only.
No. Missing WAL-segments means that data is inconsistent (and there are
no bounds on the degree of inconsistency: it's quite possible to lose
all your data).
For crash recovery, consistency is guaranteed by the WAL-before-data
rule, which (providing fsync is working correctly) forces WAL records to
be persistently stored _before_ the corresponding data file changes can
be written back to the filesystem.
For base backups you don't have this: it will contain data pages that
were modified after the start of the backup. If the WAL for these
modifications is not replayed, then (unless your filesystem copy is a
perfectly atomic snapshot) these will be inconsistent with other data
pages copied at different times; the state of the files will not
correspond to a valid state of the database. This might not be
immediately noticable; it might cause incorrect data to be returned; it
might cause errors accessing some data; it might cause loss of entire
tables or databases.
I can't stress this strongly enough: the results of a base backup are
NOT USABLE unless you have every matching WAL record between the backup
start and end locations.
(If you're doing genuinely atomic filesystem snapshots - which are a
valid way to do backups, though you must snapshot the data dir, all
tablespaces, AND the WAL directory in one atomic operation - then you
don't need pg_start_backup and generally shouldn't use it.)
Thorsten> It doesn't mean that formerly "checkpointed" data gets
Thorsten> magically broken, else crash recovery wouldn't work like
Thorsten> described in the docs.
Crash recovery has the advantage of knowing that the WAL-before-data
rule was being enforced (and if it was not, for example if you turned
fsync off or ran with unsafe caching, then it can easily fail resulting
in total effective loss of the data).
Thorsten> Which makes sense if all WAL-archives are simply considered
Thorsten> to be incremental changes based on some former full backup.
Thorsten> But that's the point: I don't see how WAL-archives created
Thorsten> between pg_start- and pg_stop_backup are any different to
Thorsten> later ones.
They are not any different (other than having full-page-writes forced
on, which is the default anyway). What's different is the state of the
data files.
--
Andrew (irc:RhodiumToad)