The one thing to understand first
Managed PostgreSQL backups are not a nightly pg_dump. They are the combination of periodic storage snapshots and continuous WAL archiving. The snapshot is a starting image; the archived WAL is the stream of every change since. Replay the WAL on top of a snapshot up to a chosen moment and you can restore the database to any second within your retention window. That capability is point-in-time recovery (PITR).
How PITR works under the hood
Take a snapshot at midnight. Keep every WAL segment generated afterward. To recover to 09:42:30, the service restores the midnight snapshot and replays WAL forward, stopping precisely at 09:42:30. Because PostgreSQL’s WAL is a complete, ordered redo log, this reconstruction is exact. The same mechanism that powers replication powers recovery — it is WAL all the way down.
Restore creates a new instance
Across RDS, Cloud SQL, and Flexible Server, a PITR restore produces a new database instance/endpoint; you do not rewind the existing one in place. That means recovery includes a cutover step — repoint the application at the restored endpoint — which you should rehearse. The serverless/branching designs (Neon, Lakebase) expose the same idea more fluidly as “time-travel” branches created from history.