How To Install PostgreSQL On Ubuntu Linux

Postgres, also known as PostgreSQL, is a system for managing databases that uses the SQL language. It follows standard rules and offers advanced options like reliable transactions and the ability to handle multiple tasks simultaneously without causing reading delays.

The tutorial explains how to set up Postgres on an Ubuntu 20.04 server. It covers the installation process and includes instructions on creating a new user and database.

This tutorial focuses on PostgreSQL version 16.0.

Prerequisites

• Setup sudo user.

Step 1: Install PostgreSQL

Enable PostgreSQL APT Respository.

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update -y && sudo apt upgrade -y

We will now install PostgreSQL packages.

sudo apt install postgresql postgresql-contrib -y

Next, we will start the PostgreSQL service and enable it to start the service on reboot.

sudo systemctl start postgresql.service && sudo systemctl enable postgresql.service

Step 2: PostgreSQL Roles and Databases

Postgres, by default, employs a mechanism termed “roles” for both authentication and authorization tasks. This is somewhat akin to the typical Unix-style user and group system.

When you install Postgres, it’s configured to use ident authentication. This means it links Postgres roles to corresponding Unix/Linux system accounts. If a role is present in Postgres, a Unix/Linux user with an identical name can log in under that role.

During the installation, a user named Postgres is established, which corresponds to the primary Postgres role. There are multiple methods to access Postgres using this account. One method is to transition to the Postgres account on your server using a specific command.

sudo -i -u postgres

Now, you can access the Postgres prompt by running the below command:

psql

This action will grant you access to the PostgreSQL interface, allowing you to immediately engage with the database management system.

To exit from the PostgreSQL interface, execute the subsequent command:

\q

Once you have exited the PostgreSQL prompt, you can easily switch back to your logged-in user from the Postgres user.

exit

Alternative way to connect to Postgres prompt:

sudo -i -u postgres psql

Step 3: Create A New Role in PostgreSQL

Once you are logged in as Postgres user, you can enter the below command:

createuser --interactive

Alternatively, you can also use the below command to run as sudo user.

sudo -u postgres createuser --interactive

output:

postgres@tekspace-db-dev:~$ createuser --interactive
Enter name of role to add: tekspace-db-dev
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Step 4: Create A New Database in PostgreSQL

When Postgres sets things up, it expects that each role (or user) will have a database with the same name they can use.

So, if you made a user named “tekspace-db-dev” before, it will try to connect to a database named “tekspace-demo” automatically. To make this database, you can use the “createdb” command.

If you’re using the Postgres account, you’d type a command similar to this:

createdb tekspace-db-dev

Alternatively, you can also run this with sudo user:

sudo -u postgres createdb tekspace-db-dev

Exit out of Postgres user:

exit

Step 5: Open a Postgres Prompt with A New Role

To sign in using ident authentication, your Linux username should match your Postgres role and database name.

If you don’t have a Linux username like that, you can make one using the “adduser” command. Perform this action with an account that has the necessary privileges, not the main ‘Postgres’ account. Please log in using a different account called ‘sudo’ instead of the main ‘Postgres’ account.

sudo adduser tekspace-db-dev

Output:

rahil@tekspace-db-dev:~$ sudo adduser tekspace-db-dev
[sudo] password for rahil:
Adding user `tekspace-db-dev' ...
Adding new group `tekspace-db-dev' (1001) ...
Adding new user `tekspace-db-dev' (1001) with group `tekspace-db-dev (1001)' ...
Creating home directory `/home/tekspace-db-dev' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for tekspace-db-dev
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [Y/n] Y
Adding new user `tekspace-db-dev' to supplemental / extra groups `users' ...
Adding user `tekspace-db-dev' to group `users' ...

After a login user has been created, now you can navigate to Postgres prompt by executing the below command:

sudo -i -u tekspace-db-dev

To access the prompt in PostgreSQL as a newly created user, execute the following command after logging in:

psql

If you want to connect to a different database, you can specify the database as shown below:

psql -d postgres

Output:

tekspace-db-dev@tekspace-db-dev:~$ psql -d postgres
psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1))
Type "help" for help.

Once you are logged-in, you can check your current connection information by executing the below command:

\conninfo

postgres=> \conninfo
You are connected to database "postgres" as user "tekspace-db-dev" via socket in "/var/run/postgresql" at port "5432".
postgres=>

That’s it! You have successfully set up a PostgreSQL server on an Ubuntu Linux server.

User & Database Management

CREATE DATABASE tekspace_db_dev;
CREATE USER tekspace_db_dev_user WITH PASSWORD 'super_secret_password';
GRANT ALL PRIVILEGES ON DATABASE tekspace_db_dev to tekspace_db_dev_user;

Next connect with admin account to a newly created database:

psql -h localhost -U postgres -d tekspace_db_dev -W

This is a workaround for PostgreSQL 15.

GRANT ALL ON SCHEMA public TO tekspace_db_dev_user;

Now connect to a newly created database with its user:

psql -h localhost -U tekspace_db_dev_user -d tekspace_db_dev -W

Change Password For Postgres USER

To change the password for Postgres user follow below steps.

sudo -i -u postgres

psql

ALTER USER postgres PASSWORD 'Super secret password';