PostgreSQL Point-in-Time Recovery on a Hostperl VPS

What PostgreSQL point-in-time recovery gives you

PostgreSQL point-in-time recovery lets you roll a database back to the exact moment before a mistake, a bad deploy, or a broken import. That matters when someone deletes rows at 3 p.m. and notices at 3:20, or when an update corrupts a table while the server itself keeps running.

On a Hostperl VPS, the goal is bigger than “make backups.” You need a restore path you can verify, WAL archives you can trust, and a process that brings the database back without guesswork. If you are still deciding whether PostgreSQL suits your workload, our guide on PostgreSQL vs MySQL for VPS hosting is a good place to start.

This tutorial sets up a working recovery flow on a fresh server, then tests a restore into a separate data directory. If you want a broader recovery playbook for customer sites and migrations, the PostgreSQL backup and restore guide pairs well with this one.

Connect to the VPS and identify the operating system

On your local computer, open your first SSH session:

ssh root@203.0.113.10

Replace 203.0.113.10 with the public IP assigned to your server. This is only a documentation example.

On the VPS as root, confirm the operating system before you install anything:

cat /etc/os-release

You will use different package names and service controls on Debian or Ubuntu than on AlmaLinux or Rocky Linux. Keep the same terminal open while you work.

Create the admin user and prepare the server

For day-to-day administration, do not keep using root. Create a sudo-capable account named deploy, then keep the root session open until the new login works.

On the VPS as root, use the instructions for your distribution.

Ubuntu and Debian

apt update
apt -y upgrade
apt -y install sudo openssh-server ufw ca-certificates curl gnupg lsb-release

This refreshes packages and installs the tools needed for the rest of the setup. Next, create the user and add it to the sudo group:

adduser deploy
usermod -aG sudo deploy

Set up SSH access for the new user:

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Turn on the firewall and allow SSH before you add any database-related rules later:

ufw allow OpenSSH
ufw enable
ufw status verbose

You should see SSH allowed and the firewall active.

AlmaLinux and Rocky Linux

dnf -y update
dnf -y install sudo openssh-server firewalld curl ca-certificates policycoreutils-python-utils

Create the admin account and grant wheel access:

useradd -m -s /bin/bash deploy
passwd deploy
usermod -aG wheel deploy

Copy the SSH key and set the correct ownership:

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Start and enable the firewall, then allow SSH:

systemctl enable --now firewalld
firewall-cmd --permanent --add-service=ssh
firewall-cmd --reload
firewall-cmd --list-services

If SELinux is enforcing, leave it on. This setup works with SELinux in place because PostgreSQL stores its files under the standard data paths.

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

ssh deploy@203.0.113.10

Then test sudo on the VPS as the non-root user:

sudo -v
whoami

You should see root after sudo and no password prompt if your sudo policy is key-based. Keep the original root session open until this works.

Install PostgreSQL and verify the service

The exact package version depends on your distribution and repository setup, but the service should start cleanly and stay enabled at boot. Hostperl customers usually want this kind of setup on a Hostperl VPS because it keeps the recovery path under their control without requiring a larger dedicated machine.

Ubuntu and Debian

On the VPS as the non-root sudo user, install PostgreSQL from the default repositories:

sudo apt -y install postgresql postgresql-contrib

Check the version and service:

psql --version
systemctl status postgresql --no-pager

You should see PostgreSQL active and listening on the local socket.

AlmaLinux and Rocky Linux

On the VPS as the non-root sudo user, install PostgreSQL from the distribution repositories:

sudo dnf -y install postgresql-server postgresql-contrib

Initialize the database cluster, then enable the service:

sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Check the version and service:

psql --version
systemctl status postgresql --no-pager

If the service fails, inspect the journal before changing anything else:

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

Harden the database for recovery work

Point-in-time recovery depends on a stable PostgreSQL configuration. You need WAL archiving, predictable data paths, and a backup user with limited privileges.

On the VPS as the non-root sudo user, switch to the PostgreSQL account and create a folder for archived WAL files:

sudo -iu postgres mkdir -p /var/lib/postgresql/wal-archive

On Debian and Ubuntu, that path is fine as written. On AlmaLinux and Rocky Linux, use the same folder under /var/lib/pgsql if you want to keep it closer to the default PostgreSQL files. Make sure the PostgreSQL account owns the archive directory:

sudo chown -R postgres:postgres /var/lib/postgresql/wal-archive
sudo chmod 700 /var/lib/postgresql/wal-archive

Edit the main configuration file. The location differs by distribution:

Ubuntu and Debian

