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