How to Install and Use PostgreSQL 18 on Ubuntu 24.04 LTS

Bywiredgorilla

PostgreSQL (Postgres in short) is an open source, powerful, advanced, high-performance, and stable relational-document database system, which extends the SQL language and includes a wide range of features for secure data storage and management.

It is efficient, reliable, and scalable for handling large, complicated volumes of data and setting up enterprise-level and fault-tolerant environments, while ensuring high data integrity.

Postgres is also highly extensible with features such as advanced indexing, full-text search, and comes with APIs so that you can develop your own solutions to solve your data storage challenges.

In this article, we will explain how to install PostgreSQL 18 (which was just released on September 25, 2025) on an Ubuntu 24.04 LTS server and learn essential ways to use it effectively.

How to Install PostgreSQL on Ubuntu 24.04

PostgreSQL comes prepackaged with all Ubuntu versions by default. However, Ubuntu includes a specific “snapshot” of PostgreSQL that remains fixed for the entire lifecycle of that Ubuntu release.

If you want access to newer PostgreSQL versions, then you can use the following automated repository configuration script that automatically set up the official PostgreSQL Apt repository.

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

Now update the package list and install PostgreSQL (the latest version is PostgreSQL 18 as of today):

sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18
Install PostgreSQL 18 in Ubuntu
Install PostgreSQL 18 in Ubuntu

Install pgAdmin 4 for PostgreSQL Administration

If you prefer a graphical interface to manage your PostgreSQL databases, you can install pgAdmin 4, the official PostgreSQL management tool that allows you to manage databases, users, queries, and more – all from a web-based dashboard.

On Ubuntu 24.04, pgAdmin 4 isn’t available in the default repositories, so we’ll use the official pgAdmin repository.

sudo apt install -y curl ca-certificates gnupg
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Once the pgAdmin repository has been added, you can install pgAdmin 4 as shown.

# Install for both desktop and web modes
sudo apt install pgadmin4

# Install for desktop mode only
sudo apt install pgadmin4-desktop

# Install for web mode only 
sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

After the installation, the PostgreSQL service should start automatically, and you can confirm its status by running:

sudo systemctl status postgresql

To enable PostgreSQL to start on boot (if not already enabled):

sudo systemctl enable postgresql

After running this, you can open pgAdmin in your browser at:

http://127.0.0.1/pgadmin4
pgAdmin Management Tool for PostgreSQL
pgAdmin Management Tool for PostgreSQL

How to Use PostgreSQL Roles and Databases

In PostgreSQL, client authentication is controlled by the /etc/postgresql/18/main/pg_hba.conf configuration file (the version number may differ based on your installation).

The default authentication method is "peer" for the database administrator, meaning it gets the client’s operating system user name and checks if it matches the requested database user name to allow access for local connections.

During the installation process, a system user account called postgres was created without a password, which is also the default database administrator user name.

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

Understanding PostgreSQL Roles

In PostgreSQL, database access permission management is performed via roles. A role can be considered as either a database user, or a group of database users, depending on how the role is set up.

The default role is also postgres. Importantly, database roles are conceptually fully unconnected to operating system users, but practically they may be linked (particularly for authentication purposes).

Roles can:

  • Own database objects (tables, views, functions, etc.).
  • Assign privileges on those objects to other roles.
  • Grant membership in a role to another role (role inheritance).

Enable Encrypted Passwords for PostgreSQL Roles

To configure roles to use encrypted passwords instead of peer authentication, you need to modify the pg_hba.conf file. Change the authentication method from peer to scram-sha-256 (the modern secure method) or md5 for password authentication.

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

Find the lines that look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

And change to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256

This keeps peer authentication for the postgres user but requires passwords for other users.

Configure PostgreSQL Roles with Encrypted Passwords
Configure PostgreSQL Roles with Encrypted Passwords

Then restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

How to Use PostgreSQL on Ubuntu

Once everything is set up, you can access the postgres system account with the following command, where the -i flag tells sudo to run the shell specified by the target user’s password database entry as a login shell.

sudo -i -u postgres
psql    # to launch the postgres shell program

To access the postgres shell directly, without first accessing the postgres user account, run:

sudo -u postgres psql

You can quit/exit the postgres shell by typing q or pressing Ctrl+D.

q
Logging into PostgreSQL System Account
Logging into PostgreSQL System Account

Create PostgreSQL Database Roles

Create a new user role using the following command:

CREATE ROLE tecmint;

To create a role with a LOGIN attribute (roles with the LOGIN attribute can be considered the same as a database users):

CREATE ROLE tecmint LOGIN;

Or use the CREATE USER command, which assumes the login function by default:

CREATE USER tecmint;

Create a Role with a Password

A role can also be created with a password, which is essential if you configured the client authentication method to require encrypted passwords:

CREATE ROLE tecmint WITH LOGIN PASSWORD 'secure_password_here';

Or using the CREATE USER syntax:

CREATE USER tecmint WITH PASSWORD 'secure_password_here';

Create a Role with Additional Privileges

You can create a role with superuser privileges (use carefully):

CREATE ROLE admin WITH LOGIN PASSWORD 'admin_password' SUPERUSER;

Or create a role that can create databases:

CREATE ROLE developer WITH LOGIN PASSWORD 'dev_password' CREATEDB;

List Existing PostgreSQL Database Roles

To list the existing user roles, use any of these commands:

du              -- shows actual users with details

Or:

SELECT rolname FROM pg_roles;

To see more detailed information:

du+

Modify PostgreSQL Database Roles

To change a role’s password:

ALTER ROLE tecmint WITH PASSWORD 'new_password';

