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 ...