PostgreSQL PITR on RHEL 9 for Real Recovery

Why point-in-time recovery matters

If a bad migration drops rows at 14:17, a nightly backup will not save you. PostgreSQL point-in-time recovery, or PITR, lets you restore a database to a specific moment before the mistake. That is the recovery path many Hostperl customers need after an application incident, operator error, or failed deploy. This tutorial shows how to set up a practical PITR workflow on RHEL 9 using PostgreSQL, local base backups, and archived WAL files.

Use this procedure when you need something more precise than a full backup restore. For larger production environments, it pairs well with a Hostperl VPS or a dedicated server hosting plan when the database load or recovery objective justifies extra CPU, memory, and storage headroom.

This guide focuses on PostgreSQL PITR on RHEL 9. It uses the RHEL-family toolchain, SELinux-aware paths, and systemd service management. If your workload runs on Debian or Ubuntu instead, the recovery idea is the same, but the package names and service details differ.

What you will build

You will configure PostgreSQL to archive WAL files, take a base backup, simulate a failure, and restore the database to a chosen recovery timestamp. The goal is a tested recovery path, not just a configuration change.

• RHEL 9 with PostgreSQL 16 from the PGDG repository
• WAL archiving to a local backup directory
• Base backups with pg_basebackup
• Recovery using recovery.signal and restore_command
• Verification before and after the restore

If you want a deeper recovery pattern after this, Hostperl also has a related guide on PostgreSQL WAL archiving for recovery, which covers the same operational family from a different RHEL-compatible distribution angle.

Prerequisites and layout

This tutorial assumes a fresh RHEL 9 server. The examples use the documentation IP 203.0.113.10; replace it with your server's real public IP.

We will use these example values:

• PostgreSQL data directory: /var/lib/pgsql/16/data
• WAL archive directory: /var/lib/pgsql/16/wal_archive
• Backup directory: /var/backups/postgresql
• Database name for testing: appdb
• Application user for testing: appuser

Connect to the server and confirm the OS

On your local computer:

ssh root@203.0.113.10

203.0.113.10 is a documentation address. Replace it with the real public IP assigned to your server. Keep the root session open until you finish the non-root verification steps.

cat /etc/os-release

This confirms that you are on RHEL 9 or a compatible RHEL-family system. You should see Red Hat Enterprise Linux or a compatible variant in the output.

Create a non-root admin account

On the VPS as root:

useradd -m -G wheel -s /bin/bash deploy

This creates the deploy account, adds it to the wheel group, and gives it a login shell. Use your own administrator username if needed, but keep the pattern. Now set a password only if you need password fallback during initial setup:

passwd deploy

Create the SSH directory and permissions:

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh

Append your public key to authorized_keys. Replace the sample key with your real public key:

cat > /home/deploy/.ssh/authorized_keys <<'EOF'
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIExampleReplaceThisWithYourRealPublicKey deploy@laptop
EOF
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Open a second terminal and test the new login before changing any SSH rules:

ssh deploy@203.0.113.10

After logging in, confirm sudo works:

sudo -v
whoami
pwd

You should see deploy from whoami and a valid working directory. Keep the root session open until this succeeds.

Install PostgreSQL 16 on RHEL 9

On the VPS as root:

dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql

dnf -y install postgresql16-server postgresql16-contrib postgresql16

This adds the PGDG repository, disables the stock AppStream module, and installs the server, client tools, and contrib extensions. Check the version after installation:

/usr/pgsql-16/bin/postgres --version
/usr/pgsql-16/bin/psql --version

Initialize the database cluster:

/usr/pgsql-16/bin/postgresql-16-setup initdb

Enable the service, but do not start application traffic yet until the archive path is ready:

systemctl enable postgresql-16

Prepare archive and backup directories

Create the archive and backup directories with the PostgreSQL ownership that RHEL expects:

install -d -o postgres -g postgres -m 700 /var/lib/pgsql/16/wal_archive
install -d -o postgres -g postgres -m 700 /var/backups/postgresql

That 700 mode keeps WAL files and backup material private. On a customer database server, that is the difference between a safe backup path and a leaky one.