To grant superuser privileges to an existing role:

ALTER ROLE tecmint WITH SUPERUSER;

To revoke superuser privileges:

ALTER ROLE tecmint WITH NOSUPERUSER;

Drop a PostgreSQL Database Role

To drop an existing user role, use the DROP ROLE command:

DROP ROLE tecmint;

Note: You cannot drop a role that owns database objects, you must first reassign or drop those objects.

Create a PostgreSQL Database

Once you have created a role with a particular name (for instance tecmint user), you can create a database which will be managed by that role:

CREATE DATABASE tecmint;

To create a database owned by a specific role:

CREATE DATABASE tecmint OWNER tecmint;

To create a database with a specific encoding:

CREATE DATABASE tecmint ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' OWNER tecmint;

Grant Privileges to a Role

After creating the database, grant all privileges to the role:

GRANT ALL PRIVILEGES ON DATABASE tecmint TO tecmint;

Now to manage the database tecmint, access the postgres shell as the tecmint role:

psql -U tecmint -d tecmint

If you’re prompted for a password, enter the password you set for the role.

Create a PostgreSQL Table

We’ll create a test table called authors, which stores information about TecMint.com authors:

CREATE TABLE authors (
    code      SERIAL PRIMARY KEY,
    name      VARCHAR(40) NOT NULL,
    city      VARCHAR(40) NOT NULL,
    joined_on DATE NOT NULL
);

Note: We’re using SERIAL for the primary key, which auto-generates sequential numbers, which is more practical than manually assigning codes.

Insert Data into PostgreSQL Table

After creating a table, populate it with some data:

INSERT INTO authors (name, city, joined_on) VALUES 
    ('Ravi Saive', 'Mumbai', '2012-08-15'),
    ('Aaron Kili', 'Nairobi', '2014-03-20'),
    ('Matei Cezar', 'Bucharest', '2015-06-10');

Query Data from PostgreSQL Table

To view the data stored in a table, run a SELECT command:

SELECT * FROM authors;

For specific columns:

SELECT name, city FROM authors;

With filtering:

SELECT * FROM authors WHERE city = 'Mumbai';

With ordering:

SELECT * FROM authors ORDER BY joined_on DESC;

Update Data in PostgreSQL Table

To modify existing data:

UPDATE authors SET city = 'Delhi' WHERE name = 'Ravi Saive';

Delete Data from PostgreSQL Table

To remove specific rows:

DELETE FROM authors WHERE name = 'Ravi Saive';

List PostgreSQL Database Tables

You can list all tables in the current database with:

dt

For more detailed information:

dt+

To see the table structure:

d authors

Alter PostgreSQL Table Structure

To add a new column to an existing table:

ALTER TABLE authors ADD COLUMN email VARCHAR(100);

To drop a column:

ALTER TABLE authors DROP COLUMN email;

To rename a column:

ALTER TABLE authors RENAME COLUMN code TO author_id;

Delete/Drop a PostgreSQL Table

To delete a table in the current database:

DROP TABLE authors;

To drop with cascade (removes dependent objects):

DROP TABLE authors CASCADE;

List All PostgreSQL Databases

To list all databases, use any of the following commands:

SELECT datname FROM pg_database;

Or for a detailed description:

list

Or the shorthand:

l

Delete/Drop a PostgreSQL Database

If you want to delete a database:

DROP DATABASE tecmint;

Warning: This permanently deletes all data in the database.

Switch to Another PostgreSQL Database

You can switch from one database to another easily:

connect database_name

Or the shorthand:

c database_name

Configure PostgreSQL for Remote Access

By default, PostgreSQL only accepts connections from localhost.

To allow remote connections, you need to edit postgresql.conf file.

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

Find the line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Next, edit pg_hba.conf file.

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

Add a line to allow connections from your network (replace 192.168.1.0/24 with your network):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256

Or to allow from any IP (less secure):

host    all             all             0.0.0.0/0               scram-sha-256

Allow PostgreSQL through the firewall:

sudo ufw allow 5432/tcp

Finally, restart PostgreSQL.

sudo systemctl restart postgresql

Basic PostgreSQL Performance Tuning

For better performance on Ubuntu 24.04, consider adjusting these settings in postgresql.conf:

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

Recommended changes (adjust based on your server’s RAM):

shared_buffers = 256MB              # 25% of RAM
effective_cache_size = 1GB          # 50-75% of RAM
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB

After changes, restart PostgreSQL:

sudo systemctl restart postgresql

Backup and Restore PostgreSQL Databases

To backup a single database:

pg_dump -U postgres tecmint > tecmint_backup.sql

To backup all databases.

pg_dumpall -U postgres > all_databases_backup.sql

To restore a database.

psql -U postgres tecmint < tecmint_backup.sql

Useful PostgreSQL Commands Reference

Here’s a quick reference of commonly used PostgreSQL commands:

Command Description
l or list List all databases
c dbname Connect to a database
dt List all tables
d tablename Describe table structure
du List all roles/users
dn List all schemas
df List all functions
dv List all views
timing Toggle query timing
x Toggle expanded display
i filename Execute commands from a file
q Quit psql
h Help on SQL commands
? Help on psql commands
Conclusion

That’s it! In this article, we have explained how to install and use the PostgreSQL database management system on Ubuntu 24.04 LTS.

We covered installation, user management, database operations, remote access configuration, performance tuning, and backup strategies. You can send us your queries or thoughts in the comments below.

For more information, refer to the PostgreSQL 18 Official Documentation, or explore useful websites for learning PostgreSQL.

Similar Posts