IPv4 & IPv6 Leasing - Any RIR, Any LocationOrder Now
Hostperl

PostgreSQL Backups and Restore Validation on RHEL 9

By Raman Kumar

Share:

Updated on Nov 22, 2026

PostgreSQL Backups and Restore Validation on RHEL 9

What this tutorial does

This guide shows you how to build and test a PostgreSQL backup workflow on RHEL 9 using physical backups, WAL archiving, and a restore drill you can actually trust. The goal is not just to create files under /var/backups; it is to confirm that you can recover a database after a failure, replay transactions to a chosen point in time, and verify the restored data before you cut traffic back over.

If you run PostgreSQL for a live application on a Hostperl VPS or a dedicated server, this is the level of backup discipline you want before a migration, a major release, or a storage incident. For broader planning around recovery testing, see PostgreSQL backups and PITR recovery in 2026 and RHEL cloud VPS sizing for production workloads.

Before you start

This tutorial is written for RHEL 9, and the same steps are suitable for Oracle Linux 9 and compatible RHEL 9 clones with minor package naming differences. PostgreSQL packages, SELinux behavior, and firewalld are the reason this guide stays on the RHEL family instead of mixing in another distribution. You need root access to the server, a second terminal for verification, and enough free disk space for a full backup plus WAL files.

We will use these example values throughout: PostgreSQL 16, backup directory /var/backups/postgresql, data directory /var/lib/pgsql/16/data, and a backup user named pgbackup. Replace only what matches your environment.

Connect and confirm the operating system

On your local computer

ssh root@203.0.113.10

203.0.113.10 is a reserved documentation address. Replace it with the public IP assigned to your server by your hosting provider.

After you log in, confirm the OS version before you touch packages or services.

On the VPS as root

cat /etc/os-release

You should see RHEL 9 or a compatible derivative. If you see a different OS, stop here and use a matching guide for that platform.

Update the server and install PostgreSQL tools

Start with a current package set. This lowers the chance of dependency issues during the backup drill.

On the VPS as root

dnf -y update

Then install PostgreSQL server packages and the client tools you need for backup and restore validation.

dnf -y install postgresql16-server postgresql16 postgresql16-contrib rsync policycoreutils-python-utils firewalld

Verify that PostgreSQL is available.

psql --version

Expected output should show PostgreSQL 16.x. If psql is missing, the package installation did not complete cleanly.

Initialize PostgreSQL and start the service

RHEL packages do not create a database cluster automatically. Initialize the data directory first, then start PostgreSQL.

On the VPS as root

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

This creates the database cluster under /var/lib/pgsql/16/data. If you already have a live cluster, do not run this on top of it.

systemctl enable --now postgresql-16

Check that the service is running.

systemctl status postgresql-16 --no-pager

You want an active (running) status. If it fails, the logs usually show permissions or data-directory problems.

Create a dedicated backup account

Run backups as a restricted account rather than as root. That keeps your backup path cleaner and reduces the blast radius if the backup job is compromised.

On the VPS as root

useradd -r -m -d /var/lib/pgbackup -s /sbin/nologin pgbackup

Create the backup directory and lock down ownership.

mkdir -p /var/backups/postgresql /var/lib/pgbackup/bin
chown -R pgbackup:pgbackup /var/backups/postgresql /var/lib/pgbackup
chmod 750 /var/backups/postgresql /var/lib/pgbackup

The backup account will own the archive location, but PostgreSQL still writes the WAL files. Next, configure PostgreSQL so it can support PITR.

Enable WAL archiving for point-in-time recovery

Open the PostgreSQL configuration file and set a backup-friendly archive policy. We will use a simple shell copy command for the archive destination, which is enough for a single-server recovery workflow.

On the VPS as root

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

Add or update these lines in the file:

listen_addresses = 'localhost'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/backups/postgresql/wal/%f && cp %p /var/backups/postgresql/wal/%f'
max_wal_senders = 3
wal_compression = on
checkpoint_timeout = '15min'
max_wal_size = '2GB'

Save and exit the editor. If you use nano, press Ctrl+O, Enter, then Ctrl+X.

Create the WAL archive directory and set safe permissions.

mkdir -p /var/backups/postgresql/wal
chown -R postgres:postgres /var/backups/postgresql/wal
chmod 750 /var/backups/postgresql/wal

