Postgresql – Postgres WAL logs— point in time recovery with (time) gaps in the logs

postgresql

I'm setting up continuous archiving/ point in time recovery on Postgres and I've noticed some odd behavior. Here's the sequence of events:

  • Save a base backup
  • Archive WAL logs during normal DB operation
  • Turn off the database for a little while
  • Turn on database
  • Archive WAL logs during normal DB operation
  • Run a complete restore

I expected to recover all the data, including that which occurred after the database downtime.

What I'm observing is that only data from before the downtime is being restored. In fact, Postgres never even asks for/looks for the WAL logs from after the downtime.

(To be clear, there is no missing data, just a period of time in which the DB was not running and thus no WAL logs were shipping.)

Is this the intended behavior? I was unable to find any info on the specific logic by which Postgres decides which WAL logs to apply.

If so, does that mean a new base backup should be taken after any DB downtime?

Best Answer

PostgreSQL does not care when the WAL logs are generated, it just reads them sequentially on restore/recovery.

If the WAL logs were properly sent after the restart of the database, they should be restored as well.

However, if there is even a single WAL file missing for some reason (in this example, I'd guess that'd be either the last segment before the shutdown or one or more of the first ones from after the startup), which is typically either an incorrectly configured archive_command (missing error checks for example) or simply a broken archive server, the restore process cannot go past that point.

Related Question