# How to install PostgreSQL on Ubuntu 24.04 and keep it private

> Install PostgreSQL 16 from Ubuntu or 18 from the PostgreSQL repository, create a role and a database, and connect safely over an SSH tunnel.

Source: https://vpsnine.com/tutorials/install-postgresql-ubuntu · Updated: 2026-10-21

Difficulty: beginner · Time: about 20 minutes · Tested on: Ubuntu 24.04 LTS

Prerequisites:

- A server running Ubuntu 24.04
- Root or a user with sudo
- An SSH session to the server

To install PostgreSQL on Ubuntu 24.04, run `sudo apt install postgresql` for the version Ubuntu ships (PostgreSQL 16), or add the PostgreSQL project's own apt repository first and install `postgresql-18` for the newest major release. Either way it starts listening on localhost only with password logins hashed as SCRAM-SHA-256, so the work left is creating a role and a database for your app and reaching it from your laptop through an SSH tunnel instead of opening port 5432.

The examples use these names. Replace them with yours:

| What | In this tutorial |
| --- | --- |
| The server's public IPv4 address | `203.0.113.10` |
| Your login on the server | `deploy` |
| The app's role (user) and database | `appuser` and `appdb` |
| A read-only role for reports | `reporting` |

## Pick a version

Both sources are maintained, and both get security updates through `apt upgrade`:

