Handling point-in-time recovery

Now, let's leave replication for a while and turn our attention to backups.

Replication is widely used to secure databases and to scale out reads. A similar technique can be used for backup purposes only: point-in-time recovery (PITR). In case of replication, the transaction log is replicated more or less instantly to make sure that those slaves have up-to-date data. The purpose of PITR is to replay transaction logs maybe days or weeks later to restore the database for a certain point in time.

Here is a practical example: let's assume we have done a base backup around midnight. All the transaction logs since then have been kept safely on a backup server. Now it is 5 p.m. and you have just found out that something important has been deleted at 3 p.m. What you can do now is to take your base backup, replay all the transaction logs up to 2:59 p.m. and move on from there. So, with a base backup on you can magically beam the database to a certain point in time of...