A checkpoint records progress that lets a database recover without treating its entire log history as outstanding work. The exact operation depends on the engine. PostgreSQL synchronizes the data changes required for a checkpoint and establishes a redo starting point. SQLite in WAL mode transfers eligible committed page frames from its WAL into the main database file. Neither operation is simply another name for transaction commit. ...
Point-in-time recovery starts with a valid physical base backup and replays the WAL required to reach a chosen recovery target. The target must fall within the recoverable history of that backup and its available timelines. Archiving does not create the ability to recover to arbitrary earlier dates, nor does it repair a gap between the backup and the desired endpoint. ...
An unlogged PostgreSQL table keeps normal SQL transaction behavior while giving up crash recovery for its contents. It is useful for reproducible staging data, but a committed row is not guaranteed to survive a server crash. The distinction is between clean operation and recovery after an unclean shutdown, not between ordinary queries and every restart. ...
Continuous WAL archiving preserves completed log files outside PostgreSQL’s active WAL directory. Together with a suitable base backup and an uninterrupted recovery chain, those files let recovery replay logged database changes to a covered target. Archiving is separate from ordinary commit acknowledgment: a transaction can commit while its WAL segment is still open or while the archive destination is unavailable. ...