| Source | Package | Version on Ubuntu 24.04 (October 2026) | Supported until |
| --- | --- | --- | --- |
| Ubuntu's archive | `postgresql` | 16 | PostgreSQL 16 is supported upstream until November 2028 |
| PostgreSQL apt repository ([PGDG](https://wiki.postgresql.org/wiki/Apt)) | `postgresql-18` | 18 | November 2030 |

Use Ubuntu's package unless your app needs something newer: it is one command and Ubuntu keeps it patched for the life of 24.04. Use PGDG when an app or extension asks for 17 or 18, or when you want to stay on the newest release and upgrade between majors yourself. The PGDG repository also carries older majors (`postgresql-16`, `postgresql-17` and so on), so you can match the version another server runs.

## Install PostgreSQL

**From Ubuntu (PostgreSQL 16):**

```bash
sudo apt update
sudo apt install -y postgresql
```

**From the PostgreSQL repository (PostgreSQL 18):** the `postgresql-common` package ships a script that adds the repository and its signing key for you.

```bash
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-18
```

The script asks you to press Enter before it changes anything. After that, `apt upgrade` brings PostgreSQL 18's minor releases (18.6, 18.7 and so on) like any other package. It never jumps to a new major release on its own.

### Check it worked

The install creates a cluster called `main` and starts it. `pg_lsclusters` lists it:

```bash
pg_lsclusters
```

```text
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
```

The first column is your major version (16 if you used Ubuntu's package); the paths in the rest of this tutorial use it. Ask the server itself which version it runs:

```bash
sudo -u postgres psql -c "SELECT version();"
```

On Ubuntu's package this prints `PostgreSQL 16.x (Ubuntu ...)`; from PGDG, `PostgreSQL 18.x (Ubuntu 18.x-1.pgdg24.04+...)`. The service is `postgresql@18-main` (or `postgresql@16-main`), so `systemctl status postgresql@18-main` shows it and `journalctl -u postgresql@18-main` has its startup messages.

## Understand how logins work

Ubuntu's PostgreSQL comes set up safely. It is worth knowing why before you change anything:

- **`listen_addresses = 'localhost'`.** PostgreSQL only listens on `127.0.0.1` and `::1`. Nothing on the internet can reach port 5432.
- **Peer login over the Unix socket.** The `postgres` database superuser has no password. You become it by being the `postgres` Linux user, which is what `sudo -u postgres psql` does.
- **Passwords over TCP.** Connections to `127.0.0.1` must give a password, checked with SCRAM-SHA-256 (the default since PostgreSQL 14). The old `md5` method is not used.

You can see the rules in `pg_hba.conf` ("host-based authentication"):

```bash
sudo grep -v '^#' /etc/postgresql/18/main/pg_hba.conf | grep -v '^$'
```

```text
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
```

PostgreSQL reads the lines top to bottom and uses the first one that matches the connection type, database, user and address. Leave this file as it is for an app on the same server.

## Create a role and a database for your app

Give each app its own role (PostgreSQL's word for a user) that owns its own database, so the app never runs as the superuser. Open a superuser session:

```bash
sudo -u postgres psql
```

Then create the role with a long random password (`openssl rand -base64 24` makes one) and a database it owns:

```sql
CREATE ROLE appuser WITH LOGIN PASSWORD 'replace-with-a-long-random-password';
CREATE DATABASE appdb OWNER appuser;
\q
```

The role can't create other roles or databases, and because it owns `appdb` it can create tables in its `public` schema. On PostgreSQL 15 and later, other roles can't create objects in a database's `public` schema unless you grant it, so a second app's role can't write into this one.

Your app connects with a URL like this one:

```text
postgresql://appuser:replace-with-a-long-random-password@127.0.0.1:5432/appdb
```

### Check it worked

Connect as the new role over TCP, the way your app will:

```bash
psql -h 127.0.0.1 -U appuser -d appdb -c "CREATE TABLE hello (id int); INSERT INTO hello VALUES (1); SELECT * FROM hello;"
```

psql asks for the password and prints one row. Drop the test table afterwards with `DROP TABLE hello;`.

## Add a read-only role (optional)

A role for reporting tools or a teammate who only needs to read: allowed to connect and select, nothing else.

```bash
sudo -u postgres psql -d appdb
```

```sql
CREATE ROLE reporting WITH LOGIN PASSWORD 'another-long-random-password';
GRANT CONNECT ON DATABASE appdb TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
ALTER DEFAULT PRIVILEGES FOR ROLE appuser IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
```

`GRANT SELECT ON ALL TABLES` covers the tables that exist now. The `ALTER DEFAULT PRIVILEGES` line covers tables `appuser` creates later, for example in your app's next migration. An `INSERT` as `reporting` fails with `ERROR:  permission denied for table ...`, which is what you want.

## Connect from your laptop over an SSH tunnel

Don't open port 5432 to the internet. PostgreSQL's port is scanned constantly and password guessing against it never stops. An SSH tunnel gives your laptop the same access as a program on the server, protected by your SSH key.

On your laptop, open the tunnel and leave it running:

```bash
ssh -N -L 5433:127.0.0.1:5432 deploy@203.0.113.10
```

This forwards port 5433 on your laptop to port 5432 on the server's localhost. (5433 avoids clashing with a PostgreSQL you may run locally.) In a second terminal on your laptop, connect through it:

```bash
psql -h 127.0.0.1 -p 5433 -U appuser -d appdb
```

Graphical clients such as DBeaver, pgAdmin and DataGrip have an SSH tunnel option in their connection settings: give them the server's address, your SSH user and key, and `127.0.0.1` port `5432` as the database host. If your SSH login still uses a password, set up [SSH keys](https://vpsnine.com/help/ssh-keys) first.

If another server needs the database (an app server talking to a database server), connect the two over a private network or a VPN such as [WireGuard](https://vpsnine.com/tutorials/wireguard-vpn-server-ubuntu), add that network to `listen_addresses` and a `host ... scram-sha-256` line for its range to `pg_hba.conf`, and allow port 5432 from that range only in your [firewall](https://vpsnine.com/help/firewall-with-ufw).

## Give it some memory

PostgreSQL's defaults suit a very small machine (`shared_buffers` is 128 MB). On a server where PostgreSQL is the main job, a quarter of the RAM is the usual starting point. `ALTER SYSTEM` writes the setting to `postgresql.auto.conf` so you don't have to edit the main file:

```bash
sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '512MB';"
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW shared_buffers;"
```

That example is for a 2 GB server. `shared_buffers` only changes on a restart; most other settings apply with `SELECT pg_reload_conf();`. Leave the rest at the defaults until you have a slow query to look at.

## Back it up

Copying the data directory of a running PostgreSQL doesn't give you a usable backup. Dump each database instead:

```bash
sudo -u postgres pg_dump -Fc appdb > appdb.dump
```

`-Fc` is PostgreSQL's compressed custom format; restore it with `pg_restore -d appdb appdb.dump` into an empty database. Run the dump every night and send it off the server: [restic to S3-compatible storage](https://vpsnine.com/tutorials/restic-backup-s3) and [borgmatic to a second server](https://vpsnine.com/tutorials/borgmatic-backup-setup) both include it, and our guide to [backups you should run](https://vpsnine.com/help/backups-you-should-run) covers the basics.

## Troubleshooting

**`psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist`.** You ran `psql` as root without `-U`, so it tried a PostgreSQL role called `root`. Use `sudo -u postgres psql` for admin work.

**`FATAL:  Peer authentication failed for user "appuser"`.** You connected over the Unix socket (no `-h`), where the rules say your Linux username must match the role. Add `-h 127.0.0.1` to log in with the password instead.

**`FATAL:  password authentication failed for user "appuser"`.** Wrong password, or the role has none. Set it again with `ALTER ROLE appuser WITH PASSWORD '...';` as `postgres`.

**`connection to server at "127.0.0.1", port 5433 failed: Connection refused` on your laptop.** The SSH tunnel isn't running, or it exited because the SSH login failed. Start it again and watch for errors; add `-v` to see where it stops.

## Next steps

- Put a cache next to it: [install and secure Redis on Ubuntu 24.04](https://vpsnine.com/tutorials/install-redis-ubuntu).
- Run your app next to it: [Node.js with PM2 and nginx](https://vpsnine.com/tutorials/deploy-nodejs-app-pm2-nginx-ubuntu) or [FastAPI with Gunicorn and nginx](https://vpsnine.com/tutorials/deploy-fastapi-gunicorn-nginx).
- Send the nightly dumps off the server with [restic](https://vpsnine.com/tutorials/restic-backup-s3).
- Major upgrades (16 to 17, 17 to 18) are a separate job: install the new version next to the old one and use `pg_upgradecluster`, after a backup and a [snapshot](https://vpsnine.com/help/snapshots). The [PostgreSQL documentation](https://www.postgresql.org/docs/current/upgrading.html) explains the options.