PostgreSQL PITR on Ubuntu Server 24.04 for VPS Recovery

Why PostgreSQL PITR matters on a live VPS

PostgreSQL PITR gives you point-in-time recovery. If a bad migration, dropped table, or application bug corrupts data, you can roll back to the minute before the mistake. On a production VPS, that usually means a short incident instead of a long recovery.

This tutorial shows you how to build PostgreSQL PITR on Hostperl VPS hosting using Ubuntu Server 24.04, WAL archiving, a base backup, and a verified restore drill. The same recovery pattern works well for customer sites, agency clients, and SaaS workloads that need predictable recovery.

For related operational context, see PostgreSQL logical backups on Debian 12 with pg_dump for export-style backups, and backup strategy for VPS hosting for broader planning.

What you will build

You will set up one Ubuntu Server 24.04 VPS as the PostgreSQL primary, enable WAL archiving, create a backup directory, and test a recovery into a separate restore directory. The restore uses PostgreSQL 16, which ships with Ubuntu 24.04 by default.

• Primary server: 203.0.113.10 as the documentation IP for your VPS
• Database version: PostgreSQL 16
• Backup target: /var/backups/postgresql
• Application database example: appdb
• Application user example: appuser

Replace 203.0.113.10 with your own public server IP. Keep your original root SSH session open until the new sudo user is confirmed working.

Connect to the VPS and confirm the OS

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 Hostperl server. If your provider gives you a default non-root account, connect with that account first, then continue the same way.

On the VPS as root

cat /etc/os-release

This confirms you are on Ubuntu Server 24.04 before you use the Ubuntu-specific commands below.

Create a sudo user and lock down SSH access later

Use a non-root admin before you make any SSH changes. That keeps you from locking yourself out halfway through setup.

On the VPS as root

adduser deploy

Create a strong password when prompted. Then add the account to sudo:

usermod -aG sudo deploy

Prepare the SSH directory and copy your key from the root account. Replace the public key file path with your own if needed.

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

Open a second terminal on your local computer and test the new login before changing root access.

On your local computer

ssh deploy@203.0.113.10

Then confirm sudo works:

On the VPS as the non-root sudo user

sudo -v

If that succeeds, keep this session open. Only then should you consider disabling password logins or root SSH later in your maintenance window.

Update Ubuntu and install PostgreSQL 16

Refresh packages first. That lowers the chance of dependency errors during the database install.

On the VPS as the non-root sudo user

sudo apt update
sudo apt -y full-upgrade
sudo apt -y install postgresql-16 postgresql-client-16 rsync

Check that PostgreSQL is active:

systemctl status postgresql --no-pager

Ubuntu uses the service name postgresql, even though the cluster is versioned underneath it.

Create a base database and make the first backup

Next, create a small example workload so the restore test has real data. Use the local PostgreSQL superuser to create a database and role.

On the VPS as the non-root sudo user

sudo -u postgres psql

At the psql prompt, run:

CREATE USER appuser WITH PASSWORD 'UseARealStrongPasswordHere';
CREATE DATABASE appdb OWNER appuser;
\q

Now create a sample table and a few rows.

sudo -u postgres psql -d appdb -c "CREATE TABLE orders (id bigserial PRIMARY KEY, order_ref text NOT NULL, created_at timestamptz NOT NULL DEFAULT now()); INSERT INTO orders (order_ref) VALUES ('ORD-1001'), ('ORD-1002'), ('ORD-1003'); SELECT * FROM orders;"

That output confirms the database contains data you can recover later.

Enable WAL archiving for PostgreSQL PITR

WAL files are the transaction trail that makes point-in-time recovery possible. PostgreSQL must archive them continuously to a directory you keep outside the live data path.

Check the active data directory first:

On the VPS as the non-root sudo user

sudo -u postgres psql -tAc "show data_directory;"

On Ubuntu 24.04, it usually returns /var/lib/postgresql/16/main.

Create the archive directory and set ownership:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql/wal

Edit the main PostgreSQL configuration.

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

Add or replace these lines inside the file:

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_keep_size = 512MB

Save and exit the editor. Then restart the cluster service and check that it comes back cleanly.

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

If PostgreSQL starts cleanly, WAL archiving is active. Confirm the setting values:

sudo -u postgres psql -tAc "show wal_level; show archive_mode; show archive_command;"

You should see replica, on, and the copy command you configured.

Take a base backup for recovery

A base backup is the starting snapshot for PITR. Without it, archived WAL alone is not enough.

On the VPS as the non-root sudo user

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql/basebackups

sudo -u postgres pg_basebackup -D /var/backups/postgresql/basebackups/base-$(date +%F-%H%M) -Fp -Xs -P -R

The -R flag writes recovery connection info into the backup set. The command should finish with a progress report and no errors.

Check that files exist:

sudo find /var/backups/postgresql/basebackups -maxdepth 2 -type f | head

You should see backup files and a backup_label entry.