Test the configuration syntax by restarting PostgreSQL only after you have checked the file contents. PostgreSQL does not have an Nginx-style syntax test, so the restart itself is the practical validation step.

systemctl restart postgresql-16
journalctl -u postgresql-16 -n 30 --no-pager

If the service starts cleanly, you should see normal startup messages and no configuration errors.

Lock down access and open only what you need

RHEL uses firewalld, and PostgreSQL should not be exposed publicly unless you have a specific remote-access requirement. This guide keeps PostgreSQL local to the server, which is safer for a backup validation drill.

On the VPS as root

systemctl enable --now firewalld

Confirm the firewall is active.

firewall-cmd --state

Because PostgreSQL listens on localhost in this tutorial, you do not need to open port 5432. If you later decide to allow remote access from a management network, add the rule first, test it, and only then change the listen address.

Create a sample database and data set

Use a small but realistic database so the restore test proves more than an empty cluster. We will create one sample database and a table with a few rows.

On the VPS as root

sudo -iu postgres psql

At the psql prompt, run:

CREATE DATABASE appdb;
\c appdb
CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_name text NOT NULL,
  total numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer_name, total) VALUES
('Ava', 149.90),
('Noah', 89.50),
('Mia', 240.00);
SELECT count(*) FROM orders;

Exit psql with \q. You should see a row count of 3. This becomes your restore checkpoint.

Take a full physical backup

Now capture a base backup and store it under the backup directory. The backup needs to be owned by the backup account and readable only by trusted users.

On the VPS as root

sudo -iu postgres mkdir -p /var/backups/postgresql/base
sudo -iu postgres pg_basebackup -D /var/backups/postgresql/base -Fp -Xs -P -R

The -R flag writes a standby signal file and connection settings. For a recovery drill, that is fine because we will use the backup as the basis for a restore, not as a live replica.

Check that files exist and the backup completed.

ls -lh /var/backups/postgresql/base

You should see a full cluster copy including PG_VERSION and data files.

Record a restore point and archive WAL

To test point-in-time recovery, create a restore marker, make another change, and then restore to the moment before that change. First, note the current time.

On the VPS as root

date -u

Save that timestamp. Then add one more row so you have a change to roll back.

sudo -iu postgres psql -d appdb -c "INSERT INTO orders (customer_name, total) VALUES ('Liam', 510.25);"

Now force PostgreSQL to write a fresh WAL segment by waiting for a checkpoint or generating a little activity. For a simple drill, this command is enough to create a clear recovery boundary.

sudo -iu postgres psql -d appdb -c "CHECKPOINT;"

Make sure the WAL archive directory contains files.

find /var/backups/postgresql/wal -type f | tail

Seeing WAL files confirms that archive_command is working. If the directory stays empty, the archive path or permissions need attention before you can trust PITR.

Simulate a failure and restore the backup

This is the part many teams skip. Do not. A backup that never gets restored is an assumption, not a recovery plan.

Stop PostgreSQL and move the current data directory aside so you do not overwrite the live copy.

On the VPS as root

systemctl stop postgresql-16
mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.failed.$(date +%F-%H%M%S)
mkdir -p /var/lib/pgsql/16/data
chown postgres:postgres /var/lib/pgsql/16/data
chmod 700 /var/lib/pgsql/16/data

Restore the base backup into the new data directory.

sudo -iu postgres rsync -a /var/backups/postgresql/base/ /var/lib/pgsql/16/data/

Create a recovery configuration so PostgreSQL replays WAL files from the archive directory.

On the VPS as root

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

Add this line:

restore_command = 'cp /var/backups/postgresql/wal/%f %p'

Save and exit the editor.

To recover to a point in time before the last inserted row, create a recovery target file. Use the timestamp you saved earlier and set it to a moment just before the second insert.

sudo -iu postgres nano /var/lib/pgsql/16/data/recovery.signal

The file can be empty. Then add a recovery target directive:

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

Append a line like this, replacing the time with your own target:

recovery_target_time = '2026-01-01 12:00:00+00'

Start PostgreSQL again.

systemctl start postgresql-16

Check the journal for successful recovery messages.

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

If PITR works, PostgreSQL should stop at your chosen recovery point and then come online normally.

Validate the restored data

