hieuvnlabs logo
Hiusvu
Table Of Content

How To Install PostgreSQL on Ubuntu

Published on September 5, 2026

How To Install PostgreSQL on Ubuntu

Introduction

PostgreSQL (often just Postgres) is an open-source relational database known for its stability, strict SQL compliance and advanced features such as JSON data types, full-text search and extensions. It is the default choice for many modern frameworks and platforms, including Django, Rails, Prisma and Supabase.

In this guide we will install PostgreSQL on Ubuntu, look at how Postgres handles authentication, create a role and database, and practise the basic table operations.

Prerequisites

  • A server running Ubuntu 20.04 or 22.04.
  • A regular user with sudo privileges.

Step 1 — Installing PostgreSQL

PostgreSQL is available in Ubuntu's default repositories. Update the package index and install the postgresql package along with postgresql-contrib, which adds some extra utilities:

sudo apt update
sudo apt install postgresql postgresql-contrib

The service starts automatically after installation. Check its status:

sudo systemctl status postgresql
Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2026-09-05 10:02:14 UTC; 1min 3s ago
   Main PID: 5183 (code=exited, status=0/SUCCESS)

The active (exited) state is normal: postgresql.service is only a wrapper, the actual server runs as postgresql@14-main.service (the version number may differ on your machine).

If you want a newer PostgreSQL release than the one in Ubuntu's repository, add the official PostgreSQL (PGDG) repository:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install postgresql-16

Step 2 — Understanding PostgreSQL Roles and Authentication

Postgres uses the concept of roles to handle authentication and authorisation. A role can be thought of as a user or a group of users.

By default Postgres uses peer authentication for local connections: if the operating system username matches a Postgres role name, that user can log in without a password.

The installation creates an operating system account called postgres, which corresponds to the default postgres administrative role. There are two ways to use it.

Option 1: switch to the postgres account, then open the prompt:

sudo -i -u postgres
psql

Option 2: run psql directly as the postgres account without switching shells:

sudo -u postgres psql

Either way you land in the PostgreSQL prompt:

Output
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

To exit the prompt, type:

\q

Step 3 — Creating a New Role

Right now only the postgres role exists. It is good practice to create a separate role for each application rather than using the administrative role.

From the postgres account, use createuser with the --interactive flag to be prompted for the options:

sudo -u postgres createuser --interactive
Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Alternatively, create the role with a password from inside psql. You need this if your application connects with a password:

sudo -u postgres psql
CREATE ROLE sammy WITH LOGIN PASSWORD 'password';
ALTER ROLE sammy CREATEDB;

Step 4 — Creating a New Database

By default, when a role logs in, Postgres tries to connect to a database with the same name as the role. So create a database matching the role you just made:

sudo -u postgres createdb sammy

Or create a database with any name and assign the role as its owner:

CREATE DATABASE myapp OWNER sammy;

On PostgreSQL 15 and later, roles no longer have permission to create tables in the default public schema. If you hit permission denied for schema public, grant it:

GRANT ALL ON SCHEMA public TO sammy;

Step 5 — Opening a Prompt With the New Role

To authenticate with peer authentication you need an operating system user with the same name as the Postgres role. If it does not exist, create it:

sudo adduser sammy

Then switch to that user and open the prompt:

sudo -i -u sammy
psql

To connect to a database with a different name than the role:

psql -d myapp

Check the current connection details:

\conninfo
Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

If you would rather not create an operating system user, connect with a password over TCP instead:

psql -h localhost -U sammy -d myapp

Step 6 — Creating and Deleting Tables

Now that you know how to connect, let's practise the basics. Create a playground table to store playground equipment:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

What this does:

  • equip_id is a serial, an auto-incrementing integer used as the primary key.
  • type and color are length-limited strings that must have a value.
  • location has a check constraint allowing only one of eight preset values.
  • install_date is a date.

List the tables:

\dt
Output
            List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Describe the table's structure:

\d playground

Step 7 — Adding, Querying, Updating and Deleting Data

Insert two rows:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2024-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2024-08-16');

Query the whole table:

SELECT * FROM playground;
Output
 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2024-04-28
        2 | swing | yellow | northwest | 2024-08-16
(2 rows)

Change the swing's colour:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Delete the slide:

DELETE FROM playground WHERE type = 'slide';

Add and drop a column:

ALTER TABLE playground ADD last_maint date;
ALTER TABLE playground DROP last_maint;

Drop the table when you no longer need it:

DROP TABLE playground;

Step 8 — Allowing Remote Connections (Optional)

By default PostgreSQL only listens on localhost. If your application runs on another machine, some extra configuration is needed.

Open the main configuration file (replace 14 with your version):

sudo nano /etc/postgresql/14/main/postgresql.conf

Find the listen_addresses line, remove the # and set it to:

listen_addresses = '*'

Next, open the client authentication file:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the following line at the end to let the sammy role connect to the myapp database from your IP range using a password:

host    myapp    sammy    203.0.113.0/24    scram-sha-256

Restart PostgreSQL and open port 5432 on the firewall, only for a specific IP address:

sudo systemctl restart postgresql
sudo ufw allow from 203.0.113.5 to any port 5432

Warning: Never expose port 5432 to the whole internet. Restrict it by IP address or use an SSH tunnel or VPN.

Conclusion

You have installed PostgreSQL, learned how roles and peer authentication work, created a dedicated role and database for your application, and practised basic CRUD operations. Next, look into backups with pg_dump, performance tuning with indexes, or using PostgreSQL with ORMs such as Prisma and Drizzle.