Install PostgreSQL in Ubuntu 18.04/ 20.04/ 22.04

Step-by-step guide installation PostgreSQL

PostgreSQL is a robust, open-source relational database management system (RDBMS). Its reliability, feature set, and community support make it a popular choice for managing data in a wide variety of applications, from small personal projects to large-scale enterprise systems. If you’re looking to Install PostgreSQL in Ubuntu 18.04/ 20.04/ 22.04, this guide will provide you with a step-by-step process.

In this comprehensive guide, we will walk you through the process to Install PostgreSQL in Ubuntu 18.04/ 20.04/ 22.04, along with creating a new role (user) and a new database, essential steps for setting up a functional PostgreSQL environment.

Step 1 – Update Ubuntu’s Package Index

Before embarking on any software installation in Ubuntu, it’s crucial to update the system’s package index. This synchronization ensures that you have the most current information regarding available packages and their dependencies, preventing potential installation issues.

Open your terminal application and execute the following command:

$ sudo apt update

This command will refresh the package lists, ensuring that you are working with the latest versions.

Step 2 – Install PostgreSQL

Now that your package index is up-to-date, you can proceed with the installation of PostgreSQL. The apt package manager simplifies this process.

Execute the following command in your terminal:

$ sudo apt install postgresql postgresql-contrib

This command installs both the core PostgreSQL server package (postgresql) and the postgresql-contrib package, which provides a collection of useful utilities and extensions.

During the installation, you might be prompted to set a password for the default PostgreSQL superuser, ‘postgres’. It’s highly recommended to set a strong and secure password at this stage. If you aren’t prompted, the user will be created without a password and require authentication using peer or ident authentication.

Step 3 – Verify PostgreSQL Installation

After the installation process completes, it’s important to verify that PostgreSQL is running correctly. The systemctl command is used to manage system services.

Run the following command to check the status of the PostgreSQL service:

$ sudo systemctl status postgresql

The output will display information about the PostgreSQL service, including its current status. If the service is running correctly, you should see a line indicating that it is "active (running)". If the service is not running, you can start it using sudo systemctl start postgresql.

Step 4 – Access PostgreSQL

With PostgreSQL installed and running, you can access the database server using the psql command-line interface.

To access the psql shell as the default ‘postgres’ superuser, execute the following command:

$ sudo -u postgres psql

This command switches the user to ‘postgres’ and then launches the psql shell, providing you with a command-line interface to interact with the PostgreSQL server.

Step 5 – Creating a New Role

In PostgreSQL, a role represents a user account that can be used to log in and interact with databases. Creating dedicated roles for different applications or users is a security best practice.

Within the PostgreSQL command-line interface (psql), use the CREATE ROLE command to create a new role:

CREATE ROLE newuser WITH LOGIN PASSWORD 'password';

Replace newuser with the desired username for the new role, and password with a strong, unique password.

To grant the new role the ability to create databases, execute the following command:

ALTER ROLE newuser CREATEDB;

This command modifies the newuser role, granting it the CREATEDB privilege, which allows the user to create new databases.

To exit the PostgreSQL command-line interface, type q and press Enter.

Step 6 – Creating a New Database

Now that you have created a new role, you can create a new database for that role to use.

Log back into the PostgreSQL command-line interface as the ‘postgres’ superuser:

$ sudo -u postgres psql

Use the CREATE DATABASE command to create a new database:

CREATE DATABASE newdatabase;

Replace newdatabase with the desired name for your new database.

To grant the newly created role (newuser) full access to the new database (newdatabase), execute the following command:

GRANT ALL PRIVILEGES ON DATABASE newdatabase TO newuser;

This command grants all privileges (SELECT, INSERT, UPDATE, DELETE, etc.) on the newdatabase to the newuser role. This allows the newuser to fully manage and interact with the database.

Finally, exit the PostgreSQL command-line interface by typing q and pressing Enter.

Conclusion