Open psql and confirm the row counts and the recovered state. The row added after your target point should be absent if the recovery point was set correctly.

On the VPS as root

sudo -iu postgres psql -d appdb -c "SELECT id, customer_name, total, created_at FROM orders ORDER BY id;"

You should see the first three rows, but not the later test insert if your recovery target predates it. That is the main proof that your backup and WAL archive strategy works.

Check that PostgreSQL is listening only where you intended.

ss -ltnp | grep 5432

Because we set listen_addresses = 'localhost', the service should only bind to loopback.

Make the backup routine repeatable

At this point, you have a one-off backup and restore drill. Production systems need repetition. Turn the backup capture into a scheduled job so you do not rely on memory during an incident.

On the VPS as root

crontab -u postgres -e

Add a nightly backup job like this:

0 2 * * * /usr/pgsql-16/bin/pg_basebackup -D /var/backups/postgresql/base-$(date +\%F) -Fp -Xs -P -R >> /var/backups/postgresql/backup.log 2>&1

Save and exit the editor. This creates dated backups that are easier to rotate and audit. If you use a retention policy, delete only after you have verified a newer restore point.

Rollback and recovery path

If the restore drill exposed a problem, the safest rollback is to return to the last known good data directory and service state. Stop PostgreSQL, move the failed restore aside, and restore the original data directory you moved earlier.

On the VPS as root

systemctl stop postgresql-16
rm -rf /var/lib/pgsql/16/data
mv /var/lib/pgsql/16/data.failed.* /var/lib/pgsql/16/data
chown -R postgres:postgres /var/lib/pgsql/16/data
chmod 700 /var/lib/pgsql/16/data
systemctl start postgresql-16

That returns the server to the pre-test state. Do not forget to remove recovery settings if you are reverting the cluster back to its original operational mode.

Troubleshooting the most likely failures

1) PostgreSQL will not start after the config change
Diagnostic command: journalctl -u postgresql-16 -n 50 --no-pager
Expected clue: messages about a bad archive_command, permission denied, or a missing directory.
Fix: confirm the WAL directory exists, check ownership with ls -ld /var/backups/postgresql /var/backups/postgresql/wal, and correct permissions with chown -R postgres:postgres /var/backups/postgresql.

2) WAL files do not appear in the archive directory
Diagnostic command: sudo -iu postgres psql -c "SELECT * FROM pg_stat_archiver;"
Expected clue: failed_count increases or last_failed_wal is populated.
Fix: verify the archive_command path in postgresql.conf, then restart PostgreSQL and trigger another checkpoint.

3) Restore finishes, but the database is empty
Diagnostic command: sudo -iu postgres psql -d appdb -c "SELECT count(*) FROM orders;"
Expected clue: the restored data set does not match the source backup.
Fix: confirm that pg_basebackup completed without errors and that you restored the correct backup directory with rsync -a.

4) SELinux blocks archive or restore paths
Diagnostic command: ausearch -m avc -ts recent
Expected clue: AVC denials for PostgreSQL accessing backup directories.
Fix: keep backups under standard paths owned by postgres. If you must use a custom path, label it with semanage fcontext -a -t postgresql_db_t '/custom/path(/.*)?' followed by restorecon -Rv /custom/path.

Hostperl can host the PostgreSQL workloads that need real recovery discipline, not just a database service that starts once and is forgotten. If you want a platform sized for backups, WAL retention, and restore drills, look at Hostperl VPS hosting or Hostperl dedicated server hosting.

For teams running customer data, that extra headroom matters during migrations, retention windows, and incident recovery.

FAQ

How often should I test PostgreSQL backups?
At least monthly for smaller systems, and after every major schema change. For busy production environments, tie the test to your release calendar.

Is pg_basebackup enough on its own?
No. It gives you a full physical backup, but WAL archiving is what makes point-in-time recovery possible.

Should I store backups on the same server?
Only for a short-term recovery workflow. For real protection, copy validated backups to separate storage or another Hostperl server.

What should I check after a restore?
Check row counts, key application queries, service status, and logs. Then confirm the app can reconnect and write new data.

Can I use this on Oracle Linux 9?
Yes, with the same general RHEL-family flow. Package names can vary slightly, so confirm the PostgreSQL repository package for your exact OS build before installing.