PostgreSQL Administration Essentials

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