Set Up PostgreSQL Backup and Restore on a Hostperl VPS

PostgreSQL backup and restore starts with a tested plan

If you take only one lesson from this guide, make it this: a PostgreSQL backup that has never been restored is still a guess. On a Hostperl VPS, you need backups that survive a bad migration, a broken update, or a disk failure. This tutorial shows you how to set up PostgreSQL backup and restore on a fresh server, test the restore, and automate the routine with cron.

If you are still choosing a server, a small PostgreSQL workload usually fits well on a Hostperl VPS. For teams moving an active site or internal app, that gives you room to grow storage and RAM without rebuilding later.

ssh root@203.0.113.10

This is a reserved documentation example. Replace 203.0.113.10 with the real public IP of your server.

If your Hostperl plan provides a default non-root login, use that account instead:

ssh deploy@203.0.113.10

Keep the root session open until the new login is verified. After that, you can harden access safely.

Check the operating system first

PostgreSQL package names, service names, and firewall steps differ by distribution. Confirm the OS before you install anything.

cat /etc/os-release

You should see either Ubuntu or Debian family details, or AlmaLinux/Rocky Linux release information. The next steps are split by family so you do not apply the wrong commands.

Prepare a non-root admin account for backup work

Backups often run unattended. That means you should use a named administrator account, not root, for daily maintenance.

Ubuntu and Debian

adduser deploy

Set a strong password when prompted. Then give the account sudo access.

usermod -aG sudo deploy

Now create SSH access for the account. Run this on the VPS as root, replacing the example key path with your own public key file if needed.

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

Open a second terminal and test the login.

ssh deploy@203.0.113.10
sudo -v

You should land in the shell as deploy and see no sudo error. Leave the root session open until this works.

AlmaLinux and Rocky Linux

useradd deploy
passwd deploy

After setting the password, add the account to wheel for sudo access.

usermod -aG wheel deploy

Create the SSH directory and copy the key safely.

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

Test a second login and sudo access before you change any SSH policy.

ssh deploy@203.0.113.10
sudo -v

Install PostgreSQL and backup tools

Hostperl customers often ask whether backups should live on the same server. They should not. Install the database tools on the VPS, then copy archives to off-server storage later in your workflow.

Ubuntu and Debian

apt update
apt install -y postgresql postgresql-client

Check the service and client version.

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

On Debian-based systems, PostgreSQL usually starts automatically. If you manage a specific cluster, list it with:

pg_lsclusters

AlmaLinux and Rocky Linux

dnf install -y postgresql-server postgresql

Initialize the database cluster if the package has not already done it for you.

postgresql-setup --initdb

Then enable and start the service.

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

If SELinux is enforcing, keep your backup directories under standard locations such as /var/lib/pgsql or /var/backups unless you know how to label a custom path correctly.

Create a backup directory and restrict access

Use a dedicated directory for SQL dumps. That keeps the files easy to rotate, copy, and clean up.

mkdir -p /var/backups/postgresql
chown root:root /var/backups/postgresql
chmod 700 /var/backups/postgresql

The directory should be readable only by root unless you intentionally delegate access to another service account.

Create a sample database and user for testing

Testing restore behavior against a real database is better than guessing with an empty cluster. The names below are examples you can copy exactly for a lab system.

sudo -iu postgres psql

At the PostgreSQL prompt, create a sample database and user:

CREATE USER appuser WITH PASSWORD 'change-this-password';
CREATE DATABASE appdb OWNER appuser;
\q

Now create a simple table and row so your backup contains something real. Run the next command as the postgres user.

sudo -iu postgres psql -d appdb -c "CREATE TABLE notes (id serial PRIMARY KEY, body text NOT NULL); INSERT INTO notes (body) VALUES ('backup test'); SELECT * FROM notes;"

You should see one row returned. That gives you a clean restore target later.

Run a manual PostgreSQL backup

For most hosting customers, pg_dump is the right first tool. It creates a portable logical backup you can move to another VPS, restore after a bad deploy, or hand to support during recovery.

sudo -iu postgres pg_dump -Fc appdb > /var/backups/postgresql/appdb-$(date +%F-%H%M).dump

This creates a custom-format dump of appdb in /var/backups/postgresql. Replace appdb with your own database name.

Confirm the file exists and is not empty:

ls -lh /var/backups/postgresql
file /var/backups/postgresql/appdb-*.dump

You should see a recent dump file with a sensible size, not a zero-byte placeholder.

Restore the backup into a clean database

Restore testing is the part many teams skip. Do not skip it. A restore that fails at 2 a.m. costs far more time than a ten-minute test restore now.

sudo -iu postgres createdb appdb_restore
sudo -iu postgres pg_restore -d appdb_restore /var/backups/postgresql/appdb-*.dump

Now verify the data inside the restored database.

sudo -iu postgres psql -d appdb_restore -c "SELECT * FROM notes;"

You should see the backup test row. If that row appears, your backup and restore chain is working.

Automate the backup with cron

Backups should happen without a human remembering to log in. A simple daily cron job is enough for many small business sites and internal apps.

crontab -e

Add this line to run a daily dump at 02:15 and remove files older than seven days. Save and exit the editor.

