Why is Postgres basebackup needed with WAL for PITR?

Question

I'm reading the following article to setup PITR. One thing I'm confused about is why the article recommends performing a basebackup (full cluster backup) after setting up continuous WAL archiving. I thought WAL was the actual data? So if I have WAL from time t=0 why would I need any other backup? I've asked in two other postgres communities, but the response to this point has been "because in practice you won't have WALs from time t=0" or that it's sort of unknown as to why both are really needed. Anyway just curious, thanks!

Answer 1

WAL contains information like: “in file X, block Y, write these 13 bytes at offset z” or “extend file X by 1 8kB block”.

You always need some starting point from which you can recover WAL:

• With crash recovery, that is the latest checkpoint, whose position is written to the control file.

• With PITR, the checkpoint is taken from the backup_label file that is created when you run a base backup and contains the checkpoint that started the backup.

If you don't have a point from which to start, how can you recover?

Perhaps your misconception is that the database is initially empty, so all you need are the WAL files from the beginning of time. But that isn't true: even right after initdb, the database already contains catalog tables and metadata.

Answer 2

There is a pseudorandom Database system identifier which differs from one database system to another. This is recorded in both pg_controldata, and also in each WAL segment, and must match to replay WAL into a server. In my hands, running initdb multiple times gives directories which are identical other than the contents of pg_controldata and pg_wal/000000010000000000000001 (and the filesystem time stamps, of course).

So if you feel like running in traffic while juggling chainsaws, you could skip the base backup, and then just run a new initdb and try to retrofit the system identifier into the new pg_controldata file.

Enjoy blowing yourself up. If successful, your reward will be not having to take an initial base backup, which compresses down to 1.35MB.

This is an actual issue immediately after a pg_upgrade. The new system has to be considered vulnerable until a new basebackup is taken (which would presumably be far larger and slower than 1.35MB). If you used pg_upgrade -k, this new basebackup could constitute the vast majority of the time window needed to complete the upgrade.