How to Install PostgreSQL on Ubuntu

This tutorial aims to guide beginners through the process of installing PostgreSQL on Ubuntu, ensuring even those new to database management or Linux can follow along easily. PostgreSQL, often known as Postgres, is an open-source, powerful, and advanced relational database management system (RDBMS). It’s renowned for its robustness, scalability, and ability to handle large volumes of data, making it a popular choice for developers and businesses worldwide.

Why Choose PostgreSQL on Ubuntu?

Before we start the installation process, let’s briefly discuss why PostgreSQL is a preferred choice for many developers and businesses:

Open-Source: PostgreSQL is free to use for both personal and commercial projects.
Advanced Features: It supports advanced data types, sophisticated locking mechanisms, and robust transactional integrity.
Extensibility: Users can define their own data types, build out custom functions, and even write code from different languages without recompiling your database.
Standards Compliance: PostgreSQL is highly compliant with SQL standards.

Preparing for Installation

Before installing PostgreSQL on Ubuntu, ensure your system is up-to-date. Open your terminal and execute the following commands:

sudo apt update
sudo apt upgrade

This will update the package lists for upgrades for packages that need upgrading, as well as new packages that have just come to the repositories.

Step 1: Installing PostgreSQL on Ubuntu

Ubuntu’s default repositories contain PostgreSQL packages, making the installation process straightforward. To install PostgreSQL along with its -contrib package (which adds some additional utilities and functionality), execute the following command:

sudo apt install postgresql postgresql-contrib

Step 2: Checking the Installation

After installation, PostgreSQL should start automatically. To check if PostgreSQL is running, use:

sudo -u postgres psql -c "SELECT version();"

This command connects to the PostgreSQL database server and outputs the version of PostgreSQL you’ve installed, confirming the installation was successful.

Step 3: Basic Configuration

Setting Up a New Role

PostgreSQL uses role-based authentication to handle database access permissions. By default, a role named postgres is created. For daily operations, it’s recommended to create a new role. To do this, switch to the postgres user and run the createuser command:

sudo -u postgres createuser --interactive

When prompted, enter the name of the new role (user) and specify whether it should have superuser permissions.

Creating a Database

In PostgreSQL, typically a database is owned by a specific role. To create a new database, use the createdb command:

sudo -u postgres createdb <your_new_database_name>

Replace <your_new_database_name> with your desired database name.

Step 4: Accessing and Managing the Database

To start working with PostgreSQL, you can access the interactive terminal by typing:

sudo -u postgres psql

This command logs you into the PostgreSQL terminal, from where you can execute SQL commands. Here’s a quick example to create a table:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

To list all databases, use the command:

\l

To connect to a specific database:

\c <your_new_database_name>

Step 5: Configuring Remote Access (Optional)

By default, PostgreSQL is configured to allow connections only from the local machine. If you need to enable remote access, you’ll need to modify two files: postgresql.conf and pg_hba.conf.

postgresql.conf: Find the line #listen_addresses = 'localhost' and change it to listen on all interfaces: listen_addresses = '*'.
pg_hba.conf: Add a line specifying which hosts are allowed to connect, the authentication method, and the database:

host    all             all             0.0.0.0/0               md5

After making these changes, restart PostgreSQL to apply them:

sudo systemctl restart postgresql

Congratulations! You’ve successfully installed PostgreSQL on Ubuntu. You’ve also learned how to create a new role and database, access the database terminal, and configure PostgreSQL to allow remote connections. This guide aimed to lay down a strong foundation for beginners to start their journey with PostgreSQL