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 the pg_dump
utility 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
utility, 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 we are pleading with you to plan your backups ahead of time.
First, you need to decide from what point in time 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 the end of the logs.