Point-in-Time Recovery Techniques
Point-in-time recovery restores a database to its state immediately before a chosen moment. It combines an earlier backup with a continuous record of later changes, then replays those changes only as far as the selected recovery target.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
Point-in-Time Recovery Techniques
Point-in-time recovery, or PITR, reconstructs a database as it existed at a selected recovery target. The target may be a timestamp, transaction identifier, log position, system change number, or named restore point. PITR is useful when the newest state is unsafe, such as after an accidental deletion, a faulty deployment, or logical corruption.
PITR is not a single backup file. It is a recovery chain with two essential parts:
- A baseline backup captures a consistent database state before the target.
- A continuous change log records committed changes after that baseline.
Database engines use different names for the change log. PostgreSQL archives write-ahead log records, MySQL retains binary logs, SQL Server backs up its transaction log, Oracle applies redo, and MongoDB Atlas can replay the oplog. The recovery pattern is the same: restore the baseline, replay an unbroken change sequence, and stop at the selected boundary.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpublication800-34r1.pdf
Supports
- Recovery point and recovery time objectives
- Recovery testing, validation, reconstitution, and dependency planning
- https://www.postgresql.org/docs/current/continuous-archiving.html
Supports
- Baseline backup plus archived WAL recovery pattern
- Continuous WAL requirements, recovery targets, replay, and timelines
- PostgreSQL target types and recovery-chain failure behavior
- https://dev.mysql.com/doc/refman/8.4/en/point-in-time-recovery-binlog.html
Supports
- Full backup plus binary-log recovery pattern
- Time and event-position recovery choices
- https://dev.mysql.com/doc/refman/8.4/en/point-in-time-recovery-positions.html
Supports
- Binary-log event-position targeting around an unwanted statement
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-ver17
Supports
- Full or differential backup followed by ordered log restores
- STOPAT behavior and full or bulk-logged recovery-model limits
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/apply-transaction-log-backups-sql-server?view=sql-server-ver17
Supports
- Chronological log application and the consequence of a missing log backup
- https://docs.oracle.com/en/database/oracle/oracle-database/26/bradv/rman-performing-flashback-dbpitr.html
Supports
- Backup, incremental backup, and redo application for database PITR
- Time, SCN, log sequence, and restore-point targets
- PITR use cases and Oracle incarnation behavior
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html
Supports
- Custom and latest-restorable-time recovery to a new RDS instance
- Retention-window and destination configuration behavior
- https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql
Supports
- Azure SQL point-in-time restore within retention to a new database
- Managed backup access and destination constraints
- https://cloud.google.com/sql/docs/postgres/backup-recovery/pitr
Supports
- Cloud SQL for PostgreSQL point-in-time recovery operations and destinations
- https://www.mongodb.com/docs/atlas/backup/cloud-backup/configure-backup-policy/
Supports
- Oplog replay for point-in-time restore within an Atlas restore window
- https://github.com/sindresorhus/awesome
Supports
- Discovery of the Database Tools and PostgreSQL awesome lists
- https://github.com/mgramin/awesome-db-tools
Supports
- Discovery of Barman, pgBackRest, and other database backup tools
- https://github.com/dhamaniasad/awesome-postgres
Supports
- Discovery of Barman, pgBackRest, WAL-G, and pitrery in the PostgreSQL backup ecosystem
- https://pgbackrest.org/user-guide.html
Supports
- pgBackRest baseline, WAL archive, target selection, restore, and validation workflow
- https://docs.pgbarman.org/release/3.12.1/user_guide/recovery.html
Supports
- Barman time, transaction, LSN, and named targets plus recovery actions
- https://wal-g.readthedocs.io/
Supports
- WAL-G archival and restore scope across PostgreSQL, MySQL, and SQL Server
- https://dalibo.github.io/pitrery/
Supports
- pitrery management of PostgreSQL PITR backups and WAL archives
- https://www.postgresql.org/
Supports
- PostgreSQL product identity and licensing
- https://www.mysql.com/
Supports
- MySQL product identity and Community and commercial editions
- https://www.microsoft.com/en-us/sql-server/
Supports
- SQL Server product identity and commercial positioning
- https://www.oracle.com/database/
Supports
- Oracle Database product identity and commercial positioning
- https://aws.amazon.com/rds/
Supports
- Amazon RDS managed database identity and engine coverage
- https://azure.microsoft.com/en-us/products/azure-sql/database
Supports
- Azure SQL Database managed-service identity
- https://cloud.google.com/sql
Supports
- Cloud SQL managed-service identity and engine coverage
- https://www.mongodb.com/atlas
Supports
- MongoDB Atlas managed-service identity and cloud backup context