Configure PostgreSQL for WAL archiving

On the VPS as root:

sudo -iu postgres vi /var/lib/pgsql/16/data/postgresql.conf

Add or update the following lines. Save and exit with :wq.

listen_addresses = 'localhost'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/16/wal_archive/%f && cp %p /var/lib/pgsql/16/wal_archive/%f'
max_wal_senders = 3
wal_compression = on
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'

If you need remote access later, you can widen listen_addresses, but keep it local for this recovery drill. That keeps the tutorial focused on recovery instead of remote exposure.

Now configure client authentication for the local test role:

sudo -iu postgres vi /var/lib/pgsql/16/data/pg_hba.conf

Add this near the top of the local rules, then save and exit with :wq:

local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Check configuration syntax before restarting the service:

su - postgres -c "/usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data -t -c config_file=/var/lib/pgsql/16/data/postgresql.conf"

If the syntax is valid, there will be no error output. Start PostgreSQL:

systemctl start postgresql-16
systemctl status postgresql-16 --no-pager

You should see an active (running) service.

Create a test database and generate backup data

Create a database user and a sample table:

sudo -iu postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'StrongAppPassword-ChangeThis';"
sudo -iu postgres createdb -O appuser appdb
sudo -iu postgres psql -d appdb -c "CREATE TABLE orders (id bigserial PRIMARY KEY, note text, created_at timestamptz DEFAULT now());"
sudo -iu postgres psql -d appdb -c "INSERT INTO orders (note) VALUES ('first row'), ('second row'), ('third row');"
sudo -iu postgres psql -d appdb -c "SELECT count(*) FROM orders;"

You should see a count of 3. That gives you a known-good baseline before the backup.

Take a base backup

Back up the live cluster into the backup directory. The -X stream option includes required WAL during the backup process, which is the safest route for this test:

sudo -iu postgres pg_basebackup -D /var/backups/postgresql/base_$(date +%F_%H%M%S) -Fp -Xs -P -c fast

Record the timestamp of the backup directory. You will need it if you later test multiple recovery points.

Verify that the backup contains the expected files:

ls -lah /var/backups/postgresql
find /var/backups/postgresql -maxdepth 2 -name backup_label -o -name PG_VERSION

Force a recoverable change

Now create a deliberate mistake that you can later undo. This simulates a damaged application deployment or an accidental deletion:

sudo -iu postgres psql -d appdb -c "INSERT INTO orders (note) VALUES ('bad deploy row');"
sudo -iu postgres psql -d appdb -c "DELETE FROM orders WHERE note = 'second row';"
sudo -iu postgres psql -d appdb -c "SELECT id, note, created_at FROM orders ORDER BY id;"

At this point the database is intentionally wrong. Note the current time. That is the recovery target you will roll back before.

Stop PostgreSQL and prepare PITR restore files

On the VPS as root:

systemctl stop postgresql-16

Rename the damaged data directory out of the way. This is safer than deleting it immediately:

mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.broken.$(date +%F_%H%M%S)
install -d -o postgres -g postgres -m 700 /var/lib/pgsql/16/data

Restore the most recent base backup into the new data directory. Replace the backup path with the directory you created earlier:

rsync -aH --delete /var/backups/postgresql/base_2026-01-01_120000/ /var/lib/pgsql/16/data/

Use your actual base-backup directory name here. Do not invent a path. The command above shows the correct restore pattern.

Create the PITR signal file and recovery settings. On PostgreSQL 16, use recovery.signal:

sudo -iu postgres bash -c 'cat > /var/lib/pgsql/16/data/postgresql.auto.conf <<EOF
restore_command = ''cp /var/lib/pgsql/16/wal_archive/%f %p''
recovery_target_time = ''2026-01-01 12:30:00''
EOF
touch /var/lib/pgsql/16/data/recovery.signal
chmod 600 /var/lib/pgsql/16/data/postgresql.auto.conf
chmod 600 /var/lib/pgsql/16/data/recovery.signal'

Replace the recovery timestamp with the exact time just before the bad change. That is the point-in-time target. If the timestamp is too early, you may lose legitimate recent writes. If it is too late, the bad change will remain.