Congratulations! You have successfully installed PostgreSQL on Ubuntu, created a new role, and created a new database. You are now equipped to begin using PostgreSQL for your data management and storage needs. You have learned how to Install PostgreSQL in Ubuntu 18.04/ 20.04/ 22.04.

Alternative Solutions and Enhancements

While the above method is a standard approach, let’s explore some alternative solutions and enhancements for installing and configuring PostgreSQL on Ubuntu.

Alternative 1: Using Docker

Docker provides a containerized environment, which simplifies the installation and management of applications like PostgreSQL. This approach eliminates dependencies on the host system and provides a consistent environment across different platforms.

Explanation:

Instead of installing PostgreSQL directly on your Ubuntu system, you can use Docker to run PostgreSQL within a container. This isolates the database server from your host system and makes it easier to manage dependencies and configurations. Docker images are pre-built and configured, reducing the chances of errors during installation.

Code Example:

Install Docker: If you don’t have Docker installed, follow the official Docker documentation for Ubuntu.
Pull the PostgreSQL Docker Image:
```
docker pull postgres:latest
```
Run the PostgreSQL Container:
```
docker run --name postgres-db -e POSTGRES_PASSWORD=your_strong_password -p 5432:5432 -d postgres
```
- --name postgres-db: Assigns a name to the container.
- -e POSTGRES_PASSWORD=your_strong_password: Sets the PostgreSQL superuser password. Replace your_strong_password with a secure password.
- -p 5432:5432: Maps port 5432 on the host to port 5432 in the container.
- -d postgres: Runs the container in detached mode (background).
Access PostgreSQL: You can now access the PostgreSQL server running in the Docker container using psql or other database clients. You’ll need to connect to localhost on port 5432 with the username postgres and the password you set in the POSTGRES_PASSWORD environment variable.
```
psql -h localhost -p 5432 -U postgres -W
```

This method encapsulates PostgreSQL, simplifying deployment and management, especially in environments where consistency is paramount.

Alternative 2: Using a Configuration Management Tool (Ansible)

For larger deployments or environments where you need to automate the installation process across multiple servers, using a configuration management tool like Ansible is a powerful option.

Explanation:

Ansible allows you to define the desired state of your systems in a declarative way. You create a playbook that specifies the steps to install and configure PostgreSQL. Ansible then executes this playbook on your target servers, ensuring that they are configured consistently.

Code Example:

Install Ansible: If you don’t have Ansible installed, use apt:
```
sudo apt install ansible
```

Create an Ansible Playbook (e.g., postgresql_install.yml):

---
- hosts: all
  become: true
  tasks:
    - name: Update apt cache
      apt:
        update_cache: yes
    - name: Install PostgreSQL and contrib packages
      apt:
        name:
          - postgresql
          - postgresql-contrib
        state: present
    - name: Ensure PostgreSQL service is running
      service:
        name: postgresql
        state: started
        enabled: yes
    - name: Create database user
      become_user: postgres
      postgresql_user:
        db: postgres
        name: newuser
        password: password
        priv: ALL
    - name: Create a database
      become_user: postgres
      postgresql_db:
        name: newdatabase
        owner: newuser

hosts: all: Targets all hosts defined in your Ansible inventory. Adjust this to target specific servers.
become: true: Allows Ansible to execute tasks with elevated privileges (sudo).
The apt tasks install the necessary packages.
The service task ensures the PostgreSQL service is running.
The postgresql_user and postgresql_db tasks (requires the community.postgresql collection) create the user and database.

Install the community.postgresql Collection:

ansible-galaxy collection install community.postgresql

Run the Playbook:
```
ansible-playbook postgresql_install.yml
```

This approach offers scalability and repeatability, making it ideal for managing PostgreSQL installations across a fleet of servers. Remember to configure your Ansible inventory file (/etc/ansible/hosts) to point to your target Ubuntu servers. You may also need to configure SSH keys for passwordless authentication.