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.10203.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-releaseYou 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 updateThen 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 firewalldVerify that PostgreSQL is available.
psql --versionExpected 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 initdbThis 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-16Check that the service is running.
systemctl status postgresql-16 --no-pagerYou 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 pgbackupCreate the backup directory and lock down ownership.
mkdir -p /var/backups/postgresql /var/lib/pgbackup/binchown -R pgbackup:pgbackup /var/backups/postgresql /var/lib/pgbackupchmod 750 /var/backups/postgresql /var/lib/pgbackupThe 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.confAdd 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/walchown -R postgres:postgres /var/backups/postgresql/walchmod 750 /var/backups/postgresql/walTest 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-16journalctl -u postgresql-16 -n 30 --no-pagerIf 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 firewalldConfirm the firewall is active.
firewall-cmd --stateBecause 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 psqlAt 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/basesudo -iu postgres pg_basebackup -D /var/backups/postgresql/base -Fp -Xs -P -RThe -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/baseYou 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 -uSave 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 | tailSeeing 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-16mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.failed.$(date +%F-%H%M%S)mkdir -p /var/lib/pgsql/16/datachown postgres:postgres /var/lib/pgsql/16/datachmod 700 /var/lib/pgsql/16/dataRestore 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.confAdd 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.signalThe file can be empty. Then add a recovery target directive:
sudo -iu postgres nano /var/lib/pgsql/16/data/postgresql.auto.confAppend 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-16Check the journal for successful recovery messages.
journalctl -u postgresql-16 -n 50 --no-pagerIf 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 5432Because 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 -eAdd 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>&1Save 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-16rm -rf /var/lib/pgsql/16/datamv /var/lib/pgsql/16/data.failed.* /var/lib/pgsql/16/datachown -R postgres:postgres /var/lib/pgsql/16/datachmod 700 /var/lib/pgsql/16/datasystemctl start postgresql-16That 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.
