PostgreSQL Point-In-Time Recovery Getting Error with No valid checkpoint record

Question

I am trying to perform a Point-In-Time Recovery using the WAL_ARCHIVE process. The archive command is added to the postgresql.conf file and I can see the WAL being archived in the backup-archive directory. When I try to start the service I get PANIC: could not locate a valid checkpoint record

I am using the below step-by-step process.

1. low level api basebackup SELECT pg_start_backup('label', true, false);
2. copying the data directory of my cluster tar -zcvpf basebkPostgres20230110New.tgz /PostgreSQL/13/data
3. closing my basebackup SELECT * FROM pg_stop_backup(false, true);
4. Stopping the postgres service
5. Removing the current's cluster data directory
6. Restoring the backed up data directory
7. Removing the contents of the pg_wal directory
8. Setting the restore_command in the postgresql.conf file
9. Starting the postgres service

Answer 1

You forgot the backup_label file and recovery.signal. You have to capture the result of pg_stop_backup (or pg_backup_stop from v15 on) and create backup_label from the contents. That file has to be in the restored data directory. Also, you have to create recovery.signal in the data directory, so that PostgreSQL starts in archive recovery mode and reads your restore_command.

Without restore_command, PostgreSQL uses the WAL in pg_wal, which is empty. Without backup_label, PostgreSQL thinks that it can recover from the checkpoint indicated by the control file pg_control. Even if that worked, the result would be a corrupted database, since you have to recover from the start of the backup.

recovery.signal is documented here (step 7), and backup_label is documented here (step 4).