PostgreSQL Backup and Restore on a Hostperl VPS

Why this PostgreSQL backup and restore plan matters

PostgreSQL backup and restore is what separates a fast recovery from a messy outage. If your VPS runs an app, store, or customer portal, you need a backup you can restore, not just a file sitting on disk.

This guide walks through the full workflow on a fresh Hostperl VPS: secure access, install PostgreSQL, create a backup user, run backups, restore them safely, and verify the result. If you are sizing a new database server or moving an existing workload, a Hostperl VPS gives you enough room to keep backups, test restores, and grow without rebuilding your stack.

For readers comparing recovery methods, this pairs well with Set Up PostgreSQL Backup and Restore on a Hostperl VPS and PostgreSQL Backup Pitfalls: Common Restore Mistakes, which cover the operational side and the mistakes people make during restores.

Connect to the VPS and confirm the operating system

On your local computer, start with the standard SSH login below.

ssh root@203.0.113.10

203.0.113.10 is a reserved documentation example. Replace it with the real public IP assigned to your Hostperl server.

If your server provider gave you a default non-root login such as deploy, use that account instead:

ssh deploy@203.0.113.10

On the VPS as root, identify the distribution before you install anything.

cat /etc/os-release

You should see whether the server is Ubuntu, Debian, AlmaLinux, or Rocky Linux. The package and service commands below are split by family so you can follow the right path.

Create a non-root admin before you touch the database

Do not manage a production database from root unless you have no other choice. Create a sudo user first, keep the root session open, and verify the new login in a second terminal.

Ubuntu and Debian

On the VPS as root, create the administrator, add sudo access, and prepare SSH keys.

adduser deploy

Set a strong password when prompted. Then grant sudo rights:

usermod -aG sudo deploy

Create the SSH directory and set safe permissions:

mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
nano /home/deploy/.ssh/authorized_keys

Paste the public key for the new admin, save the file, then lock down permissions:

chmod 600 /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh

On the VPS as root, open a second terminal from your local computer and test the new account before closing root.

ssh deploy@203.0.113.10

Then confirm sudo works:

sudo -v

You should be prompted for the deploy user’s password or sudo token and then returned to the shell. Keep the original root session open until this succeeds.

AlmaLinux and Rocky Linux

On the VPS as root, create the admin user and add wheel access.

useradd -m deploy
passwd deploy
usermod -aG wheel deploy

Set up SSH keys and permissions:

mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
nano /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh

Test the login from a second terminal before you continue.

ssh deploy@203.0.113.10
sudo -v

If sudo fails, check that the user is in the wheel group with id deploy.

Install PostgreSQL and create a backup workspace

Use a dedicated directory for backups and restore tests. On Hostperl VPS plans, that keeps database files separate from application files and makes disk growth easier to track.

Ubuntu and Debian

On the VPS as the non-root sudo user, update packages and install PostgreSQL.

sudo apt update
sudo apt install -y postgresql postgresql-client

Check the running version:

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

Create a backup directory with restricted access:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql

AlmaLinux and Rocky Linux

On the VPS as the non-root sudo user, install the database packages with dnf.

sudo dnf install -y postgresql-server postgresql

Initialize and start the service if your package set requires it:

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

Confirm the service and version:

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

Then create the backup directory:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql

Create a database and sample data for the restore test

You need a real target to prove the restore works. This section creates a small database you can back up and rebuild.

On the VPS as the non-root sudo user, switch to the postgres account and create a demo database plus table.

sudo -iu postgres psql

At the psql prompt, run:

CREATE DATABASE shopdb;
\c shopdb
CREATE TABLE orders (id serial PRIMARY KEY, customer text NOT NULL, total numeric(10,2) NOT NULL, created_at timestamptz DEFAULT now());
INSERT INTO orders (customer, total) VALUES ('Aroha', 119.50), ('Mason', 42.00), ('Leilani', 88.25);
\q

Back on the shell, confirm the rows exist:

sudo -iu postgres psql -d shopdb -c "SELECT count(*) FROM orders;"

You should see a count of 3.

Run PostgreSQL backup and restore with pg_dump and pg_restore

Use pg_dump for logical backups. It is portable, readable, and easy to test. For most small and medium VPS databases, that is the right place to start.

On the VPS as the non-root sudo user, create a plain SQL dump and a custom-format backup.

sudo -iu postgres pg_dump shopdb > /var/backups/postgresql/shopdb.sql
sudo -iu postgres pg_dump -Fc shopdb > /var/backups/postgresql/shopdb.dump
ls -lh /var/backups/postgresql

You should see both files with non-zero sizes. The custom-format file is what you use with pg_restore.

Check that the files are readable only by postgres:

