Chapter 3. Understanding Point-in-time Recovery

So far, you have endured a fair amount of theory. However, since life does not only consist of theory (as important as it may be), it is definitely time to dig into practical stuff.

The goal of this chapter is to make you understand how you can recover your database to a given point in time. When your system crashes, or when somebody just happens to drop a table accidentally, it is highly important not to replay the entire transaction log but just a fraction of it. Point-in-time Recovery will be the tool to do this kind of partial replay of the transaction log.

In this chapter, you will learn all you need to know about Point-in-time Recovery (PITR), and you will be guided through practical examples. Therefore, we will apply all the concepts you have learned in Chapter 2, Understanding the PostgreSQL Transaction Log, to create some sort of incremental backup or set up a simple, rudimentary standby system.

Here is an overview of the topics we will deal with in this chapter:

• Understanding the concepts behind PITR
• Configuring PostgreSQL for PITR
• Running pg_basebackup
• Recovering PostgreSQL to a certain point in time

At the end of this chapter, you should be able to set up Point-in-time Recovery easily.