Test recovery to a point before a bad change

Now simulate a real incident. Make a destructive change, then restore to the moment just before it.

Record the current time first:

date -u +%Y-%m-%dT%H:%M:%SZ

Then add a bad update:

sudo -u postgres psql -d appdb -c "DELETE FROM orders; INSERT INTO orders (order_ref) VALUES ('BROKEN-CHANGE'); SELECT * FROM orders;"

Stop PostgreSQL before restoring. This is destructive, so make sure you are using the backup copy path, not the live cluster.

sudo systemctl stop postgresql

Move the live data directory out of the way and restore the latest base backup into a new recovery directory. Replace the backup timestamp directory with the one you created on your server.

sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.pre-pitr
sudo rsync -a /var/backups/postgresql/basebackups/base-2026-01-01-1200/ /var/lib/postgresql/16/main/

If you do not have that exact timestamp, use the backup directory created by your own pg_basebackup command. The restore should preserve ownership and permissions.

Create the recovery instruction file in the restored data directory:

sudo nano /var/lib/postgresql/16/main/postgresql.auto.conf

Add this content, replacing the recovery target time with the timestamp you recorded before the bad change:

restore_command = 'cp /var/backups/postgresql/wal/%f %p'
recovery_target_time = '2026-01-01 12:34:56 UTC'
recovery_target_action = 'promote'

Save and exit, then restart PostgreSQL:

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

Look for messages that show PostgreSQL entered recovery and then promoted the cluster. If you see a restore failure, the WAL archive path or target time is usually wrong.

Verify the recovered data

Run a direct check against the restored database. You should see the rows that existed before the destructive change, not the broken insert.

On the VPS as the non-root sudo user

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

If PITR worked, the table contents should match the pre-incident state. That is your main success check.

Also confirm the server is listening again:

ss -ltnp | grep 5432

You should see PostgreSQL bound to port 5432 on localhost or the expected interface.

Open the firewall safely on Ubuntu

If your database only needs local access, keep port 5432 closed to the public internet. If you must allow trusted application hosts, add the rule before any stricter network change.

On the VPS as the non-root sudo user

sudo ufw allow OpenSSH
sudo ufw allow from 203.0.113.20 to any port 5432 proto tcp
sudo ufw enable
sudo ufw status verbose

Replace 203.0.113.20 with the app server or jump host that truly needs database access. Do not expose PostgreSQL broadly unless you have a clear network control and authentication plan.

Common failures and how to diagnose them

WAL files are not appearing. Check the archive command first.

sudo -u postgres psql -tAc "show archive_command;"
sudo ls -l /var/backups/postgresql/wal | tail

If the directory stays empty, fix ownership or path permissions, then restart PostgreSQL.

Recovery stops with missing WAL errors. Inspect the exact missing file name.

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

The clue is usually a file name that never reached the archive directory. In that case, restore from a newer base backup or keep a longer WAL retention window.

Service fails after config edits. Test the edited files and read the last log lines.

sudo journalctl -u postgresql -xe --no-pager

A typo in postgresql.conf or the recovery file usually shows up here immediately.

Rollback path if the restore is not clean

If the PITR restore does not complete, stop PostgreSQL and return to the pre-restore data directory you saved earlier.

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main
sudo mv /var/lib/postgresql/16/main.pre-pitr /var/lib/postgresql/16/main
sudo systemctl start postgresql

That puts the original cluster back online so your application can continue while you fix the recovery set. This is why you keep a pre-change copy of the live data directory during drills.

Final verification from server and client

From the server, confirm the service survived a restart:

sudo systemctl restart postgresql
sudo systemctl is-active postgresql

Then check the database contents again:

sudo -u postgres psql -d appdb -c "SELECT count(*) FROM orders;"

From a client that has database access, connect with the application account and run a simple read test:

psql -h 203.0.113.10 -U appuser -d appdb -c "SELECT now();"

Replace 203.0.113.10 with your real VPS IP if your database is reachable over the network. A successful timestamp response confirms the restored database is usable from outside the server too.

FAQ

How often should I run PostgreSQL PITR backups?

Run a fresh base backup after major schema changes or on a fixed schedule, then keep WAL archiving continuous. Many production teams also test a restore monthly, because an untested backup is only a guess.

Can I use PostgreSQL PITR for accidental deletes only?

Yes. That is one of the main reasons to use it. PITR lets you roll back to the minute before a mistake, not just to the last nightly backup.

Should WAL archives live on the same VPS?

Use a separate storage target when possible. If the whole VPS fails, local-only archives may fail with it. A second volume or remote backup target is safer.

Do I still need logical backups if I have PITR?

Yes. PITR handles recovery. Logical backups help with table-level exports, migrations, and selective restores.

For more backup and recovery planning, see WordPress recovery plan for updates, migrations, and rollbacks and WHM backup restore drill on AlmaLinux for safer migrations if your environment also includes hosted sites or control panels.