sudo stat -c '%a %U %G %n' /var/backups/postgresql/*

A secure setup should show 600 or similarly restrictive permissions and ownership by postgres.

Now prove the restore path. Drop the database, recreate it empty, and restore from the custom dump.

Warning: this removes the test database. Do not run it on a live production database unless you intend to rebuild it.

sudo -iu postgres psql -c "DROP DATABASE shopdb;"
sudo -iu postgres psql -c "CREATE DATABASE shopdb;"
sudo -iu postgres pg_restore -d shopdb /var/backups/postgresql/shopdb.dump

Verify the restored data:

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

You should see the same three rows you inserted earlier.

Automate backups with cron and a retention policy

A one-time backup is not enough. Add a simple cron job so the database is captured on a regular schedule.

On the VPS as the non-root sudo user, create a backup script.

sudo nano /usr/local/bin/pg_backup_shopdb.sh

Paste this content:

#!/bin/sh
set -eu
BACKUP_DIR=/var/backups/postgresql
DATE=$(date +%F_%H-%M-%S)
sudo -iu postgres pg_dump -Fc shopdb > "$BACKUP_DIR/shopdb-$DATE.dump"
find "$BACKUP_DIR" -type f -name 'shopdb-*.dump' -mtime +7 -delete

Save and exit, then make it executable:

sudo chmod 750 /usr/local/bin/pg_backup_shopdb.sh
sudo chown root:root /usr/local/bin/pg_backup_shopdb.sh

Add the cron entry:

sudo crontab -e

Insert this line:

15 2 * * * /usr/local/bin/pg_backup_shopdb.sh

That runs every day at 02:15 and keeps seven days of backups. Confirm cron accepts the schedule:

sudo crontab -l

For a broader recovery plan, see PostgreSQL Backup Pitfalls: Common Restore Mistakes and PostgreSQL Point-in-Time Recovery on a Hostperl VPS. The first helps you avoid bad dumps; the second is the next step if you need time-based recovery.

Back up and restore across servers with a realistic workflow

If you are migrating to a larger Hostperl VPS or moving from a staging box to production, copy the backup file and restore it on the new host. That is usually safer than trying to transfer raw data directories between systems.

On the source VPS as the non-root sudo user, copy the dump to another machine or storage target.

scp /var/backups/postgresql/shopdb.dump deploy@203.0.113.10:/home/deploy/

Replace the example IP with the destination server’s address. After the copy, confirm the file arrived:

ls -lh /home/deploy/shopdb.dump

On the destination VPS as the non-root sudo user, create the database and restore the dump.

sudo -iu postgres psql -c "CREATE DATABASE shopdb;"
sudo -iu postgres pg_restore -d shopdb /home/deploy/shopdb.dump

Then run the same smoke test query:

sudo -iu postgres psql -d shopdb -c "SELECT count(*) FROM orders;"

That confirms the restore is usable, not just present.

Verify service health, logs, and reboot persistence

On the VPS as the non-root sudo user, check the service status, listening port, and logs.

systemctl status postgresql --no-pager
ss -ltnp | grep 5432
journalctl -u postgresql -n 50 --no-pager

Expected results: PostgreSQL should be active, listening on port 5432, and free of startup errors. If you reboot the server, the service should still come back automatically.

Test that explicitly with:

sudo systemctl is-enabled postgresql
sudo reboot

After the VPS comes back, reconnect and repeat:

systemctl status postgresql --no-pager
sudo -iu postgres psql -d shopdb -c "SELECT now();"

Troubleshooting the most common restore problems

Most restore failures are predictable. Check the clue, then fix the cause.

• Permission denied on backup files: run ls -l /var/backups/postgresql. If ownership is wrong, fix it with sudo chown -R postgres:postgres /var/backups/postgresql.
• pg_restore says the database exists: run sudo -iu postgres psql -c "\l" and confirm the target name. Drop the conflicting database or restore into a fresh one.
• Service will not start: run journalctl -u postgresql -n 100 --no-pager. Look for port conflicts or bad configuration, then correct the file under /etc/postgresql/ on Debian-based systems or /var/lib/pgsql/data/ on RHEL-compatible systems.
• Restore seems fine but the app still fails: run the application’s database query from the app host and compare credentials, schema name, and host access.

FAQ

Should I use pg_dump or a raw filesystem backup?
Use pg_dump for most hosting workloads. It is easier to restore across versions and servers. Raw filesystem backups are better for specialized cases and usually need more care.

How often should I test restores?
Test at least once after you build the backup job, then on a regular schedule. Monthly is a practical minimum for customer-facing sites.

Can I restore a backup to a different PostgreSQL version?
Often yes with logical backups such as pg_dump and pg_restore. That is one reason they are safer for VPS migrations.

What size VPS do I need for backups?
Choose enough storage for the live database, at least one backup copy, and temporary restore work. If your database is growing fast, step up before disk usage becomes an outage risk.

Do I still need backups if my VPS uses snapshots?
Yes. Snapshots help with fast rollback, but they do not replace a tested database backup and restore process.

Final check before you put this into production

Run one last verification from the server and one from your client. On the VPS, confirm the backup file exists, PostgreSQL is active, and the restored table still has rows. From your local machine or app server, connect to the database-backed application and confirm it can read and write the expected data.

If you are building on Hostperl for long-term growth, keep the backup plan with the server plan. A managed VPS hosting setup makes it easier to keep recovery tests, capacity planning, and migration work inside one support path.