How to Install and Use PostgreSQL 13 on Rocky Linux 8

PostgreSQL is a free and open-source, and is one of the most advanced database systems around the world. At the time of writing this article, the latest version of PostgreSQL is PostgreSQL 13. This version comes with significant improvements to the indexing and lookup system. It is very popular among professionals and developers who are trying to find their way in Relational Database Management Systems. It is used by many large companies including Apple, Reddit, Spotify, Skype, Instagram, and more.

In this post, we will show you how to install PostgreSQL 13 on Rocky Linux 8.

Step 1 – Add PostgreSQL 13 Repository

By default, the latest version of PostgreSQL 13 is not included in the RockyLinux 8 default repository. You will need to add the PostgreSQL official repository to your system.
You can add it using the following commands:

dnf update -y

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Once the repository is added, you can verify it using the following command:

dnf -qy repolist

You should get the following output:

repo id                                              repo name
appstream                                            Rocky Linux 8 - AppStream
baseos                                               Rocky Linux 8 - BaseOS
extras                                               Rocky Linux 8 - Extras
pgdg-common                                          PostgreSQL common RPMs for RHEL/CentOS 8 - x86_64
pgdg10                                               PostgreSQL 10 for RHEL/CentOS 8 - x86_64
pgdg11                                               PostgreSQL 11 for RHEL/CentOS 8 - x86_64
pgdg12                                               PostgreSQL 12 for RHEL/CentOS 8 - x86_64
pgdg13                                               PostgreSQL 13 for RHEL/CentOS 8 - x86_64
pgdg96                                               PostgreSQL 9.6 for RHEL/CentOS 8 - x86_64

In order to use the PostgreSQL repository, you will need to disable the PostgreSQL default repository. You can disable it using the following command:

dnf module -qy disable postgresql

Step 2 – Install PostgreSQL 13

You can now install the latest version of PostgreSQL 13 using the following command:

dnf install postgresql13-server -y

Once PostgreSQL is installed, initialize the PostgreSQL database with the following command:

/usr/pgsql-13/bin/postgresql-13-setup initdb

Next, start the PostgreSQL service and enable it to start at system reboot:

systemctl start postgresql-13
systemctl enable postgresql-13

Step 3 – Create Admin User for PostgreSQL

It is recommended to create a separate user for PostgreSQL.

To do so, log in to PostgreSQL with the following command:

su - postgres
psql

Next, create an admin user with the desired role and set a password with the following command:

CREATE USER hitesh WITH CREATEDB CREATEROLE PASSWORD 'userpassword';

You can now check the user with the following command:

\du

Sample output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 hitesh    | Create role, Create DB                                     | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Step 4 – Create Database and Table in PostgreSQL

In this section, we will show you how to create a database and table in PostgreSQL.

First, log in to PostgreSQL with the following command:

su - postgres
psql

Next, create a database with the following command:

CREATE DATABASE hiteshdb OWNER hitesh;

Next, verify your database with the following command:

\l

You should see all databases in the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 hiteshdb  | hitesh   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Next, create a table named students with the following command:

CREATE TABLE students (
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   salary         REAL
);

You can verify the table with the following command:

\d

Sample output:

          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | students | table | postgres
(1 row)

To display your table properties, run the following command:

\d students

Sample output:

                 Table "public.students"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)

Conclusion

In the above guide, we explained how to install PostgreSQL 13 on RockyLinux 8. We also explained how to create a database and table in PostgreSQL. You should now be able to use the PostgreSQL database with your applications. Give it a try on dedicated hosting from Atlantic.Net today!