Introduction

PostgreSQL is a free, open-source relational database management system that offers advanced features and supports many programming languages like Python, Java, C++, PHP, Golang, and Node.js. The database server handles flexible data types and works on different operating systems. These features make PostgreSQL a powerful and versatile choice for developers and businesses.

This guide shows you how to install the PostgreSQL server on Ubuntu 24.04.

Prerequisites

Before you start:

• Deploy an Ubuntu 24.04 server. We recommend a Digital Ocean VPS server.
• Create a non-root user with sudo privileges. Read our guide on How to Create a Non-Root Sudo User on Ubuntu 24.04

Install PostgreSQL

The PostgreSQL package is available by default on the Ubuntu repositories. To install the package, follow the steps below:

1. Update your system's package information index.

   CONSOLE

   $ sudo apt update
   

2. Install the PostgreSQL package and the required dependencies.

   CONSOLE

   $ sudo apt install -y postgresql postgresql-contrib
   

Manage PostgreSQL Service

PostgreSQL runs as a system service on Ubuntu under the name postgresql. You can manage this service by using specific commands to start, stop, restart, and check its status. These commands ensure your PostgreSQL installation runs smoothly and make maintenance easier on your system.

Check PostgreSQL Status

$  sudo systemctl status postgresql

Output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Mon 2024-12-30 09:07:27 UTC; 1h 9min ago
    Process: 1143 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1143 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Press Ctrl + C.

Start the PostgreSQL Service

$  sudo systemctl start postgresql

Stop the PostgreSQL Service

$  sudo systemctl stop postgresql

Restart the PostgreSQL Service

$  sudo systemctl restart postgresql

Secure PostgreSQL Database Server

By default, you can log in to your PostgreSQL server using the postgres user account without a password on your localhost. To secure the postgres user account with a password, follow the steps below.

1. Log in to the PostgreSQL server.

   CONSOLE

   $ sudo -u postgres psql
   

2. Change the password.

   postgresql

   postgres=# \password postgres
   

3. Provide and confirm a new password.

   postgresql

   Enter new password: your_password_here
   Enter it again: your_password_here
   

4. Log out from the PostgreSQL server.

   postgresql

   postgres=# \q
   

5. Check the PostgreSQL version.

   CONSOLE

   $ pg_config --version
   

   Output:

   PostgreSQL 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)
   

6. Open the main PostgreSQL configuration file.

   CONSOLE

   $ sudo nano /etc/postgresql/16/main/pg_hba.conf
   

7. Locate the administrative login directive.

   INI

   # Database administrative login by Unix domain socket
   local   all             postgres                                peer
   
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   

8. Change peer to md5.

   INI

   # Database administrative login by Unix domain socket
   local   all             postgres                                md5
   
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   

9. Save and close the /etc/postgresql/16/main/pg_hba.conf file by pressing Ctrl + X + Y.

10. Restart the PostgreSQL service to load the new changes.

    CONSOLE

    $ sudo systemctl restart postgresql
    

Test PostgreSQL Database Server

In this section, you'll test your PostgreSQL installation by logging into the database server, creating a sample database and table, inserting some data, and querying the table to ensure everything functions correctly. This process helps verify that your installation works as expected and that you can seamlessly perform basic database operations.

1. Log in to the PostgreSQL server as postgres user and enter the password you defined.

   CONSOLE

   $ sudo -u postgres psql
   

2. Create a new company_db database.

   postgresql

   postgres=# CREATE DATABASE company_db;
   

   Output:

   postgresql

   CREATE DATABASE
   

3. List all databases and ensure your new database is available

   postgresql

   postgres=# \l
   

   Output:

   postgresql

   company_db
   postgres
   (... rows)
   

4. Press Q to exit from the list.

5. Connect to the new company_db.

   postgresql

   postgres=#  \c company_db;
   

   Output:

   postgresql

   You are now connected to database "company_db" as user "postgres".
   

6. Create a sample employees table.

   postgresql

   company_db-# CREATE TABLE employees (
       employee_id SERIAL PRIMARY KEY,
       full_name VARCHAR (100),
       email VARCHAR (255)         
   );
   

   Output:

   postgresql

   CREATE TABLE
   

7. Populate the employees table with sample records.

   postgresql

   company_db=# INSERT INTO employees(full_name, email) VALUES ('MARY ROE', 'mary_roe@example.com');
                INSERT INTO employees(full_name, email) VALUES ('JAY SMITH', 'jay_smith@example.com');
   

   Output:

   postgresql

   ...
   INSERT 0 1
   

8. Query the employees table to verify the records.

   postgresql

   company_db=# SELECT
       employee_id,
       full_name,
       email
   FROM employees;
   

   Output:

   postgresql

   employee_id | full_name |         email
   ------------+-----------+-----------------------
             1 | MARY ROE  | mary_roe@example.com
             2 | JAY SMITH | jay_smith@example.com
   (2 rows)
   

   Your PostgreSQL database server is working as expected.

Conclusion

In this guide, you have learned how to install PostgreSQL on Ubuntu 24.04 server using apt, secured the database server with a password, created a sample database and tables and inserted records. After installing PostgreSQL, you can now connect the database server with your favorite programming language to create a backend for apps or application programming interfaces (APIs) using frameworks like Fast API or Django.

Further Readings

• How to Back Up PostgreSQL Database