Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Book Image

PostgreSQL 9 Administration Cookbook - Second Edition

Overview of this book

Table of Contents (19 chapters)
PostgreSQL 9 Administration Cookbook Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Recovery to a point in time


If your database suffers a problem at 3:22 p.m. and your backup was taken at 4:00 a.m., you're probably hoping there is a way to recover the changes made between those two times. What you need is known as Point-in-Time Recovery (PITR).

Regrettably, if you've made a backup with pg_dump at 4:00 a.m. then you won't be able to recover to any other time. As a result, the term PITR has become synonymous with the physical backup and restore technique in PostgreSQL.

Getting ready

If you have a backup made with pg_dump, then give up all hope of using that as a starting point for a PITR. It's a frequently asked question, but the answer is still no. The reason it gets asked is exactly why I'm pleading with you to plan your backups ahead of time.

First, you need to decide what the point of time is to which you would like to recover. If the answer is "as late as possible", then you don't need to do a PITR at all, just recover until end of logs.

How to do it…

How do you decide to...