Understanding the purpose of PITR

PostgreSQL offers a tool called pg_dump to back up a database. Basically, pg_dump will connect to the database, read all of the data in "repeatable read" transaction isolation level and return the data as text. As we are using "repeatable read," the dump is always consistent. So, if your pg_dump routine starts at midnight and finishes at 6 A.M., you will have created a backup that contains all of the data as of midnight, but no further data. This kind of snapshot creation is highly convenient and perfectly feasible for small to medium amounts of data.

Tip

A dump is always consistent. This means that all the foreign keys are intact. New data added after starting the dump will be missing. It is the most common way to perform standard backups.

But what if your data is so valuable and, maybe, so large in size that you want to back it up incrementally? Taking a snapshot from time to time might be enough for some applications, but for highly critical data, it is...