PostgreSQL WAL Archiving on AlmaLinux 9 for Recovery

Why WAL archiving matters for production recovery

PostgreSQL WAL archiving gives you a second recovery path beyond a plain backup. If a migration fails, a table gets dropped, or corruption shows up between nightly backups, you can restore the latest base backup and replay WAL files to the exact minute you need.

This tutorial uses AlmaLinux 9 because it fits the RHEL-compatible environments many hosts still run. You will do more than install PostgreSQL. You will configure archiving, verify it, test a point-in-time recovery on the same server, and keep a rollback path ready.

If you are choosing the underlying VPS for database workloads, Hostperl’s VPS hosting is a practical fit for smaller production databases. For larger datasets, replication chains, or busy ecommerce back ends, dedicated server hosting gives you more memory, local NVMe capacity, and recovery headroom.

What you will build

You will set up a PostgreSQL server on AlmaLinux 9 with:

• a non-root administrator account
• PostgreSQL installed from the distribution repository
• a WAL archive directory with tight permissions
• continuous WAL shipping to that archive
• a base backup taken with pg_basebackup
• a restore test that replays WAL to a chosen recovery target time
• verification commands and rollback steps you can repeat later

The example server IP is 203.0.113.10. Replace it with your own public IP from Hostperl or another provider.

Connect to the server and confirm the OS

On your local computer, open an SSH session to the server.

ssh root@203.0.113.10

That address is reserved for documentation. Replace it with the real public IP assigned to your VPS.

If your provider gave you a default non-root account instead, use that same IP with your provider’s username.

ssh deploy@203.0.113.10

On the VPS as root, detect the operating system before you change anything.

cat /etc/os-release

You should see AlmaLinux 9 or a compatible RHEL-derived release. This tutorial uses dnf, systemd, and firewalld commands that match that family.

Create the admin account and SSH access

Keep the root session open until the new login works. Do not disable root access yet.

On the VPS as root, create a sudo-capable account named deploy.

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

Set a strong password when prompted. The wheel group is the standard sudo path on AlmaLinux and Rocky Linux.

Now create the SSH directory and install your public key. Replace the key string with your own local public key content.

mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
cat > /home/deploy/.ssh/authorized_keys <<'EOF'
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIExampleKeyReplaceThisWithYourOwnKey
EOF
chmod 600 /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh

The permissions matter. SSH will ignore key files that are too open.

On your local computer, open a second terminal and test the new login.

ssh deploy@203.0.113.10

Then confirm sudo works.

sudo -v

If that succeeds, keep using the deploy session for the rest of the work.

Install PostgreSQL and prepare the archive path

On the VPS as the non-root sudo user, install PostgreSQL and the client tools.

sudo dnf update -y
sudo dnf install -y postgresql-server postgresql-contrib postgresql

On AlmaLinux 9, the distribution package typically provides PostgreSQL 15 or the currently supported stream available through the repositories. Check the installed client version before continuing.

psql --version

Initialize the database cluster if it is not already initialized by the package post-install script.

sudo postgresql-setup --initdb

Create a dedicated archive directory. This keeps WAL files separate from the live database area.

sudo mkdir -p /var/lib/pgsql/wal_archive
sudo chown -R postgres:postgres /var/lib/pgsql/wal_archive
sudo chmod 700 /var/lib/pgsql/wal_archive

A restrictive mode matters here because WAL files can contain recoverable business data.

Enable PostgreSQL WAL archiving

Edit the main PostgreSQL configuration file. The exact version number in the path may vary slightly, so confirm it first.

sudo -iu postgres bash -c 'pwd; ls /var/lib/pgsql/data'

Open postgresql.conf.

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

Add or update these settings near the archive and WAL section:

listen_addresses = 'localhost'
port = 5432
wal_level = replica
archive_mode = on
archive_timeout = 60
archive_command = 'test ! -f /var/lib/pgsql/wal_archive/%f && cp %p /var/lib/pgsql/wal_archive/%f'
max_wal_senders = 3
wal_compression = on

Save and exit the editor. These settings enable archiving, force periodic archive switches, and compress WAL records to reduce storage pressure.

Now set archive directory permissions in a way PostgreSQL can use safely. The service runs as the postgres user, so the path must stay writable by that account.

Before you reload the service, check the configuration syntax.

sudo -iu postgres /usr/pgsql/bin/postgres -D /var/lib/pgsql/data -C wal_level

That command should return replica once PostgreSQL reads the configuration cleanly. If your package layout uses a different binary path, use which postgres first and substitute the result.

Start PostgreSQL and open the firewall

On the VPS as the non-root sudo user, start and enable the database service.

sudo systemctl enable --now postgresql

Check that the service is active.

sudo systemctl status postgresql --no-pager

Open PostgreSQL only if you need remote database access. For a single-server setup used for backup and recovery testing, loopback access is enough. If you do need remote access, add the rule before changing access controls.

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

If you are using only local access, you can skip the firewall port and keep PostgreSQL bound to localhost.

Check that PostgreSQL is listening.

sudo ss -lntp | grep 5432

Create a test database and confirm WAL generation

To make archiving visible, create a small test database and table.

