DEV Community

topeogunleye
topeogunleye

Posted on • Edited on

How to Install PostgreSQL 18 on Ubuntu 24.04

#postgres #database #programming #webdev

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 Update Your Package Index again

The reason you have to update your package index again after adding the PostgreSQL repository (PGDG repo) is because APT needs to become aware of the new software sources you just added.

sudo apt update
Enter fullscreen mode Exit fullscreen mode

5. Install PostgreSQL Version 18:

This command below Installs specifically PostgreSQL Version 18

sudo apt -y install postgresql-18
Enter fullscreen mode Exit fullscreen mode

Step 2 — Start, Enable, and Check Status

Start the service:

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Enable the service:

sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

Check the current status:

sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  1. Modify the default postgres user with a new strong password: 
ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';
Enter fullscreen mode Exit fullscreen mode
  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';
Enter fullscreen mode Exit fullscreen mode
  1. Exit the PostgreSQL console: 
\q
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode
  1. Restart PostgreSQL to apply changes: 
sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Step 4 — Creating a New Role

1. Switching to the postgres Account 

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

With login + password:

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

createuser new_username --interactive --pwprompt
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • Database Creation Privilege: You use the command below to allow the role to create new databases. 
ALTER ROLE new_username WITH CREATEDB;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  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 }
Enter fullscreen mode Exit fullscreen mode

Examples:

ALTER DATABASE mydb OWNER TO new_owner;
ALTER DATABASE mydb OWNER TO CURRENT_USER;
Enter fullscreen mode Exit fullscreen mode
  1. Move the databases file to a different storage 
ALTER DATABASE name SET TABLESPACE new_tablespace;
Enter fullscreen mode Exit fullscreen mode

Example:

ALTER DATABASE mydb SET TABLESPACE fast_ssd;
Enter fullscreen mode Exit fullscreen mode
  1. Refresh collation metadata used by the database to match the system libc locale/collation version. 
ALTER DATABASE name REFRESH COLLATION VERSION;
Enter fullscreen mode Exit fullscreen mode

Example:

ALTER DATABASE mydb REFRESH COLLATION VERSION;
Enter fullscreen mode Exit fullscreen mode

Step 7 — Accessing the Postgres Prompt With New Role

  1. Create a matching Linux user: 
sudo adduser johndoe
Enter fullscreen mode Exit fullscreen mode
  1. Switch over and connect: 
sudo -i -u johndoe
psql
Enter fullscreen mode Exit fullscreen mode

Or:

sudo -u sammy psql
Enter fullscreen mode Exit fullscreen mode
  1. Check connection info: 
\conninfo
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

Sample table:

CREATE TABLE animals (
    id serial PRIMARY KEY,
    perm_id VARCHAR(20) UNIQUE NOT NULL,
    species VARCHAR(50) NOT NULL,
    sex VARCHAR(10)
);
Enter fullscreen mode Exit fullscreen mode

Insert sample data:

INSERT INTO animals (id, perm_id, species, sex)
VALUES
('1', '401', 'Bird', 'm'),
('2', '403', 'Cattle', 'm'),
('3', '404', 'Fish', 'f');
Enter fullscreen mode Exit fullscreen mode

Query table:

SELECT * FROM animals;
Enter fullscreen mode Exit fullscreen mode

Delete table:

DROP TABLE IF EXISTS animals;
Enter fullscreen mode Exit fullscreen mode

Alter table:

ALTER TABLE table_name ALTER_OPTION sub_options;
Enter fullscreen mode Exit fullscreen mode

Exit:

\q
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
ayooluwa_isaiah profile image
Ayooluwa Isaiah •

You're missing a command before sudo apt install postgresql-18 which is updating the package index again

Collapse
 
topeogunleye profile image
topeogunleye •

Thank you very much, Mr Ayooluwa,