Fix ownership and SELinux labels before starting PostgreSQL again:

chown -R postgres:postgres /var/lib/pgsql/16/data
restorecon -Rv /var/lib/pgsql/16/data /var/lib/pgsql/16/wal_archive /var/backups/postgresql

Start recovery and check the logs

Bring PostgreSQL back up:

systemctl start postgresql-16
journalctl -u postgresql-16 -n 50 --no-pager

In the log output, look for recovery activity and a clean transition to a ready state. If the recovery target is valid, PostgreSQL will replay WAL and stop at the requested time.

Confirm the server is accepting connections:

sudo -iu postgres psql -c "SELECT pg_is_in_recovery();"
sudo -iu postgres psql -d appdb -c "SELECT count(*) FROM orders;"
sudo -iu postgres psql -d appdb -c "SELECT id, note, created_at FROM orders ORDER BY id;"

pg_is_in_recovery() should return f after PostgreSQL finishes recovery and starts normally. The row count should match the state before the bad change, not after it.

Enable firewall access only if you actually need it

If this server will serve application traffic from another host, open the PostgreSQL port only after you confirm your authentication rules are correct. Keep it local if the database is private.

firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload
firewall-cmd --list-services

This opens the standard PostgreSQL service port through firewalld. If your workload is private, skip this step and keep the database bound to localhost.

Make the recovery path persistent

A restore that works once is not enough. You also need to confirm the service survives a reboot and the archive location remains intact.

systemctl enable postgresql-16
reboot

After reconnecting, check the service status and the recovery state:

ssh deploy@203.0.113.10
systemctl status postgresql-16 --no-pager
sudo -iu postgres psql -c "SELECT version();"
sudo -iu postgres psql -c "SELECT pg_is_in_recovery();"

The service should come back active without manual intervention. If you used a private archive path, verify that the files are still present:

ls -lah /var/lib/pgsql/16/wal_archive
ls -lah /var/backups/postgresql

Troubleshooting the most likely failures

1. PostgreSQL fails to start after the restore.
Run:

journalctl -u postgresql-16 -xe --no-pager

If you see permission or SELinux denials, fix ownership and labels again:

chown -R postgres:postgres /var/lib/pgsql/16/data /var/lib/pgsql/16/wal_archive /var/backups/postgresql
restorecon -Rv /var/lib/pgsql/16/data /var/lib/pgsql/16/wal_archive /var/backups/postgresql

2. Recovery stops too early or too late.
Check the timestamp in postgresql.auto.conf:

grep -n 'recovery_target_time\|restore_command' /var/lib/pgsql/16/data/postgresql.auto.conf

If the time is wrong, stop PostgreSQL, adjust the timestamp, and start again. Always use a time just before the bad event.

3. WAL files are missing from the archive directory.
Check the archive command and permissions:

sudo -iu postgres psql -c "SHOW archive_mode;"
sudo -iu postgres psql -c "SHOW archive_command;"
ls -lah /var/lib/pgsql/16/wal_archive

If the archive directory is empty, the archive command likely failed. Confirm the directory exists, is writable by postgres, and the backup server has enough space.

4. SELinux blocks restore or startup.
Inspect recent denials:

ausearch -m avc -ts recent

If you see denials related to PostgreSQL paths, reapply labels with restorecon. Do not disable SELinux just to make the restore work.

FAQ

How often should I test PITR?

Test it after every major PostgreSQL change and at least once per backup cycle. A backup you never restored is only a guess.

Can I use PITR without WAL archiving?

No. PITR depends on archived WAL files plus a valid base backup. Without both, you can restore only to the backup point, not to a chosen time.

Should I keep the old data directory after a restore?

Yes, until the restored system is validated. Rename it first, then remove it later after you confirm that the recovery is clean.

Is this safe on RHEL 9 with SELinux enabled?

Yes, if you keep ownership correct and relabel restored paths with restorecon. This tutorial is written for that environment.

That is the practical recovery loop: archive WAL, capture a base backup, restore the cluster, and prove the database can land on the right minute. For Hostperl customers running business data, that process is what turns PostgreSQL into a recoverable system.