On Mon, Jun 8, 2015 at 3:13 PM, otheus uibk <otheus.uibk@xxxxxxxxx> wrote:Thank you, all. The manual for 9.4 is indeed clearer on this point than the 9.1 version.
Just to nit-pick, I see nowhere in either version of the manual the indication that it is normal for postgresql to continue to update files in its data catalog between pg_start_backup and pg_stop_backup. The closest I see comes in this paragraph:
It may not be explicit because the author assumes that such writing occurring is self-evident. If it did not then for any reasonably sized database the system would I/O starve or simply run out of memory as the changes continue to pile up in the buffers while the backup is running.
The normal operation of the database operates on the same principle. In between checkpoints the data files are constantly being written. A crash means that there is more data in WAL to write out to the data files. The data files cannot be reverted back to their state at the time of the checkpoint. Instead, the replay of the WAL simply reapplies every change - even those that did make it out during the period between the checkpoint and the crash.
As far as the backup routine is concerned a checkpoint occurs only when you call pg_start/end_backup and the system ensures that every WAL file generated during the intervening period is kept around so that the data files being copied, and changed, in the intervening period can be made whole. The first checkpoint ensure that all data in present in those files and a restoration will replay every WAL file to ensure that the final state of each data file matches the state of the database as of the time the last WAL file present was created (typically the one closed out at the pg_end_backup call).
David J.
