Postgresql - How to interpret backup_label contents?

Question

Postgres version 11.

I've read this: https://www.postgresql.org/docs/11/app-pgbasebackup.html

But can't really find what I'm looking for.

Performing a basebackup creates 2 files for me: base.tar.gz and pg_wal.tar.gz

base.tar.gz contains a file backup_label

Which contains something like:

START WAL LOCATION: 5CD/87000850 (file 00000001000005CD00000087)
CHECKPOINT LOCATION: 5CD/882A2558
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 
LABEL: pg_basebackup base backup
START TIMELINE: 1

And pg_wal.tar.gz contains this:

00000001000005CD00000087
00000001000005CD00000088
00000001000005CD00000089
00000001000005CD0000008A
00000001000005CD0000008B
...
00000001000005CD000000AF

So, incremental files.

backup_label has a field START WAL LOCATION which literally points to 00000001000005CD00000087, the first file in the pg_wal.tar.gz archive.

Then it has CHECKPOINT LOCATION: 5CD/882A2558.

What is this "CHECKPOINT LOCATION"? I don't see anything to correlate 882A2558 to. Or is that not intended?

Am I to interpret that as "The wal archives created while pg_basebackup was running are in pg_wal.tar.gz and they start at 5CD/87000850 and stop at 5CD/882A2558" ?

I'm just wondering: does pg_wal.tar.gz contain all the wal archives I need to do a successful restore? And if not, is there some other file that will provide me this info?

And if this is clearly written in the documentation, well I just read the entire page and didn't recognize anything in there as the answer to my question? Did I miss it?

EDIT: Well, that cleared some things up, I think I got the answer(s) I was looking for and then some more.

Thanks for all who replied.

Answer 1

Welcome to dba.se! Try to confine posts to a single question so we know what to answer. Please update your question to identify the question you are asking if I'm answering the wrong one.

The most important one I think is:

does pg_wal.tar.gz contain all the wal archives I need to do a successful restore?

Yes, the contents of pg_wal.tar need to be put in the pg_wal/ directory in the data directory extracted from base.tar. You are correct that these are the WAL entries added during the backup itself. They are NOT archived WAL. (This assumes the default WAL storage directory in the PostgreSQL configuration.)

If you want to know what the checkpoint LSN is all about, use pg_waldump on the WAL files and you will find the checkpoint transaction at that sequence number, which should be near the beginning of the WAL captured during the backup. The checkpoint ensures that pending page writes are committed to disk so that pg_basebackup can fetch them. In your case the WAL switched files before the checkpoint completed, so the checkpoint completion will appear in file 00000001000005CD00000088 (note that the last 2 digits of the WAL file correspond to the first 2 digits of the LSN after the slash).

Answer 2

The backup_label file is there for the software to read when it enters recovery. It is not meant for you to interpret. @dwhitemv is definitely wrong, it is a critical part of your backup and is not just there for your pleasure.

Historically this file was created in the base directory at the start of an online backup and was then copied along with the rest of the files by the file-copy part of the backup. (Currently, that is not the preferred way of doing things--now it is just stored in memory during the backup and then written only at the end if you use the newest method). Since historically it was created at the start of a backup and copied at some unknown time in the middle of the backup, it didn't know anything about when the backup would end, as that had not happened yet. So it doesn't contain info on that.

Answer 3

5CD/882A2558 (in file 00000001000005CD00000088) is where you will find the WAL record for checkpoint completion. 5CD/87000850 is the position from which WAL will be replayed.

You can use the pg_walfile_name function to find the file for a certain LSN.