How to Install PostgreSQL (psql) on Ubuntu 24.04 LTS

PostgreSQL, often referred to as Postgres, is a powerful, open-source object-relational database system with a strong reputation for reliability, feature robustness, and performance. In this guide, we will walk you through the steps to install PostgreSQL on Ubuntu 24.04 LTS.

Why PostgreSQL?

PostgreSQL is known for its advanced features like complex queries, foreign keys, triggers, updatable views, transactional integrity, and multi-version concurrency control. It supports a wide range of data types and can be extended by users in many ways, making it an excellent choice for both small and large applications.

Prerequisites

Before you start, ensure that you have:

1. A system running Ubuntu 24.04 LTS.
2. Sudo privileges to install software and manage system configurations.

Step 1: Update Your System

First, update your package index to make sure you have the latest information on the newest versions of packages and their dependencies:

$ sudo apt update
$ sudo apt upgrade -y

Step 2: Install PostgreSQL

Ubuntu’s default package repository includes PostgreSQL, so you can install it easily using the apt package manager.

1. Install PostgreSQL:

$ sudo apt install postgresql postgresql-contrib -y

Step 3: Verify the Installation

Once the installation is complete, you can verify that PostgreSQL is installed and running correctly.

1. Check the status of PostgreSQL:

$ sudo systemctl status postgresql

You should see output indicating that PostgreSQL is active and running.

Step 4: Configure PostgreSQL

By default, PostgreSQL is set up to use the peer authentication method for local connections, meaning it uses the operating system user accounts for authentication.

1. Switch to the PostgreSQL user:

$ sudo -i -u postgres

2. Access the PostgreSQL prompt:

$ psql

You will now be logged into the PostgreSQL shell as the postgres user.

Step 5: Basic PostgreSQL Commands

Here are some basic commands to get you started with PostgreSQL:

• Change the password for the postgres user:

\password postgres

• Create a new database:

CREATE DATABASE mydatabase;

• Create a new user:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

• Grant privileges to the new user on the database:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

• List all databases:

\l

• Connect to a database:

\c mydatabase

• List all tables in the current database:

\dt

• Exit the PostgreSQL shell:

\q

Step 6: Enable Remote Access to PostgreSQL (Optional)

If you need to access your PostgreSQL server remotely, you will need to configure PostgreSQL to listen on all IP addresses and allow connections from remote hosts.

1. Edit the PostgreSQL configuration file:

$ sudo nano /etc/postgresql/11/main/postgresql.conf

2. Find the line:

#listen_addresses = 'localhost'

3. Change it to:

codelisten_addresses = '*'

Save and close the file.

4. Edit the pg_hba.conf file to allow remote connections:

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

5. Add the following line at the end of the file:

host    all             all             0.0.0.0/0               md5

Save and close the file.

6. Restart PostgreSQL to apply the changes:

$ sudo systemctl restart postgresql

Conclusion

Congratulations! You have successfully installed and configured PostgreSQL on your Ubuntu 24.04 LTS system. PostgreSQL is now ready to be used for your database management needs, whether for development or production environments. For more detailed information on using PostgreSQL, check out the official PostgreSQL documentation.

Found this article interesting? Follow Brightwhiz on Facebook, Twitter, and YouTube to read and watch more content we post.