How to Install PostgreSQL on Ubuntu 22.04 LTS: Step-by-Step Guide

PostgreSQL is a powerful, open-source object-relational database system known for its robustness and performance. This guide will walk you through the process of installing PostgreSQL on Ubuntu 22.04 LTS.

Prerequisites

• An Ubuntu 22.04 LTS system.
• A user account with sudo privileges.
• Internet access to download packages.

Step 1: Update System Packages

Before installing new software, it’s good practice to update existing packages to their latest versions.

sudo apt update
sudo apt upgrade -y

Step 2: Install PostgreSQL

Ubuntu’s default package repositories contain PostgreSQL. Install it using the apt package manager.

sudo apt install postgresql postgresql-contrib -y

• postgresql: The core PostgreSQL database server.
• postgresql-contrib: Additional utilities and extensions.

Step 3: Verify the Installation

Check the status of the PostgreSQL service to ensure it’s running.

sudo systemctl status postgresql

You should see output indicating that PostgreSQL is active (running).

Step 4: Adjust Firewall Settings (If Applicable)

If you have a firewall enabled (like UFW), you might need to allow PostgreSQL’s default port 5432.

sudo ufw allow 5432/tcp

Step 5: Switch to the PostgreSQL User

PostgreSQL creates a default user named postgres. Switch to this user.

sudo -i -u postgres

Step 6: Access the PostgreSQL Shell

Enter the PostgreSQL command-line interface (CLI) using the psql command.

psql

Step 7: Exit the PostgreSQL Shell

To exit the psql interface, type:

\q

Step 8: Create a New Database User

Create a new PostgreSQL user (also known as a role).

createuser --interactive

You’ll be prompted for:

• Enter name of role to add: your_username
• Shall the new role be a superuser? (y/n): y or n (depending on your needs)

Step 9: Create a New Database

Create a new database associated with the new user.

createdb your_database_name

Step 10: Set a Password for the New User

Access the PostgreSQL shell to set a password.

psql

Set the password (replace your_username and your_password accordingly):

ALTER USER your_username WITH ENCRYPTED PASSWORD 'your_password';

Exit the shell:

\q

Step 11: Configure PostgreSQL to Allow Password Authentication

Edit the pg_hba.conf file to enable password authentication.

sudo nano /etc/postgresql/14/main/pg_hba.conf

Locate the lines that look like this:

local   all             all                                     peer

Change peer to md5:

local   all             all                                     md5

Save and exit the file (Ctrl + X, then Y, then Enter).

Step 12: Restart PostgreSQL Service

Apply the changes by restarting PostgreSQL.

sudo systemctl restart postgresql

Step 13: Test the New User Login

Attempt to connect to PostgreSQL using the new user credentials.

psql -U your_username -d your_database_name -h 127.0.0.1 -W

• -U: Specifies the username.
• -d: Specifies the database name.
• -h: Host address (use 127.0.0.1 for local connections).
• -W: Prompts for a password.

Enter the password when prompted.

Step 14: Enable Remote Access (Optional)

If you need to allow remote connections to your PostgreSQL server:

Edit postgresql.conf:

sudo nano /etc/postgresql/14/main/postgresql.conf

Find the line:

#listen_addresses = 'localhost'

Uncomment and change it to:

listen_addresses = '*'

Edit pg_hba.conf:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the following line to allow remote connections with password authentication:

host    all             all             0.0.0.0/0               md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 15: Secure Your Installation

Ensure your PostgreSQL installation is secure:

• Use strong passwords for all users.
• Limit remote access only to trusted IP addresses (modify 0.0.0.0/0 to specific IP ranges in pg_hba.conf).
• Keep your system updated.

Conclusion

You’ve successfully installed PostgreSQL on Ubuntu 22.04 LTS and configured it for local (and optionally remote) access. You can now start creating databases and building applications that leverage PostgreSQL’s powerful features.

Additional Resources:

• PostgreSQL Documentation
• Ubuntu Community Help Wiki – PostgreSQL