Install PostgreSQL and test backup restore

This guide installs PostgreSQL from Ubuntu 26.04 packages and keeps it on the local interface. It then creates an application role and proves that a logical backup can be restored into a separate database.

Prerequisites

• An Ubuntu 26.04 VPS with a non-root sudo user
• The VPS security baseline
• Enough free disk space for the live database, temporary files, and at least one backup

Install and inspect PostgreSQL

sudo apt update
sudo apt install postgresql
sudo systemctl enable --now postgresql
sudo systemctl status postgresql --no-pager
sudo -u postgres psql -c 'SELECT version();'
sudo -u postgres psql -c 'SHOW listen_addresses;'
sudo ss -lntp | grep 5432

For an application on the same VPS, keep listen_addresses on loopback. Do not add a public UFW rule for port 5432.

Create the role and database

Open PostgreSQL as its administrator:

sudo -u postgres psql

Create a login role and database, then set the password interactively so it does not enter shell history:

CREATE ROLE example_app LOGIN;
CREATE DATABASE example OWNER example_app;
\password example_app
\q

Store the resulting connection string in the application’s protected environment file. Test the connection and the role’s ability to create, write, read, and remove a temporary object:

psql 'postgresql://example_app@127.0.0.1/example' --set ON_ERROR_STOP=1 --command "CREATE TEMP TABLE permission_check (value text); INSERT INTO permission_check VALUES ('ok'); TABLE permission_check; DROP TABLE permission_check;"

The client prompts for the password and should print ok. The temporary table disappears with the session and does not touch application data. Use a .pgpass file with mode 600 for unattended jobs instead of placing a password in the command.

Create a known record

Create a small table that can prove the restore:

sudo -u postgres psql example

CREATE TABLE restore_check (id integer PRIMARY KEY, value text NOT NULL);
INSERT INTO restore_check VALUES (1, 'backup-test');
\q

Back up and restore

Create a protected backup directory and a custom-format dump:

sudo install -d -o postgres -g postgres -m 0700 /var/backups/postgresql
sudo -u postgres pg_dump --format=custom --file=/var/backups/postgresql/example.dump example
sudo -u postgres pg_restore --list /var/backups/postgresql/example.dump | head

Restore into a new database. --no-owner makes the test independent of source ownership metadata, while --role=example_app assigns restored objects to the application role.

sudo -u postgres createdb --owner=example_app example_restore_test
sudo -u postgres pg_restore --exit-on-error --no-owner --role=example_app --dbname=example_restore_test /var/backups/postgresql/example.dump
sudo -u postgres psql example_restore_test -c 'TABLE restore_check;'

The result must contain 1 | backup-test. Remove the test database when finished:

sudo -u postgres dropdb example_restore_test

Copy backups to separate storage, encrypt them, define retention, and automate both backup monitoring and regular restore tests. A successful pg_dump exit code alone does not prove recovery works.

Monitor and maintain

• Alert on low disk space before PostgreSQL or the operating system runs out.
• Monitor database size, connections, slow queries, failed backups, and autovacuum health.
• Read PostgreSQL release notes before minor and major upgrades.
• Rehearse major-version upgrades and application rollback in staging.

See running databases on a VPS for sizing and the choice between self-managed and managed databases.

Sources: PostgreSQL client authentication, pg_dump, and pg_restore.