How to install PostgreSQL on Ubuntu 24

Q: How do I install PostgreSQL on Ubuntu?

Update the package list: sudo apt update Then install PostgreSQL: sudo apt install postgresql Ubuntu 24.04 installs PostgreSQL 16 and 22.04 installs 14. For a newer version, add the official repository from postgresql.org.

Q: What is the default password for the postgres user?

There is none. The postgres role logs in locally through peer: sudo -u postgres psql If a tool needs a password, set one inside psql: \password postgres

Q: Where are PostgreSQL’s settings and data on Ubuntu?

Settings are in /etc/postgresql/16/main/ (postgresql.conf and pg_hba.conf), data in /var/lib/postgresql/16/main/, logs in /var/log/postgresql/. The number is your version.

To install PostgreSQL on Ubuntu you can take the version in Ubuntu’s own repository, PostgreSQL 16 on 24.04 and 14 on 22.04, or a newer one from the official repository of the PostgreSQL project. Either way the server starts right after the install. This guide also explains how the postgres user logs in, how to give an app its own database and how to open the server to one remote address.

Install PostgreSQL on Ubuntu with apt

The postgresql package installs the server, the psql client and a first database cluster, and starts it.

sudo apt update
sudo apt install -y postgresql
psql --version
pg_lsclusters

pg_lsclusters lists the clusters, for example 16 main 5432 online postgres /var/lib/postgresql/16/main. The word online means the server is running. On Ubuntu 22.04 the number is 14, and so are the folders in the paths below.

Install a newer PostgreSQL from postgresql.org

Ubuntu keeps one PostgreSQL version for the life of each release. The PostgreSQL project runs its own apt repository (PGDG) with every supported version. Its page gives a script that adds the repository and its signing key. The script asks you to press Enter before it changes anything.

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

If Ubuntu’s PostgreSQL 16 is already installed, the new version doesn’t replace it and doesn’t create a cluster of its own. Create one: sudo pg_createcluster 18 main --start It gets the next port, usually 5433, and pg_lsclusters shows both. Moving your data to the new version is a separate job (pg_upgradecluster), so back up first.

Log in as the postgres user

The install creates a system user and a database role, both called postgres. Local logins use peer authentication: PostgreSQL lets you in as the role with the same name as your system user, without a password. So you open the console as that system user:

sudo -u postgres psql

Inside, \l lists the databases, \du lists the roles and \q quits. A line like could not change directory to "/home/you": Permission denied above the prompt is harmless: the postgres user simply can’t read your home folder.

Create a user and a database

Give each app its own role and database, and make the role the database’s owner: since PostgreSQL 15 only the owner can create tables in the public schema without extra grants. Replace choose-a-strong-password with a long password of your own.

sudo -u postgres psql -c "CREATE USER app WITH PASSWORD 'choose-a-strong-password';"
sudo -u postgres psql -c "CREATE DATABASE app OWNER app;"
psql -h localhost -U app -d app

The last command asks for the password and opens the app database. The -h localhost part matters: it connects over TCP, where Ubuntu asks for a password (scram-sha-256). Without it, psql goes through the local socket with peer and fails, because your system user isn’t called app.

Allow remote connections carefully

By default PostgreSQL listens only on localhost. For a connection from another machine you change two files and open one port for one address. To find your version’s folder:

ls /etc/postgresql/

First, tell the server to listen on the network:

/etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

Then add a line at the end of pg_hba.conf that lets in user app, to database app, from one IP, with a password. Replace 198.51.100.7 with the IP of the machine that connects. With hostssl in place of host, the server refuses connections that aren’t encrypted.

/etc/postgresql/16/main/pg_hba.conf

host app app 198.51.100.7/32 scram-sha-256

A change of listen_addresses needs a restart. Then let only that address through the firewall:

sudo systemctl restart postgresql
sudo ufw allow from 198.51.100.7 to any port 5432 proto tcp

A UFW rule protects only while UFW is on. Check it: sudo ufw status Before turning it on over SSH, let SSH through: sudo ufw allow OpenSSH On a cloud server, open port 5432 in the provider’s firewall for the same single address. Never use 0.0.0.0/0 or trust in pg_hba.conf.

If PostgreSQL doesn’t let you in

FATAL: Peer authentication failed for user "postgres": you ran psql with -U postgres as your own system user. For an app role, add -h localhost so psql asks for its password. As postgres, open the console this way: sudo -u postgres psql
FATAL: password authentication failed for user "app": the password is wrong. Set a new one: sudo -u postgres psql -c "ALTER USER app WITH PASSWORD 'choose-a-strong-password';"
FATAL: no pg_hba.conf entry for host "198.51.100.7", user "app", database "app": the server was reached, but no line in pg_hba.conf matches. Add one as above and apply it: sudo systemctl reload postgresql
Connection refused with Is the server running on that host and accepting TCP/IP connections?: the server is down or listens only on localhost. Check pg_lsclusters, listen_addresses and the firewall.
ERROR: permission denied for schema public: the role doesn’t own the database. Make it the owner: sudo -u postgres psql -c "ALTER DATABASE app OWNER TO app;"
The status of the postgresql service says active (exited): that is normal. This unit only starts the clusters. The server itself is postgresql@16-main, and the log is in /var/log/postgresql/.

If you also work on a Mac

MacWashing, System Junk: Xcode junk, user caches, installers and logs found, 22.83 GB ready to be cleaned

System Junk in MacWashing finds the caches of every app it knows, including those hidden inside app data, plus logs, Xcode junk and leftovers of deleted apps. It shows how much each takes and clears them in one go. Anything debatable stays unchecked until you decide.

Download MacWashing How it works

Free trial · macOS 14 or later

Questions and answers

How do I install PostgreSQL on Ubuntu?

Update the package list: sudo apt update Then install PostgreSQL: sudo apt install postgresql Ubuntu 24.04 installs PostgreSQL 16 and 22.04 installs 14. For a newer version, add the official repository from postgresql.org.

What is the default password for the postgres user?

There is none. The postgres role logs in locally through peer: sudo -u postgres psql If a tool needs a password, set one inside psql: \password postgres

Where are PostgreSQL’s settings and data on Ubuntu?

Settings are in /etc/postgresql/16/main/ (postgresql.conf and pg_hba.conf), data in /var/lib/postgresql/16/main/, logs in /var/log/postgresql/. The number is your version.