How To Install and Create DB/User In PostgreSQL on Rocky Linux 8.6

To Install & Create DB/User In PostgreSQL On Rocky Linux 8.6

Introduction:

PostgreSQL is a powerful, open source object-relational database system that has over 15 years of the active development phase and a proven architecture that has obtained a strong reputation for reliability, data integrity, and correctness.

Installation Procedure:

Step 1: Check the installed version of OS by using the below command

[root@linuxhelp ~]# cat /etc/os-release 
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"

Step 2: List all available PostgreSQL by using the below command

[root@linuxhelp ~]# yum model list | grep postgresql
No such command: model. Please use /usr/bin/yum --help
It could be a YUM plugin command, try: "yum install 'dnf-command(model)'"

[root@linuxhelp ~]# yum module list | grep postgresql
postgresql           9.6             client, server [d]                       PostgreSQL server and client module                                                                                                                                                                                              
postgresql           10 [d]          client, server [d]                       PostgreSQL server and client module                                                                                                                                                                                              
postgresql           12              client, server [d]                       PostgreSQL server and client module                                                                                                                                                                                              
postgresql           13              client, server [d]                       PostgreSQL server and 

Step 3: Install PostgreSQL by default is PostgreSQL 10

[root@linuxhelp ~]# yum install @postgresql
Last metadata expiration check: 0:03:32 ago on Tue 05 Jul 2022 12:57:33 AM EDT.
Dependencies resolved.
====================================================================================================
 Package                Arch        Version                                    Repository      Size
====================================================================================================
Installing group/module packages:
 postgresql-server      x86_64      10.21-2.module+el8.6.0+977+ab6e685c        appstream      5.1 M
Installing module profiles:
 postgresql/server                                                                                 

Transaction Summary
====================================================================================================
Install  1 Package
Total download size: 5.1 M
Installed size: 20 M
Is this ok [y/N]: y
Downloading Packages:
postgresql-server-10.21-2.module+el8.6.0+977+ab6e685c.x86_64.rpm    3.4 MB/s | 5.1 MB     00:01    
---------------------------------------------------------------------------------------------------

Step 4: Initialize new PostgreSQL database cluster by using the below command

[root@linuxhelp ~]# postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Step 5: Setup a password for PostgreSQL account by using the below command

[root@linuxhelp ~]# passwd postgres
Changing password for user postgres.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

Step 6: Check the status of PostgreSQL by using the below command

[root@linuxhelp ~]# systemctl status postgresql.service 
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

Step 7: Start the PostgreSQL by using the below command

[root@linuxhelp ~]# systemctl start postgresql.service

Step 8: Enable the PostgreSQL by using the below command

[root@linuxhelp ~]# systemctl enable postgresql.service 
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

Step 9: Log as postgres user by using the below command

[root@linuxhelp ~]# sudo -i -u postgres
[postgres@linuxhelp ~]$ 

Step 10: Run PostgreSQL by using the below command

[postgres@linuxhelp ~]$ psql
psql (10.21)
Type "help" for help.

Step 11: Create a database by using the below command

postgres=# create database Test_db;
CREATE DATABASE

Step 12: Create a user by using the below command

postgres=# create user psql_user with encrypted password 'Linuxc#4';

Step 13: Grant all privileges for “psql_user” user to “Test_db” database by using the below command

postgres=# grant all privileges on database Test_db to psql_user;
GRANT

Step 14: List the PostgreSQL database by using the below command

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
 test_db   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | psql_user=CTc/postgres
(4 rows)

Step 15: Exit the PostgresSQL by using the below command

postgres=# \q
[postgres@linuxhelp ~]$ 

Conclusion:

We have reached the end of this article. In this guide, we have walked you through the steps required to install & create DB/user in PostgreSQL on Rocky Linux 8.6. Your feedback is much welcome.