Editor

8 min read | 3 years ago

How to Install PostgreSQL on Rocky Linux

Linux

Install postgresql on Rocky Linux

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.while writing this article postgresql 13 is the latest version.PostgreSQL 13 contains many new features and enhancements.few features are listed below.

Space savings & performance gains from de-duplication of B-tree index entries
Improved performance for queries that use aggregates or partitioned tables
Better query planning when using extended statistics
Parallelized vacuuming of indexes
Incremental sorting

In this Article, we will show you how to install postgresql on Rocky Linux 8.

Also Read -> How to Install and Configure Clamav in Linux

Step 1: Add poststgresql repo

you need to update your rocky linux 8 system before you start the installation of PostgreSQL using dnf command.

sudo dnf update

The default version of PostgreSQL on Appstream repositories is PostgreSQL 10.

if you want to install latest version of postgresql 13 and it is available on the official PostgreSQL repository, so you need to add PostgreSQL repository to your Rocky Linux system.

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

Step 2: Install postgresql

once it is installed,you need to be disable built-in PostgreSQL module repo.

sudo dnf -qy module disable postgresql

Now install latest version of postresql.

sudo dnf install postgresql13-server

output:

Last metadata expiration check: 0:01:12 ago on Mon 27 Sep 2021 03:49:27 PM IST.
Dependencies resolved.
========================================================================================================================================================================
 Package                                        Architecture                      Version                                       Repository                         Size
========================================================================================================================================================================
Installing:
 postgresql13-server                            x86_64                            13.4-1PGDG.rhel8                              pgdg13                            5.5 M
Installing dependencies:
 postgresql13                                   x86_64                            13.4-1PGDG.rhel8                              pgdg13                            1.5 M
 postgresql13-libs                              x86_64                            13.4-1PGDG.rhel8                              pgdg13                            414 k
Transaction Summary
========================================================================================================================================================================
Install  3 Packages
Total download size: 7.4 M
Installed size: 31 M
Is this ok [y/N]: y

Step 3: Initialize db and start service

once postgresql is installed,you must initialize the PostgreSQL configuration and then start and enable the PostgreSQL service.

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

output:

Initializing database ... OK

now you can start and enable the service to start on boot time and also check the status of service by the following commands.

sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
sudo systemctl status postgresql-13

output:

● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-09-27 16:04:06 IST; 13s ago
     Docs: https://www.postgresql.org/docs/13/static/
 Main PID: 29826 (postmaster)
    Tasks: 8 (limit: 7596)

To verify the installed version, you can use 'psql -V' command.

$ psql -V
psql (PostgreSQL) 13.4

Also Read -> Install PostgreSQL in XAMPP on Windows and integrate phpPgAdmin

Step 4: setup password for postgres

while postgresql installation, it will create a new system user and database user name as postgres.so you have to secure the postgres user with a password for security purpose for both system and user and database user.

sudo passwd postgres

set the new password for the system user postgres.

Now login into the postgresql shell, and change the paaword for postgres user.

First,log in as a system user postgres as below.

su - postgres

Now login to the shell using the psql

psql

then run below query to change password for db postgres user.

ALTER USER postgres WITH PASSWORD 'strong_password';

Now type \q to quit from postgres shell and type exit to logout from postgres user shell.

Step 5: create database

Log in to the PostgreSQL shell with the following command.

sudo su - postgres

and then type psql

output:

[root@RockyLinux ~]# sudo su - postgres
Last login: Mon Sep 27 19:25:41 IST 2021 on pts/1
[postgres@RockyLinux ~]$ psql
psql (13.4)
Type "help" for help.
postgres=#

now create user with privileges

postgres=# CREATE USER testuser WITH CREATEDB CREATEROLE PASSWORD 'strong_password';

and then create database

postgres=# CREATE DATABASE testdb OWNER testuser;

and verify the new user with \du and list the databases with \l

output:

postgres=# CREATE USER testuser WITH CREATEDB CREATEROLE PASSWORD 'strong_password';
CREATE ROLE
postgres=# CREATE DATABASE testdb OWNER testuser;
CREATE DATABASE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create role, Create DB                                     | {}
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
 testdb    | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

That's it. Now you have successfully installed postgresql 13 and also created the database on Rocky linux 8 system.

Also Read -> How to Install Docker and Docker Compose on Rocky Linux