Skip to main content

Databases · Beginner · 11 min

How to Install PostgreSQL 18 on Ubuntu 26.04 and Create Your First Database

By Padding · Updated 15 Nov 2026
Ubuntu 26.04.1 LTS · PostgreSQL 18 (PGDG apt)

You will install PostgreSQL 18 from the official PostgreSQL apt repository, create a database and a user for your app, and connect to it with a password.

On this page

PostgreSQL is an open-source relational database. Ubuntu 26.04 ships its own PostgreSQL 18 packages (the archive had 18.6 on 15 November 2026), and the PostgreSQL project also runs an apt repository, called PGDG, that carries every supported major version and its minor releases the day they come out. This tutorial uses PGDG, so the same steps keep working when you later need a different major version side by side.

In this tutorial you will add the repository, install PostgreSQL 18, create a database user and a database for an app, and connect with a password. At the end you will know where the main configuration files are and why the database is closed to the network by default.

Step 1 · Add the PostgreSQL apt repository

The postgresql-common package contains a script that adds the PGDG repository and its signing key. Install the package, then run the script.

Bash
sudo apt-get update
sudo apt-get install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

The script ends with You can now start installing packages from apt.postgresql.org. It writes the repository definition to /etc/apt/sources.list.d/pgdg.sources. Without -y, it asks you to confirm first.

Check it worked

Bash
cat /etc/apt/sources.list.d/pgdg.sources
apt-cache policy postgresql-18

The source file lists https://apt.postgresql.org/pub/repos/apt with the suite resolute-pgdg. The Candidate line of apt-cache policy shows a version starting with 18., ending in .pgdg26.04+....

Step 2 · Install PostgreSQL 18

Bash
sudo apt-get install -y postgresql-18

The package creates a database cluster named main for you, owned by the system user postgres. List it:

Bash
pg_lsclusters

The line reads 18 main 5432 ... followed by the status, the owner and the data directory /var/lib/postgresql/18/main. The status is online, because the service starts when the package is installed.

Confirm that with systemd:

Bash
systemctl status postgresql

If the service is not running, sudo systemctl enable --now postgresql starts it and turns it on for every boot.

Check it worked

Bash
sudo -u postgres psql -c 'SELECT version();'

The output starts with PostgreSQL 18. and the build information. The system user postgres is the database superuser, and Linux authenticates it by account name over the local socket, with no password. To open an interactive session as that user, run sudo -u postgres psql. At the postgres=# prompt, \q quits.

Step 3 · Create a user and a database

Do not let your app connect as postgres. Create a role that can log in and owns one database. Replace appuser and appdb with your own names. Open a session as the superuser:

Bash
sudo -u postgres psql

At the postgres=# prompt, create the role and the database, then set the password with \password. That command prompts you twice and sends only a hash to the server, so the password does not end up in your shell history or in the server's logs.

SQL
CREATE ROLE appuser WITH LOGIN;
CREATE DATABASE appdb OWNER appuser;
\password appuser

CREATE ROLE and CREATE DATABASE each print their own name back. PostgreSQL stores the password as a SCRAM-SHA-256 hash, not as text.

Check it worked

List the databases:

Bash
sudo -u postgres psql -c '\l'

appdb is in the list, with appuser as owner, next to postgres, template0 and template1. Leave the templates alone: new databases are copies of them.

Step 4 · Connect as the new user

Connect with the new role over TCP on the local address. psql asks for the password you set. To avoid typing it each time, keep it in ~/.pgpass, a file readable only by you (chmod 600 ~/.pgpass).

Bash
psql -h 127.0.0.1 -U appuser -d appdb

At the appdb=> prompt, create a table, add a row and read it back:

SQL
CREATE TABLE notes (id serial PRIMARY KEY, body text NOT NULL);
INSERT INTO notes (body) VALUES ('first note');
SELECT * FROM notes;

Check it worked

The last query prints a table like this. Type \q to leave psql.

Text
 id |    body    
----+------------
  1 | first note
(1 row)

Troubleshooting

  • psql: error: connection to server on socket ... failed: No such file or directory: the cluster is not running. Check with pg_lsclusters, and start it with sudo systemctl start postgresql.
  • FATAL: Peer authentication failed for user "appuser": you connected over the local socket (no -h), and the local line in pg_hba.conf uses peer, which demands that your Linux user name match the database user. Add -h 127.0.0.1 to use a password over TCP.
  • FATAL: password authentication failed: the password is wrong. Reset it with ALTER ROLE appuser WITH PASSWORD 'new-password';.
  • psql: command not found: the client is in the postgresql-client-18 package, which postgresql-18 pulls in. Reinstall it with sudo apt install postgresql-client-18.

Step 5 · Know what is open to the network

By default PostgreSQL listens only on localhost. Look at the setting and at the rules that decide who may connect:

Bash
grep -E '^#?listen_addresses' /etc/postgresql/18/main/postgresql.conf
sudo grep -E '^(local|host)' /etc/postgresql/18/main/pg_hba.conf

The first line shows listen_addresses = 'localhost' commented out, which is the default. The second command lists the rules in pg_hba.conf. Each line says: connection type, database, user, address and method. Out of the box, local-socket connections use peer, and TCP connections from 127.0.0.1 and ::1 use scram-sha-256, a password check.

The files live here:

| Path | What it holds |
|---|---|
| /etc/postgresql/18/main/postgresql.conf | Server settings, such as listen_addresses and port |
| /etc/postgresql/18/main/pg_hba.conf | Rules for who may connect, from where and how |
| /var/lib/postgresql/18/main | The data directory |
| /var/log/postgresql/postgresql-18-main.log | The server log |

To let an app on another machine connect, you change two things: listen_addresses in postgresql.conf and a host line in pg_hba.conf for that machine's address, then sudo systemctl restart postgresql. Do this only when you need to. Allow just the app server's address, never 0.0.0.0/0, and keep port 5432 closed in the firewall to everyone else.

Next steps

  • Make backups, and prove you can restore them: /tutorials/back-up-and-restore-postgresql-with-pg-dump.
  • Find slow queries: /tutorials/speed-up-slow-postgres-queries-with-explain.
  • Keep the server itself safe: /tutorials/harden-ssh-on-ubuntu-26-04.
  • The PGDG repository instructions are at wiki.postgresql.org/wiki/Apt.

Need this done for you?

Padding does this work for growing companies, from the first diagnosis to a system that keeps working.

Platform Engineering

Get new tutorials

New tutorials, about twice a month.