How to Install PostgreSQL on Ubuntu 24.04

Introduction

PostgreSQL is an open-source advanced Relational Database Management System (RDBMS) designed to handle a wide range of data management tasks. It supports the Structured Query Language (SQL) to manage data in small and large enterprise applications such as analytical systems, Geographic Information Systems (GIS), healthcare applications, and dynamic web applications.

This article explains how to install PostgreSQL on a Ubuntu 24.04 server. You will enable the PostgreSQL database server and secure it for production use on your server.

Prerequisites

Before you begin:

• Deploy a Ubuntu 24.04 server instance on Vultr.
• Access the server using SSH.
• Create a non-root user with sudo privileges and switch to the user.
• Update the server.

Install PostgreSQL

PostgreSQL is available in the default APT repositories on Ubuntu. Follow the steps below to install the PostgreSQL database server packages and enable the application to start at boot time.

1. Install the postgresql-common dependency package on your server.

   CONSOLE

   Copy

   $ sudo apt install -y postgresql-common -y

   Explain Code

   Powered by Vultr AgentBeta

2. Run the following command to execute the PostgreSQL APT repository script.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   Press Enter when prompted to add the new repository to your server sources.

   This script will enable the PostgreSQL APT repository on apt.postgresql.org on
   your system. The distribution codename used will be noble-pgdg.
   
   Press Enter to continue, or Ctrl-C to abort.

3. Install the postgresql database server package.

   CONSOLE

   Copy

   $ sudo apt install -y postgresql

   Explain Code

   Powered by Vultr AgentBeta

4. Start the PostgreSQL database server.

   CONSOLE

   Copy

   $ sudo systemctl restart postgresql

   Explain Code

   Powered by Vultr AgentBeta

5. View the PostgreSQL system service status and verify that it's active.

   CONSOLE

   Copy

   $ sudo systemctl status postgresql

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   ● postgresql.service - PostgreSQL RDBMS
       Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
       Active: active (exited) since Mon 2024-05-27 16:09:21 UTC; 35s ago
       Process: 5601 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 5601 (code=exited, status=0/SUCCESS)
           CPU: 3ms

Secure the PostgreSQL Database Server

PostgreSQL runs with the default postgres privileged database user account on your server. Follow the steps below to enable password authentication and secure the PostgreSQL database server to grant only authorized users access to databases.

1. Log in to the PostgreSQL database server using the postgres user account.

   CONSOLE

   Copy

   $ sudo -u postgres psql

   Explain Code

   Powered by Vultr AgentBeta

2. Modify the default postgres user with a new strong password.

   SQL

   Copy

   postgres=#  ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';

   Explain Code

   Powered by Vultr AgentBeta

3. Create a new user db_manager with a new strong password.

   SQL

   Copy

   postgres=#  CREATE USER db_manager ENCRYPTED PASSWORD 'strong_password';

   Explain Code

   Powered by Vultr AgentBeta

4. Exit the PostgreSQL console.

   SQL

   Copy

   postgres=#  \q

   Explain Code

   Powered by Vultr AgentBeta

5. Run the following command to change the default peer value in the scram-sha-256 field in the main PostgreSQL configuration file pg_hba.conf to enable password authentication on the server.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

6. Restart the PostgreSQL server to apply the new configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart postgresql

   Explain Code

   Powered by Vultr AgentBeta

Access the PostgreSQL Database Server

You can access the PostgreSQL database console using the psql utility that is pre-installed with the server package. In addition, you can access the database server using compatible graphical tools that create a direct connection to the console. Follow the steps below to access your PostgreSQL database server console and create a new sample database to use with your non-privileged user.

1. Create a new sample PostgreSQL database hospital and grant the db_manager user ownership privileges to the database.

   CONSOLE

   Copy

   $ sudo -u postgres createdb hospital -O db_manager

   Explain Code

   Powered by Vultr AgentBeta

   When prompted, enter the Postgres user password you created earlier.

2. Log in to the PostgreSQL database as the user db_manager to test access to the hospital database.

   CONSOLE

   Copy

   $ sudo -u postgres psql -U db_manager -d hospital

   Explain Code

   Powered by Vultr AgentBeta

   Enter the database user password when prompted and press Enter to access the database.

3. Create a new sample doctors table.

   SQL

   Copy

   psql > CREATE TABLE doctors (
              doctor_id SERIAL PRIMARY KEY,
              first_name VARCHAR(50),
              last_name VARCHAR(50),
              appointment_date DATE
          );

   Explain Code

   Powered by Vultr AgentBeta

   The above SQL statement creates a new table in the hospital database with the following columns:

   • doctor_id is a PRIMARY KEY that uniquely identifies each doctor in the doctors table.
   • first_name and last_name store names in the doctors table.
   • appointment_date stores the doctor's appointment date with a patient in the hospital.
   • SERIAL generates a new doctor_id for each new record.

4. Insert sample data into the doctors table.

   SQL

   Copy

   psql > INSERT INTO doctors
          ( first_name, last_name, appointment_date)
          VALUES
          ( 'Ben', 'Joe', '2024-11-15'),
          ( 'Carson', 'Smith', '2024-02-28'),
          ( 'Donald', 'James', '2024-04-10');

   Explain Code

   Powered by Vultr AgentBeta

5. Query the doctors table to view all available records.

   SQL

   Copy

   psql > SELECT * FROM doctors;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   SQL

   Copy

   doctor_id | first_name | last_name | appointment_date
   -----------+------------+-----------+------------------
          1 | Ben        | Joe       | 2024-11-15
          2 | Carson     | Smith     | 2024-02-28
          3 | Donald     | James     | 2024-04-10
   (3 rows)

   Explain Code

   Powered by Vultr AgentBeta

6. Exit the PostgreSQL console.

   SQL

   Copy

   psql > \q

   Explain Code

   Powered by Vultr AgentBeta

Conclusion

You have installed PostgreSQL on your Ubuntu 24.04 server and accessed the database server console using the psql utility to create sample databases and table records. You can further integrate the PostgreSQL database server with your existing applications to securely enable the creation and management of application database records. For more information and configuration options, visit the official PostgreSQL documentation.