---
title: "How to install PostgreSQL on an Ubuntu or Debian VPS"
description: "PostgreSQL on a VPS: install with apt, create a role and database, secure remote access via SSH tunnel or pg_hba.conf, pg_dump backups and basic memory tuning."
url: https://tihost.io/en/blog/postgresql-on-vps
language: en
section: "Guides"
published: 2026-10-05
updated: 2026-10-05
publisher: Tihost (https://tihost.io)
---

# How to install PostgreSQL on an Ubuntu or Debian VPS

> **In short:** On Ubuntu and Debian PostgreSQL installs with a single `sudo apt install postgresql` and listens only on localhost by default. Create a dedicated role and database for your app, reach the database from your own computer through an SSH tunnel instead of exposing port 5432 to the internet, take a daily `pg_dump -Fc`, and start memory tuning with shared_buffers at about 25% of RAM.

**Key takeaways:**

- PostgreSQL installs on Ubuntu and Debian from the distribution repository with `sudo apt install postgresql`: that is version 14 on Ubuntu 22.04, 15 on Debian 12 and 16 on Ubuntu 24.04.
- After installation PostgreSQL listens only on localhost, so port 5432 is closed to the internet; the safest way to reach the database remotely is an SSH tunnel, `ssh -L 5432:localhost:5432`.
- If the PostgreSQL port must be exposed, pg_hba.conf allows one specific IP with the scram-sha-256 method, and UFW opens port 5432 to that address only.
- A PostgreSQL backup is made with `pg_dump -Fc` and restored with `pg_restore`; the copy has to be stored off the database server.
- The common rule of thumb for PostgreSQL shared_buffers is about 25% of the server's RAM, while the default is only 128 MB.

> Commands target Ubuntu 22.04/24.04 and Debian 12. Do the [first server setup](https://tihost.io/en/blog/vps-first-setup) and enable UFW before installing.

## 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`:

```bash
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](https://www.postgresql.org/download/) - 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:

```bash
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.

`psql`:

```
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:

```bash
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](https://tihost.io/en/blog/ssh-connect-to-vps).

## 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:

```bash
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:

`/etc/postgresql/<version>/main/`:

```
# postgresql.conf
listen_addresses = '*'

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

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

> Never put `0.0.0.0/0` in pg_hba.conf or open port 5432 in UFW for everyone: an exposed PostgreSQL is brute-forced by bots around the clock. The role password should be long and random.

## 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:

```bash
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:

```bash
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](https://tihost.io/en/blog/vps-backup).

## 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:

```bash
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](https://tihost.io/en/blog/how-much-ram-vps). 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](https://tihost.io/en/blog/nodejs-app-on-vps) guide.

**Launch a server in 2 minutes.** AMD Ryzen 9, NVMe and DDoS protection in Germany, Finland and Poland. Pay with crypto or card. [Order a Server](https://tihost.io/login)

## FAQ

### How do I connect to PostgreSQL on a VPS from my home computer?

The safest way is an SSH tunnel: run `ssh -N -L 5432:localhost:5432 user@server-ip`, then point the client at `localhost:5432`. The PostgreSQL port stays closed to the internet.

### Why does PostgreSQL refuse external connections?

After installation on Ubuntu and Debian PostgreSQL listens only on localhost. External connections need `listen_addresses` changed in postgresql.conf, a line for the client IP in pg_hba.conf, a service restart and port 5432 opened in the firewall.

### Which PostgreSQL version does apt install?

Ubuntu 22.04 installs PostgreSQL 14, Debian 12 installs PostgreSQL 15 and Ubuntu 24.04 installs PostgreSQL 16. Newer versions are available from the official PostgreSQL repository (apt.postgresql.org).

### What is the difference between pg_dump and pg_dumpall?

`pg_dump` saves a single database, while `pg_dumpall` saves the whole PostgreSQL cluster including roles. A practical scheme is `pg_dump -Fc` for each database plus `pg_dumpall --globals-only` for roles.

### How much memory should PostgreSQL shared_buffers get?

The common rule of thumb for shared_buffers is about 25% of the server's RAM, for example 1 GB on a 4 GB server. If an app runs on the same VPS, go lower.

### PostgreSQL in Docker or from apt?

apt gives simpler security updates and standard config paths; Docker is handier when the whole app is described in compose.yaml. In Docker publish the PostgreSQL port only on `127.0.0.1`, because Docker opens ports around UFW.

---

Updated 2026-10-05 · https://tihost.io/en/blog/postgresql-on-vps