sudo nano /etc/postgresql/*/main/postgresql.conf

Replace or add these lines in the file:

listen_addresses = 'localhost'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal-archive/%f && cp %p /var/lib/postgresql/wal-archive/%f'
max_wal_senders = 3
wal_keep_size = 256MB

Save and exit, then check the config indirectly by restarting the service and confirming it comes up cleanly.

AlmaLinux and Rocky Linux

sudo nano /var/lib/pgsql/data/postgresql.conf

Use the same settings:

listen_addresses = 'localhost'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal-archive/%f && cp %p /var/lib/postgresql/wal-archive/%f'
max_wal_senders = 3
wal_keep_size = 256MB

Save and exit, then restart PostgreSQL:

sudo systemctl restart postgresql
sudo systemctl status postgresql --no-pager

For AlmaLinux and Rocky Linux, if SELinux blocks access to the archive path, inspect audit logs first:

sudo ausearch -m avc -ts recent
sudo journalctl -t setroubleshoot --no-pager

In many cases, keeping the archive path under the PostgreSQL data tree avoids that problem altogether.

Create a test database and take the base backup

Point-in-time recovery needs two pieces: a base backup and the WAL segments that follow it. Start with a small test database so you can prove the workflow safely.

On the VPS as the non-root sudo user, create a sample database and table:

sudo -iu postgres psql

At the psql prompt, run:

CREATE DATABASE appdb;
\c appdb
CREATE TABLE orders (id serial PRIMARY KEY, item text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO orders (item) VALUES ('starter-plan'), ('vps-backup');
SELECT * FROM orders;

Exit with:

\q

Now create a backup directory and take a base backup:

sudo mkdir -p /backups/postgresql/base
sudo chown postgres:postgres /backups/postgresql/base
sudo -iu postgres pg_basebackup -D /backups/postgresql/base -Fp -Xs -P -v

You should see a completed base backup with files such as PG_VERSION and base inside the directory.

For teams comparing database choices during a migration, our post on database selection for VPS hosting helps set expectations around backup and recovery work.

Simulate a mistake and restore to an earlier point

This is the part most site owners care about. You will make a harmless change, archive a WAL point, then restore into a separate directory so the original instance stays untouched.

On the VPS as the non-root sudo user, create a marker by forcing PostgreSQL activity, then record the timeline:

sudo -iu postgres psql -d appdb -c "INSERT INTO orders (item) VALUES ('bad-import');"
sudo -iu postgres psql -d appdb -c "SELECT now();"

Now stop PostgreSQL before the restore test:

sudo systemctl stop postgresql

Move the current data directory out of the way. This is safe only because you are testing on a fresh server and have a base backup.

Ubuntu and Debian

sudo mv /var/lib/postgresql/*/main /var/lib/postgresql/*/main.bak

If your shell does not expand the wildcard the way you expect, check the actual version path with ls /var/lib/postgresql first.

AlmaLinux and Rocky Linux

sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.bak

Restore the base backup into the live data directory:

Ubuntu and Debian

sudo cp -a /backups/postgresql/base /var/lib/postgresql/15/main

Replace 15 with the major version you installed. Check ls /var/lib/postgresql if needed.

AlmaLinux and Rocky Linux

sudo cp -a /backups/postgresql/base /var/lib/pgsql/data

Create a recovery signal that tells PostgreSQL to stop at a target time. The file name depends on the version, but for modern releases use recovery.signal and a restore command in postgresql.auto.conf.

On the VPS as the non-root sudo user, write the recovery settings:

Ubuntu and Debian

sudo -iu postgres bash -c 'cat > /var/lib/postgresql/15/main/postgresql.auto.conf <<EOF
restore_command = ''cp /var/lib/postgresql/wal-archive/%f %p''
recovery_target_time = ''2026-01-01 12:00:00+00''
EOF
touch /var/lib/postgresql/15/main/recovery.signal'

AlmaLinux and Rocky Linux

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

Because this is a controlled tutorial, the time value is only an example. In a real incident, you would set the target to the timestamp just before the bad change.

Start PostgreSQL again:

sudo systemctl start postgresql
sudo systemctl status postgresql --no-pager

Then confirm that the data reflects the recovered state, not the bad import:

sudo -iu postgres psql -d appdb -c "SELECT * FROM orders ORDER BY id;"

If the restore worked, you should see the original rows and stop before the inserted bad-import row, depending on the recovery target you chose.

Check firewall access and service persistence

PostgreSQL should stay private on a single-server setup unless you have a specific replication or remote administration requirement. For most Hostperl customers, keeping the service bound to localhost reduces exposure and keeps the recovery path simple.

On the VPS as the non-root sudo user, confirm the listener and the active port:

ss -lntp | grep 5432
systemctl is-enabled postgresql

You should see PostgreSQL listening locally and enabled at boot. Reboot the server if you want a full persistence check:

sudo reboot

After the server returns, SSH back in and run:

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

That confirms the service survived the reboot and the database is still readable.

Common problems and fast fixes

• PostgreSQL fails to start after editing config. Run sudo journalctl -u postgresql -n 50 --no-pager. A bad path or quoting error in archive_command is the usual cause. Fix the file, then restart the service.
• WAL files do not appear in the archive directory. Check sudo -iu postgres psql -c "SHOW archive_mode; SHOW archive_command;". If the command is disabled or the path is wrong, correct postgresql.conf and restart PostgreSQL.
• SELinux blocks restore or archive actions on AlmaLinux or Rocky Linux. Run sudo ausearch -m avc -ts recent. If the archive path is outside the allowed location, move it under the PostgreSQL data tree or label it appropriately.
• The restored database still shows the bad row. Your recovery target was too late. Check the actual event time with sudo -iu postgres psql -d appdb -c "SELECT now();" and choose an earlier recovery timestamp.

FAQ

How often should I take a PostgreSQL base backup?

For most small and medium sites, take a fresh base backup daily or after major schema changes, then keep WAL archiving running continuously.

Can I use this setup for live production recovery?

Yes, but test it on a staging VPS first. The restore target, archive path, and permissions should be proven before you rely on them during an outage.

Do I need a separate disk for WAL archives?

It is safer. Separate storage keeps a full data disk from crowding out recovery files, and it makes retention policies easier to manage.

Should I expose PostgreSQL on the public internet?

Not unless you have a clear reason and strict network controls. Most hosting customers should keep PostgreSQL on localhost and use an application server or tunnel for access.

What should I test after a reboot?

Check the service status, confirm the listener on port 5432, and run a simple read query against your test database.

For ongoing database workloads, Hostperl’s managed VPS hosting gives you a practical place to keep PostgreSQL recovery, staging restores, and migration testing under one roof. If you are planning a broader site move, pair this with your panel or WordPress migration workflow and test the restore before cutover.