sudo -iu postgres psql <<'SQL'
CREATE DATABASE recoverytest;
\c recoverytest
CREATE TABLE events (
  id bigserial PRIMARY KEY,
  note text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO events (note) VALUES ('initial row'), ('second row');
SQL

Confirm the data exists.

sudo -iu postgres psql -d recoverytest -c "SELECT count(*) FROM events;"

Next, force a WAL switch so the archive directory receives files quickly.

sudo -iu postgres psql -c "SELECT pg_switch_wal();"
ls -lh /var/lib/pgsql/wal_archive

You should see at least one WAL segment appear. That is your first sign that archiving is working.

Take a base backup for recovery

WAL archiving alone is not enough. You need a base backup plus archived WAL to restore from a specific point in time.

Run the base backup into a new directory owned by postgres.

sudo mkdir -p /var/lib/pgsql/basebackups
sudo chown postgres:postgres /var/lib/pgsql/basebackups
sudo -iu postgres pg_basebackup -D /var/lib/pgsql/basebackups/base_$(date +%F_%H%M) -Fp -Xs -P -R

The -R option writes standby settings. For a recovery-only workflow, that is useful because it records connection details and confirms the backup was taken in a PostgreSQL-aware way.

List the backup directory to confirm files were created.

sudo ls -lah /var/lib/pgsql/basebackups

Test point-in-time recovery safely

This is the part many administrators skip. Do not. A backup that has never been restored is only a hope.

First, record the current time, then create another row and remember that timestamp. You will recover to a time just before that insert.

date -u
sudo -iu postgres psql -d recoverytest -c "INSERT INTO events (note) VALUES ('row before recovery point');"
sudo -iu postgres psql -d recoverytest -c "SELECT now();"

Stop PostgreSQL before the recovery test. This is a safe local test because you are restoring from the base backup rather than destroying production data in place.

sudo systemctl stop postgresql

Move the current data directory aside so you can roll back if needed.

sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.before-recovery

Restore the base backup into the original location.

sudo cp -a /var/lib/pgsql/basebackups/base_* /var/lib/pgsql/data
sudo chown -R postgres:postgres /var/lib/pgsql/data

Create a recovery signal and tell PostgreSQL where to find archived WAL. For PostgreSQL 15 and later, recovery configuration is driven by postgresql.auto.conf and a signal file.

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

Replace the recovery target time with a timestamp just before your test insert. In a real incident, you would point to the exact minute before the bad change.

Start PostgreSQL again.

sudo systemctl start postgresql

Watch the logs for recovery progress.

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

When recovery completes, connect and check the row count. If the target time was set correctly, the row inserted after the recovery point should be absent.

sudo -iu postgres psql -d recoverytest -c "SELECT count(*) FROM events;"

If you want a stricter check, query the table contents.

sudo -iu postgres psql -d recoverytest -c "SELECT id, note, created_at FROM events ORDER BY id;"

Return the server to normal operation

Once the recovery test is complete, put PostgreSQL back into normal service by removing the recovery signal and restoring the original data directory if needed.

sudo systemctl stop postgresql
sudo rm -f /var/lib/pgsql/data/recovery.signal
sudo rm -f /var/lib/pgsql/data/postgresql.auto.conf
sudo rm -rf /var/lib/pgsql/data
sudo mv /var/lib/pgsql/data.before-recovery /var/lib/pgsql/data
sudo chown -R postgres:postgres /var/lib/pgsql/data
sudo systemctl start postgresql

Check that the service comes up cleanly and that your original data is back in place.

sudo systemctl status postgresql --no-pager
sudo -iu postgres psql -d recoverytest -c "SELECT count(*) FROM events;"

Operational checks you should keep using

A recovery workflow is only useful if you check it regularly. These commands help you spot problems before they become incidents.

• Archive growth: sudo ls -lh /var/lib/pgsql/wal_archive
• Service health: sudo systemctl status postgresql --no-pager
• Recent logs: sudo journalctl -u postgresql -n 100 --no-pager
• Listening port: sudo ss -lntp | grep 5432
• Archive replay test: repeat the base-backup-and-restore flow on a schedule

For deeper database support planning, Hostperl’s PostgreSQL logical replication tutorial is useful when you need live read scaling or delayed failover. If you are comparing restoration strategies for a growing application, the replication failover planning guide gives a solid operational baseline.

Troubleshooting common failures

Archive files are not appearing

Run the journal and check the archive directory permissions.

sudo journalctl -u postgresql -n 100 --no-pager
sudo ls -ld /var/lib/pgsql/wal_archive

If the logs show permission errors, fix ownership and mode, then generate another WAL switch.

sudo chown -R postgres:postgres /var/lib/pgsql/wal_archive
sudo chmod 700 /var/lib/pgsql/wal_archive
sudo -iu postgres psql -c "SELECT pg_switch_wal();"

PostgreSQL fails to start after editing configuration

Check the syntax and the archive command string. A quoting mistake is the usual cause.

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

If you see configuration errors, correct postgresql.conf and restart the service after a clean syntax check.

sudo systemctl restart postgresql

Recovery never reaches the target time

Confirm that WAL segments exist for the required period and that restore_command points to the correct archive path.

sudo ls -lh /var/lib/pgsql/wal_archive
sudo cat /var/lib/pgsql/data/postgresql.auto.conf

If the target time is too old for the available WAL files, restore from a newer base backup or keep more WAL on disk or object storage.

Final verification checklist

Before you call the job complete, confirm all three conditions below:

1. PostgreSQL starts cleanly with systemctl status postgresql.
2. WAL files are landing in /var/lib/pgsql/wal_archive.
3. A restored copy can replay to a chosen point in time and return expected data.

That is the practical value of PostgreSQL WAL archiving: it turns backups from a static file into a recovery process you can trust.