>>>>> "Thorsten" == Thorsten Schöning <tschoening@xxxxxxxxxx> 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)
