PostgreSQL PITR with pgBackRest: prove a named recovery target on an isolated clone

Use PostgreSQL 18 and pgBackRest already installed on the disposable source and isolated restore hosts. PGDG APT documents Ubuntu 26.10/26.04 and Debian 13/12; PostgreSQL binaries are under /usr/lib/postgresql/18/bin and packages are postgresql-18 and postgresql-client-18. PGDG RPM documents current RHEL, Rocky Linux and AlmaLinux 9/10 minors; binaries are under /usr/pgsql-18/bin and packages are postgresql18-server and postgresql18. The RHEL selection changes the clone root to /var/lib/pgsql/academy-pitr-20261028. It does not select an existing package cluster or initialize a service. RHEL 9.6 and 10.0 PGDG repositories are retired; use a currently maintained minor, not those archived streams.

These are documented command paths, not executed compatibility tests. Read /etc/os-release and verify the exact product/version before continuing. Existing accounts, approved network access and protected credentials are prerequisites. Do not restart existing services to provision tools, initialize a database, open a firewall port or alter a system Python installation to make this exercise run.

Important boundary: this rehearsal accepts only a self-contained lab cluster whose postgresql.conf, pg_hba.conf and pg_ident.conf are inside its data directory. The default Debian/Ubuntu package cluster with external configuration remains excluded; a binary-path change does not make that layout compatible. The next preflight must prove this before markers are created.

On SELinux-enforcing hosts, ordinary ownership/mode checks are not sufficient. Keep SELinux enabled. If a file, socket or connection is denied, have the authorized administrator inspect AVC records and the actual process/file context; do not guess a type, run chcon recursively or generate an allow-all policy. Inspect existing firewalld policy only when needed with authorized privileges; this guide requires no new inbound rule.

Run these checks on both the disposable source and isolated restore hosts as the PostgreSQL account, not on a client-only administration laptop.

cat /etc/os-release
id
test -x /usr/pgsql-18/bin/psql
/usr/pgsql-18/bin/psql --version
test -x /usr/pgsql-18/bin/pg_ctl
/usr/pgsql-18/bin/pg_ctl --version
pgbackrest version
getent passwd postgres
if command -v getenforce >/dev/null; then getenforce; id -Z; fi

On the authorized source host as the PostgreSQL OS user, inspect the binary/server major version and current data directory. Confirm the selected database is disposable and the stanza points to THIS cluster. The check command may create a restore-point/check WAL record; it is not purely read-only.

Stop if archiving is disabled, pgBackRest check fails, or the repository reports a different database system identifier. Preserve normal retention and scheduled backups.

This example deliberately restricts SQL names and stanza to lowercase ASCII letters, digits and underscores, starting with a letter, at most32characters; do not bypass the guard to accommodate another naming convention.

for value in 'academy_lab' 'academy_pitr_probe' 'academy_before_change' 'academy'; do
  printf '%s\n' "$value" | grep -Eq '^[a-z][a-z0-9_]{0,31}$' || exit 1
done
'/usr/pgsql-18/bin/psql' -X -v ON_ERROR_STOP=1 -d 'academy_lab' -c "SELECT version(); SELECT system_identifier FROM pg_control_system(); SHOW data_directory; SHOW config_file; SHOW hba_file; SHOW ident_file; SHOW archive_mode; SHOW archive_command;"
pgbackrest --stanza='academy' check
pgbackrest --stanza='academy' info
data_dir=$('/usr/pgsql-18/bin/psql' -X -At -d 'academy_lab' -c 'SHOW data_directory')
config=$('/usr/pgsql-18/bin/psql' -X -At -d 'academy_lab' -c 'SHOW config_file')
hba=$('/usr/pgsql-18/bin/psql' -X -At -d 'academy_lab' -c 'SHOW hba_file')
ident=$('/usr/pgsql-18/bin/psql' -X -At -d 'academy_lab' -c 'SHOW ident_file')
test "$config" = "$data_dir/postgresql.conf" && test "$hba" = "$data_dir/pg_hba.conf" && test "$ident" = "$data_dir/pg_ident.conf" || exit 1

In the disposable database, create a fresh dedicated schema. An existing schema must make the command fail; do not drop or overwrite it. Keep the exact database/schema identity in the rehearsal record.

'/usr/pgsql-18/bin/psql' -X -v ON_ERROR_STOP=1 -d 'academy_lab' <<'SQL'
CREATE SCHEMA academy_pitr_probe;
CREATE TABLE academy_pitr_probe.marker (id integer PRIMARY KEY, note text NOT NULL);
INSERT INTO academy_pitr_probe.marker VALUES (1, 'before-change');
SELECT * FROM academy_pitr_probe.marker;
SQL

Run a full backup on the approved rehearsal cluster. A full backup consumes repository space and I/O; obtain capacity from the owning backup plan rather than changing retention. Record the successful backup label shown by info.

The target must be after completion of the backup selected for restore. Do not leave the restore tool to choose an unrelated newer backup created after the target.

pgbackrest --stanza='academy' --type=full backup
pgbackrest --stanza='academy' info