How to install PostgreSQL on an Ubuntu or Debian VPS

Step 1. Install PostgreSQL from the distribution repository

The postgresql package from the Ubuntu or Debian repository is the simplest route: it creates the database cluster, the postgres system user and a service that starts at boot. Security updates arrive with a regular apt upgrade:

sudo apt update
sudo apt install -y postgresql
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

If you need a newer version than the distribution ships, add the official PostgreSQL repository following the instructions on postgresql.org - the remaining steps stay the same. Configuration files live in /etc/postgresql/<version>/main/.

Step 2. Create a role and a database for the app

A role in PostgreSQL is a database user. Your app needs its own role with a password and its own database rather than the postgres superuser. createuser --pwprompt asks for the password, and createdb --owner creates a database owned by that role:

sudo -u postgres createuser --pwprompt myapp
sudo -u postgres createdb --owner=myapp myapp
psql -h localhost -U myapp -d myapp -c "SELECT current_user;"

You can do the same with SQL in the psql console. sudo -u postgres psql lets you in without a password thanks to the peer method: over the local socket PostgreSQL matches the role name against the system user name.

sudo -u postgres psql
CREATE ROLE myapp WITH LOGIN PASSWORD 'strong-password';
CREATE DATABASE myapp OWNER myapp;
\q

Step 3. Connect remotely through an SSH tunnel

An SSH tunnel is the safest way to open the database in DBeaver, pgAdmin or DataGrip from your own computer: port 5432 stays closed and the traffic travels inside an already encrypted SSH connection. Run the command on your computer with your user and server IP, and keep the window open:

ssh -N -L 5432:localhost:5432 deploy@203.0.113.10

Now point the client at localhost:5432 with the myapp role and its password. If your computer already runs its own PostgreSQL, use another local port: -L 15432:localhost:5432. Key-based login is covered in the guide to connecting to a VPS over SSH.

Step 4. If you really need to open the port

Direct access to port 5432 is needed when another server talks to the database and a tunnel is inconvenient. Then everything except one address is shut out, on three levels: PostgreSQL listens on the external interface, pg_hba.conf admits only the right role from the right IP, and UFW passes packets only from that IP. First find the configuration file paths:

sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

Change listen_addresses in postgresql.conf and add a line to pg_hba.conf, putting your client's IP in place of 198.51.100.7. hostssl allows only encrypted connections - on Ubuntu and Debian PostgreSQL has SSL enabled by default with a self-signed certificate - and scram-sha-256 is the modern password check method:

# postgresql.conf
listen_addresses = '*'

# pg_hba.conf - add a line at the end
hostssl  myapp  myapp  198.51.100.7/32  scram-sha-256

sudo systemctl restart postgresql
sudo ufw allow from 198.51.100.7 to any port 5432 proto tcp
sudo ss -tlnp | grep 5432

Step 5. Set up pg_dump backups

pg_dump -Fc takes a consistent dump of a database in the compressed custom format straight from the running server - there is no need to stop PostgreSQL. Roles and their passwords are not part of a database dump; pg_dumpall --globals-only saves them:

mkdir -p ~/backups
sudo -u postgres pg_dump -Fc myapp > ~/backups/myapp-$(date +%F).dump
sudo -u postgres pg_dumpall --globals-only > ~/backups/globals-$(date +%F).sql

A dump is restored with pg_restore - to test it, restore into a separate database and leave the live one alone:

sudo -u postgres createdb --owner=myapp myapp_restored
sudo -u postgres pg_restore -d myapp_restored --no-owner --role=myapp < ~/backups/myapp-2026-10-05.dump

A dump that sits on the same server is lost together with it. Shipping dumps to another server on a schedule, keeping history and testing restores is covered in the guide to VPS backups.

Step 6. Basic memory tuning

By default PostgreSQL uses only 128 MB for its own cache (shared_buffers). The common rule of thumb is about 25% of the server's RAM, while effective_cache_size is a hint to the planner about how much data fits in cache together with the OS cache, usually 50-75% of RAM. An example for a 4 GB server where PostgreSQL is the main workload:

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
sudo -u postgres psql -c "ALTER SYSTEM SET effective_cache_size = '3GB';"
sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

These are starting values, not a formula: if the app and nginx share the server, leave them headroom and go lower. Change one parameter at a time and watch memory and query times. ALTER SYSTEM writes settings to postgresql.auto.conf, and shared_buffers takes effect only after a restart.

What VPS does PostgreSQL need?

The database of a small website or bot fits in 2 GB of RAM; as the data grows and the app shares the server, take 4-8 GB so hot data stays in cache. More in the guide to how much RAM a VPS needs. Tihost servers run on NVMe drives, which matters for a write-heavy database, and start at $4.00 a month. The app on top of the database can be deployed with the Node.js on a VPS guide.