Did the original article not specifically make a point about Linux’s `rename` to be atomic only in the happy case and not when a crash happens during the rename?

Rename is the most atomic you can get on Unix. Original article talks about partial file updates. New SQLITE file + rename should be as failproof as possible. And I’m any case nothing is 100% safe, so redundancy is a hard requirement for real safety.

I've encountered one gotcha there: SQLite operations may create "-journal" files, and you may have to be careful exactly what files you're copying or moving around.

More: https://sqlite.org/howtocorrupt.html , https://sqlite.org/tempfiles.html

I was referring to the last section of the article, the part where it says rename isn’t atomic on crashes. Why not migrate traditionally via DDL in a transaction?

It's not clear to me if they're correct about that. The way it's written, it seems to be saying "my read of the POSIX standard suggests rename may not be atomic on crashes", rather than "there are POSIX implementations in common use that have been observed to have non-atomic renames on crashes".

On Windows you will at least have an old version and a new version lying around intact. Would take this over a single corrupted file any day.

Unless you call FlushFileBuffers after all writes are finished, Windows makes no guarantee that the physical copy of a file on-disk will contain all previously written data, even if the file has been subsequently closed and renamed.

Typical (mis)behavior includes correctly-sized files that consist partially or entirely of zero bytes (because NTFS does guarantee newly-allocated space will be zero-filled).

Nevertheless, I agree with you, and, therefore, when dealing with application-managed files (as opposed to user-visible documents) on local NTFS filesystems, I typically

1. Write to a temporary file filename.temporary, say.

2. FlushFileBuffers

3. Rename the original file to e.g., filename.original.

4. Rename the temporary file to the original filename.

5. Delete filename.original.

Then, in some thread-safe manner at some point before accessing any such files — at application startup, say, when performance permits — I remove lingering ".temporary" files and restore any orphaned ".original" files to their original filenames.

This, of course, assumes FlushFileBuffers works properly, so, in particular, still fails in the presence of storage devices, drivers, and configurations (e.g., "Turn off Windows write-cache buffer flushing") that break buffer flushing, which are not nearly as rare as they should be.

> Unless you call FlushFileBuffers after all writes are finished, Windows makes no guarantee that the physical copy of a file on-disk will contain all previously written data, even if the file has been subsequently closed and renamed.

On *nix you need to sync before renaming as well.