Unable to restore from WAL files

Question

I copied both the base backup and the WAL files from my primary Postgres database to a standby database and tried to restore from them but I'm getting this error:

PostgreSQL Database directory appears to contain a database; Skipping initialization

LOG:  database system was shut down at 2024-02-16 19:38:58 UTC
LOG:  starting archive recovery
LOG:  restored log file "000000010000000000000001" from archive
LOG:  invalid primary checkpoint record
LOG:  restored log file "000000010000000000000001" from archive
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 26) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

I tried adding a checkpoint after inserting a bunch of records into a table but I'm still getting the same error.

The general flow of my script is:

# Set the postgresql.conf values (i.e.: archive_command)
...

# Set the recovery.conf values (i.e.: restore_command)
...

# Create a base backup
docker exec primary-db pg_basebackup -h localhost -p 5432 -U postgres -D /var/lib/postgresql/data/basebackup -Ft -z -P -X fetch

# Populate the database tables with new records
...

# Create a checkpoint
docker exec primary-db psql postgres -U postgres -c "CHECKPOINT;"

# Copy the base backup
cp -a $PRIMARY/basebackup/. $REPLICA/wal/

# Copy the WAL files
cp -a $PRIMARY/wal/. $REPLICA/wal/

# Restart the standby database to start the restoration
docker restart replica-db

I have the base backup and the WAL files all inside the same directory. I'm not sure if the base backup should be stored somewhere else. What am I doing wrong here?

Answer 1

I have the base backup and the WAL files all inside the same directory.

That's not correct. The WAL directory, pg_wal, is inside the data directory, the one created from the base backup.

Answer 2

You can mount a separate disk for WAL files, but put a symlink to the mounting directory inside the directory specified in the postgres configuration file in the data_directory parameter

The best way to create a DB replica is to use the pg_basebackup command with the stream option

Here is a version of the script for starting the replica. Note, I'm using a non-docker variant of postgresql

#!/bin/bash

# Stop PostgreSQL if it is running
systemctl stop postgresql

# Execute pg_basebackup to get data from the wizard
su - postgres -c "pg_basebackup -P -R -X stream -c fast -h master_db_address -U replica -D /var/lib/postgresql/data/15/main"

# We move the WAL files to a separate disk
mv /var/lib/postgresql/data/15/main/pg_wal/* /mnt/pg_wal

# Delete the pg_wal folder from the database directory
rmdir /var/lib/postgresql/data/15/main/pg_wal

# Create a symlink to the directory with WAL files
ln -s /mnt/pg_wal /var/lib/postgresql/data/15/main/pg_wal

# Change the owner and group for the pg_wal folder
chown -R postgres:postgres /mnt/pg_wal

# Start PostgreSQL
systemctl start postgresql

I want to remind you that this is an approximate script that will help you make a replica of the database.