Install PostgreSQL on Ubuntu 24.04: The Complete Guide

Are you ready to find out how to install PostgreSQL on Ubuntu 24.04? You're in the right place! PostgreSQL, often simply called Postgres Ubuntu, is a powerful open-source relational database management system (RDBMS) known for its reliability, robustness, and rich feature set. Whether you're developing a new web application, managing large amounts of data, or simply exploring the world of databases, this guide will walk you through it step by step.

In this article, we'll see together how to install the latest version of PostgreSQL on Ubuntu 24.04 (Noble Numbat), configure basic access, and take your first steps with this extraordinary database.

Why Choose PostgreSQL?

PostgreSQL stands out for several reasons:

• Open Source and Free: No licensing costs.
• Extensibility: Supports custom data types, functions, operators, and aggregates.
• SQL Standard Compliance: High adherence to SQL standards.
• Reliability and Stability: Ideal for mission-critical applications thanks to its robust architecture.
• Active Community: Extensive documentation and community support.
• Advanced Features: Support for JSON, ACID transactions, replication, and much more.

If you're looking for a powerful and flexible database solution for your Ubuntu 24.04 server, PostgreSQL is an excellent choice.

Prerequisites

Before you begin, make sure you have:

• A system with Ubuntu 24.04 LTS (Noble Numbat) installed.
• Access to a user with sudo privileges.
• An active internet connection to download packages.

Step 1: Update Your Ubuntu System

It's always good practice to update the package list and installed packages before adding new software. Open the terminal and type:

sudo apt update
sudo apt upgrade -y

This command will update the list of available packages and then upgrade all installed packages to their latest versions.

Step 2: Install PostgreSQL on Ubuntu 24.04

Ubuntu 24.04 includes a recent version of PostgreSQL in its official repositories. At the time of writing this article, the default version is PostgreSQL 16. To install it, along with the postgresql-contrib package which provides additional utilities and functionalities, run:

sudo apt install postgresql postgresql-contrib -y

Verifying the Installation

Once the installation is complete, the PostgreSQL service should start automatically. You can check its status with the following command:

sudo systemctl status postgresql.service

If everything went well, you should see output indicating that the service is active (running). Press q to exit the status view.

You can also check the installed version with:

psql --version

It should return something like psql (PostgreSQL) 16.x (Ubuntu 16.x-...).

Step 3: Accessing PostgreSQL and Configuring the postgres User

During installation, PostgreSQL creates an operating system user named postgres. This user has the default superuser role within the database.

Connecting via the postgres user

To connect to the PostgreSQL prompt (called psql), you first need to switch to the postgres operating system user:

sudo -i -u postgres

Once you become the postgres user, you can access the psql prompt:

psql

You should now be at the PostgreSQL prompt, indicated by postgres=#. To exit the psql prompt, type \q and press Enter. To return to your regular user, type exit and press Enter.

Setting a Password for the postgres User

For security reasons, it is crucial to set a password for the postgres database user. Inside the psql prompt (after connecting as described above), type:

ALTER USER postgres PASSWORD 'yourSuperSecurePassword';

Replace 'yourSuperSecurePassword' with a strong password. Remember that this password is for the postgres *database* user, not the operating system user.

Step 4: Creating a New User and a New Database (Recommended)

For most applications, it is not advisable to use the postgres user directly. It's good practice to create dedicated users with specific privileges for your databases.

Create a New User Role

Still from the psql prompt (connected as the postgres user), you can create a new role (user). For example, to create a user named myuser:

CREATE USER myuser WITH PASSWORD 'aPasswordForMyuser';

Create a New Database

Now, let's create a database. For example, a database named mydatabase owned by the newly created myuser:

CREATE DATABASE mydatabase OWNER myuser;

Grant Privileges to the New User on the Database

Even though myuser owns the database, you might want to explicitly grant all privileges:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Exit the psql prompt (\q) and the postgres user session (exit).

Verify the Connection with the New User

You can now try to connect to your new database with your new user directly from your regular Linux user (without sudo -u postgres), if you have configured md5 or scram-sha-256 authentication (usually default for local connections after setting a password).

psql -U myuser -d mydatabase -h localhost

You will be prompted for the password you set for myuser. If the connection is successful, you will see the mydatabase=> prompt.

Note: The default authentication method for local connections (host or local) is often peer for the postgres user and md5 or scram-sha-256 for other users after they have been assigned a password. If you encounter authentication issues, you may need to modify the pg_hba.conf file. For Ubuntu 24.04 with PostgreSQL 16, it is usually located at /etc/postgresql/16/main/pg_hba.conf.

Useful Commands for Managing PostgreSQL

Here are some useful systemctl commands for managing the PostgreSQL service:

• Start PostgreSQL:

  sudo systemctl start postgresql

• Stop PostgreSQL:

  sudo systemctl stop postgresql

• Restart PostgreSQL:

  sudo systemctl restart postgresql

• Enable PostgreSQL to start on boot:

  sudo systemctl enable postgresql

• Disable PostgreSQL from starting on boot:

  sudo systemctl disable postgresql

Frequently Asked Questions (FAQ) about PostgreSQL on Ubuntu 24.04

• Q: What version of PostgreSQL is installed on Ubuntu 24.04 with apt?
  A: Ubuntu 24.04 (Noble Numbat) includes PostgreSQL 16 in its default repositories.
• Q: Where are the PostgreSQL configuration files located?
  A: The main configuration files (postgresql.conf and pg_hba.conf) are usually located in /etc/postgresql/16/main/ (the version number might vary if you install a different version).

• Q: How can I enable remote connections to PostgreSQL?
  A: You need to modify two files:

  1. postgresql.conf: set listen_addresses = '*'.
  2. pg_hba.conf: add a line to allow connections from the desired network, e.g., host all all 0.0.0.0/0 md5 (for IPv4) or host all all ::/0 md5 (for IPv6). Warning: 0.0.0.0/0 allows connections from any IP; carefully consider the security implications.

  Remember to restart the PostgreSQL service after making changes.

• Q: What is a popular GUI tool for managing PostgreSQL?
  A: pgAdmin is the most popular open-source administration and development platform for PostgreSQL. It is available as a web or desktop application.
• Q: How do you back up a PostgreSQL database?
  A: You can use the pg_dump utility. For example: pg_dump -U username -d databasename -f backup.sql.

Conclusion

Congratulations! You now have a fully functional PostgreSQL Ubuntu server on your Ubuntu 24.04 installation. You've learned how to install PostgreSQL