How to Install PostgreSQL on Ubuntu 24.04 LTS

In this article we will cover How to Install PostgreSQL on Ubuntu 24.04 LTS, Configure PostgreSQL on Ubuntu 24.04 LTS, and Secure PostgreSQL on Ubuntu 24.04 LTS.

PostgreSQL, often referred to as Postgres, is a powerful, open-source relational database management system known for its robustness, performance, and advanced features. As a popular choice among developers and enterprises.

PostgreSQL supports a wide range of data types and performance optimization features, making it ideal for handling complex data relationships and mission-critical applications. In this guide, we will walk you through the steps to install PostgreSQL on Ubuntu 24.04 (Noble Numbat), ensuring you have a reliable and efficient database system up and running on your server.

Prerequisites

• SSH Access with admin privileges
• Ubuntu 24.04 LTS with minimal installation

Install PostgreSQL on Ubuntu 24.04 LTS

Update Your System:

sudo apt update

Install PostgreSQL using the package manager:

sudo apt install -y postgresql-common -y

execute the PostgreSQL APT repository script:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Install the PostgreSQL database server package:

sudo apt install -y postgresql

Start the PostgreSQL database server:

sudo systemctl restart postgresql

Verify the status:

sudo systemctl status postgresql

Secure the PostgreSQL Database Server on Ubuntu 24.04 LTS

Log in to the PostgreSQL database server:

sudo -u postgres psql

Change the Default Password: 

ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';

Create a new user db_manager with a new password:

CREATE USER db_manager ENCRYPTED PASSWORD 'strong_password';

Exit the console:

cntrl+d

To change the default peer value to scram-sha-256 in the pg_hba.conf file and enable password authentication on the server, run the following command:

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

Restart the server:

sudo systemctl restart postgresql

Access the PostgreSQL Database Server command line

To create a new sample table called 'doctors‘ in PostgreSQL on Ubuntu 24.04, follow these steps:

Switch to the postgres user (the default PostgreSQL superuser):

sudo -i -u postgres

Access the PostgreSQL prompt:

psql

after giving above command it will ask for the password

Create a Database: If you don’t have a database yet, you can create one first. This is optional if you’re using the default postgres database.

CREATE DATABASE sample_db;

Connect to the newly created database:

\c sample_db

Create the doctors Table:

Once you’re connected to your desired database, you can create the doctors table.

Create the doctors table with columns such as id, name, specialization, experience_years, and phone:

CREATE TABLE doctors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100) NOT NULL,
    experience_years INT NOT NULL,
    phone VARCHAR(15)
);

1. id SERIAL PRIMARY KEY: Auto-incrementing primary key.
2. name VARCHAR(100): Doctor’s name (string of up to 100 characters).
3. specialization VARCHAR(100): Doctor’s specialization (string of up to 100 characters).
4. experience_years INT: Years of experience (integer).
5. phone VARCHAR(15): Doctor’s phone number (optional).

Insert Sample Data into the doctors Table:

You can now insert some sample data into the table to test it.

INSERT INTO doctors (name, specialization, experience_years, phone)
VALUES
('Dr. Alice Smith', 'Cardiologist', 10, '123-456-7890'),
('Dr. Bob Johnson', 'Neurologist', 8, '098-765-4321'),
('Dr. Carol Brown', 'Pediatrician', 5, '567-890-1234');

Query the Table:

After inserting data, you can query the table to verify the records:

SELECT * FROM doctors;

Exit the PostgreSQL Prompt:

To exit the PostgreSQL command-line interface, type:

\q

Conclusion:

Installing PostgreSQL on Ubuntu 24.04 is a straightforward process that involves updating your system, installing the PostgreSQL package, and configuring it for secure and efficient use. By following these steps, including securing the default user and enabling remote access if needed, you can set up a reliable PostgreSQL database server. Whether for personal projects or production environments, PostgreSQL offers a robust and scalable solution for managing your databases on Ubuntu.

Related Articles:

How to Install Postgresql on Ubuntu 22.04 LTS

Reference:

PostgreSQL official page