How to Install PostgreSQL 18 on Ubuntu 24.04

PostgreSQL, often called Postgres, is an advanced object-relational database management system. It is used because it is free, open-source, and known for its reliability, flexibility, and strong adherence to SQL standards. Some of its common uses include transaction processing, data analytics, mobile and web applications, geospatial applications, database and application modernization, generative ai.

PostgreSQL has remained open-source since its development at Berkeley. Being open-source means that the code is completely available for viewing, modification, and distribution.

You will install PostgreSQL on Ubuntu 24.04 using this guide.

Prerequisites

Before you begin, you will need:

• Ubuntu 24.04 Installed on your local machine or on a server.


• A non-root user with sudo privileges.


• To know how to use the Linux terminal.

Step 1 - Installing PostgreSQL

1. Update Your Package Index

Ubuntu’s default repositories come together with Postgres packages, so you can install these using the apt packaging system.

If you haven’t updated your system’s packages recently, run the command below:

sudo apt update

sudo apt upgrade

2. Add the PGDG repo

Add the PostgreSQL Global Development Group repo to get the latest stable PostgreSQL releases plus older supported versions. This will allow APT to know where to find PostgreSQL versions:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

What the Command above does:

• 
  $(lsb_release -cs) → It inserts the Ubuntu codename e.g noble for Ubuntu 24.04.


• 
  pgdg → tells it to use the PostgreSQL repository for that release.

3. Import the repository signing Keys:

Import the repository signing Keys (so APT knows it can trust packages from that repo)

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

What the command above does:

• 
  curl -fsSL https://.../ACCC4CF8.asc → downloads the PostgreSQL repository signing key (a GPG key).


• 
  gpg --dearmor → converts it from ASCII-armored format into binary format that APT can use.


• 
  o /etc/apt/trusted.gpg.d/postgresql.gpg → saves it in the directory where APT looks for trusted keys.

4. Install PostgreSQL Version 18:

This command below Installs specifically PostgreSQL Version 18

sudo apt -y install postgresql-18

Step 2 — Start, Enable, and Check Status

Start the service:

sudo systemctl start postgresql

Enable the service:

sudo systemctl enable postgresql

Check the current status:

sudo systemctl status postgresql

If the service is active, the output will show active (running).

Step 3 — Secure the PostgreSQL Database on Ubuntu (Local Machine)

1. Log in as the PostgreSQL user:
   

sudo -u postgres psql

1. Modify the default postgres user with a new strong password:
   

ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';

1. Create a new user db_manager with a new strong password:
   You shouldn’t use the default user “db_manager” for daily tasks. You should create a new user (e.g., db_manager) with its own password:
   

CREATE USER db_manager ENCRYPTED PASSWORD 'strong_password';

1. Exit the PostgreSQL console:
   

\q

1. Edit the main configuration file to enable password authentication:
   

sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/16/main/pg_hba.conf

1. Restart PostgreSQL to apply changes:
   

sudo systemctl restart postgresql

Step 4 — Creating a New Role

1. Switching to the postgres Account

sudo -u postgres psql

2. Create a New Role

Using createuser (interactive):

Now, that you have switched to the default postgres account, you can create a new user with the command below.:

createuser --interactive

Or if you want to create a new role without switching to the postgres account, use this command below and do it directly from your terminal

sudo -u postgres createuser --interactive

With login + password:

To create a basic user that can log in, you can run

createuser new_username --interactive --pwprompt

This command will interactively ask for the role name, a password, and what privileges to grant.

Using SQL (createrole):

A role without the LOGIN attribute cannot be used to log in. They are typically used as “group roles” to bundle a set of permissions together which can then be used to grant permissions to multiple user roles simplifying permission management

CREATE ROLE new_username WITH LOGIN PASSWORD 'a_secure_password';

Note: CREATE USER is an alias for CREATE ROLE ... WITH LOGIN.

1. Grant Privileges (Optional)
   You can grant specific privileges to a role either during its creation or afterward using the ALTER ROLE command.

From your terminal, switch to a superuser account, for example: a postgres account because only a superuser can grant SUPERUSER status

• 
  Superuser Priviledge:
  You use the command below to bypass all permission checks for a user:
  

ALTER ROLE new_username WITH SUPERUSER;

• 
  Database Creation Privilege:
  You use the command below to allow the role to create new databases.
  

ALTER ROLE new_username WITH CREATEDB;

• 
  Multiple Privileges at Once:
  Make use of the command below to assign these attributes directly when creating the role.
  

CREATE ROLE new_username WITH LOGIN PASSWORD 'a_secure_password' SUPERUSER CREATEDB;

Step 5 — Creating a New Database

Only a superuser can create a database and assign ownership to another role simultaneously. You must be a superuser or have the special CREATEDB privilege.

1. Switching to the postgres Account

If you aren’t current on a postgres or superuser account. use this command to do it:

sudo -u postgres psql

2. Create a New Database

Using createdb (Shell Command):

This command is a wrapper for the CREATE DATABASE command. Use the -O (uppercase O) flag to specify the new owner. This command should be run from your server's command line, not inside the psql shell.

createdb -O new_role new_database_name

Using SQL (inside psql):

From within the psql interface, you can use the CREATE DATABASE command with the OWNER clause.

CREATE DATABASE new_database_name OWNER new_role;

By default, if you do not specify an owner, the role executing the command becomes the owner of the new database. If you need to change the owner after creation, you can use the ALTER DATABASE command.

Step 6 — Altering Databases

Similarly to the CREATE DATABASE command, Only a superuser can Alter a database, still in the supperuser account that you just used.

1. Change name of database
   

ALTER DATABASE name RENAME TO new_name;

1. Change the owner i.e the role that owns the database:
   

ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

Examples:

ALTER DATABASE mydb OWNER TO new_owner;

ALTER DATABASE mydb OWNER TO CURRENT_USER;

1. Move the databases file to a different storage
   

ALTER DATABASE name SET TABLESPACE new_tablespace;

Example:

ALTER DATABASE mydb SET TABLESPACE fast_ssd;

1. Refresh collation metadata used by the database to match the system libc locale/collation version.
   

ALTER DATABASE name REFRESH COLLATION VERSION;

Example:

ALTER DATABASE mydb REFRESH COLLATION VERSION;

Step 7 — Accessing the Postgres Prompt With New Role

1. Create a matching Linux user:
   

sudo adduser johndoe

1. Switch over and connect:
   

sudo -i -u johndoe

psql

Or:

sudo -u sammy psql

1. Check connection info:
   

\conninfo

Step 8 — Creating and Deleting Tables

Basic syntax:

CREATE TABLE table_name (

    column_name1 col_type (field_length) column_constraints,

    column_name2 col_type (field_length),

    column_name3 col_type (field_length)

);

Sample table:

CREATE TABLE animals (

    id serial PRIMARY KEY,

    perm_id VARCHAR(20) UNIQUE NOT NULL,

    species VARCHAR(50) NOT NULL,

    sex VARCHAR(10)

);

Insert sample data:

INSERT INTO animals (id, perm_id, species, sex)

VALUES

('1', '401', 'Bird', 'm'),

('2', '403', 'Cattle', 'm'),

('3', '404', 'Fish', 'f');

Query table:

SELECT * FROM animals;

Delete table:

DROP TABLE IF EXISTS animals;

Alter table:

ALTER TABLE table_name ALTER_OPTION sub_options;

Exit:

\q

Conclusion

You have successfully installed PostgreSQL on your Ubuntu 24.04 machine. There is much more to learn about PostgreSQL including and not limited to: querying data on a table, adding and deleting columns from a table, and updating data in a table.