Managing point-in-time recovery

The pg_dump utility is more of a traditional form of creating a backup. It makes sense for a small amount of data, but it tends to have its limitations as soon as the amount of data grows beyond a certain limit. Don't get me wrong; pg_dump works perfectly even with terabytes of data. However, let's assume you've got a dump of a 10 TB beast! Does it really make sense to replay a 10 TB database from a dump? Just consider all the indexes that have to be built, and consider the insane amount of time it will take to do that. It definitely makes sense to use a different method. This method is called point-in-time recovery (PITR), or simply xlog archiving. In this section, you will learn about PITR in detail.

How PITR works

The idea behind PITR is to take a snapshot of the data and archive the transaction log created by PostgreSQL from then on. In case of a crash, it is then possible to restore any given point in time after the initial backup (base backup) has finished...