15 2 * * * sudo -iu postgres pg_dump -Fc appdb > /var/backups/postgresql/appdb-$(date +\%F-\%H\%M).dump && find /var/backups/postgresql -type f -name 'appdb-*.dump' -mtime +7 -delete

If you prefer to run it as root from a shell script, create a file such as /usr/local/sbin/backup-appdb.sh and call sudo -iu postgres inside it. That gives you a central place to add off-server copy commands later.

Add a simple restore script for emergencies

When support teams need to recover a site quickly, a small restore script reduces mistakes. Keep it readable.

cat > /usr/local/sbin/restore-appdb.sh <<'EOF'
#!/bin/sh
set -eu
DUMP_FILE="$1"
DB_NAME="appdb_restore_from_dump"
sudo -iu postgres dropdb --if-exists "$DB_NAME"
sudo -iu postgres createdb "$DB_NAME"
sudo -iu postgres pg_restore -d "$DB_NAME" "$DUMP_FILE"
echo "Restored into $DB_NAME"
EOF
chmod 700 /usr/local/sbin/restore-appdb.sh

Run it against a recent dump file to confirm the workflow.

/usr/local/sbin/restore-appdb.sh /var/backups/postgresql/appdb-*.dump
sudo -iu postgres psql -d appdb_restore_from_dump -c "SELECT count(*) FROM notes;"

Protect the database and the backup files

Even on a private VPS, you should keep the database on a local interface and avoid broad exposure. PostgreSQL normally listens on localhost by default on many setups, which is exactly what you want for a single-server app.

If you do open the port for replication or another host, add the firewall rule before you change PostgreSQL access settings.

UFW on Ubuntu and Debian

ufw allow OpenSSH
ufw allow 5432/tcp
ufw status verbose

Only open port 5432 if you genuinely need remote PostgreSQL access. For a local-only deployment, leave it closed.

firewalld on AlmaLinux and Rocky Linux

systemctl enable --now firewalld
firewall-cmd --permanent --add-service=ssh
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
firewall-cmd --list-all

Again, open PostgreSQL only when required. Many Hostperl customers keep the port private and connect through the application layer instead.

Verify logs, listening ports, and reboot behavior

Now confirm the service is healthy from the server side and from a client shell.

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

On Debian-based systems, the service name may be attached to the cluster package, but postgresql is still the usual systemd unit. On AlmaLinux and Rocky Linux, the same unit name is used after initialization.

From your local computer, test that the backup file is present on the server and that the database responds if you opened remote access:

ssh deploy@203.0.113.10 "ls -lh /var/backups/postgresql && psql --version"

To test reboot persistence, restart the VPS and confirm PostgreSQL comes back:

systemctl is-enabled postgresql
systemctl is-active postgresql

You want enabled and active. If either check fails, fix the unit before you rely on the machine for backups.

Common restore problems and how to diagnose them

pg_restore says the database does not exist

Run this command to confirm the target database name exists before restore:

sudo -iu postgres psql -lqt

If the database is missing, create it first with createdb, then rerun pg_restore.

The dump file is empty or too small

Check the source database size and the dump command output:

du -h /var/backups/postgresql/appdb-*.dump
sudo -iu postgres pg_dump -Fc appdb > /tmp/appdb-test.dump
ls -lh /tmp/appdb-test.dump

If the new file is still tiny, you may be dumping the wrong database name or connecting to the wrong cluster.

Remote access fails after opening the firewall

Check whether PostgreSQL is listening on the address you expect:

ss -ltnp | grep 5432
sudo -iu postgres grep -n '^listen_addresses' /etc/postgresql/*/main/postgresql.conf /var/lib/pgsql/data/postgresql.conf 2>/dev/null

If it still binds to localhost, adjust listen_addresses carefully and test syntax before restarting the service. Then reload or restart PostgreSQL and confirm the port again.

FAQ

Should I use pg_dump or filesystem snapshots?

Use pg_dump for portable logical backups and easy restore testing. Use snapshots as a supplement, not a replacement, unless you also test crash recovery and consistency.

How often should PostgreSQL backups run?

For a small production site, daily logical backups are a practical baseline. Faster-changing databases may need more frequent dumps plus separate off-server copies.

Can I restore a PostgreSQL dump to a different server?

Yes. That is one of the main reasons to use pg_dump -Fc. Restore it with pg_restore on another PostgreSQL server of a compatible major version.

Where should I store the backup files?

Store them on the VPS temporarily, then copy them to a separate system or storage target. Do not keep your only copy on the same disk as the live database.

Final check before you trust the job

Run one last backup and one last restore. If both succeed, you have a working PostgreSQL backup and restore routine, not just a script that looks right.

sudo -iu postgres pg_dump -Fc appdb > /var/backups/postgresql/appdb-final.dump
sudo -iu postgres createdb appdb_final_restore
sudo -iu postgres pg_restore -d appdb_final_restore /var/backups/postgresql/appdb-final.dump
sudo -iu postgres psql -d appdb_final_restore -c "SELECT * FROM notes;"

That final smoke test should return the row you inserted earlier. Once it does, you can ship the database with more confidence on a Hostperl VPS or a larger dedicated server if the workload grows.