How to install PostgreSQL on Ubuntu 24.04 and keep it private

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:

Pick a version#

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

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

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.

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:

pg_lsclusters

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:

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"):

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

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:

sudo -u postgres psql

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

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:

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:

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.

sudo -u postgres psql -d appdb

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:

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:

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 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, 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.

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:

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:

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 and borgmatic to a second server both include it, and our guide to 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.
• Run your app next to it: Node.js with PM2 and nginx or FastAPI with Gunicorn and nginx.
• Send the nightly dumps off the server with restic.
• 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. The PostgreSQL documentation explains the options.