Installing PostgreSQL in Rocky Linux 8

Hello,

In this week’s feature highlight, we look at How to Install PostgreSQL in Rocky Linux 8

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

List the PostgreSQL Module

List the PostgreSQL module by using the following command:

dnf module list postgresql

Output:

[root@server ~]# dnf module list postgresql
Last metadata expiration check: 2:22:31 ago on Fri 14 May 2021 01:53:51 PM EDT.
Rocky Linux 8 - AppStream
Name         Stream   Profiles             Summary
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

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Enable the PostgreSQL Module

Enable the latest version of PostgreSQL module i.e. version 12 by using the following command,

dnf module enable postgresql:12

Output:

[root@server ~]# dnf module enable postgresql:12
Last metadata expiration check: 2:22:51 ago on Fri 14 May 2021 01:53:51 PM EDT.
Dependencies resolved.
================================================================================
 Package           Architecture     Version             Repository         Size
================================================================================
Enabling module streams:
 postgresql                         12

Transaction Summary
================================================================================

Is this ok [y/N]: y
Complete!

Install the PostgreSQL Server

After the version 12 module has been enabled, install the postgresql-server by using the following command:

dnf install postgresql-server

Output:

[root@server ~]# dnf install postgresql-server
Last metadata expiration check: 2:23:17 ago on Fri 14 May 2021 01:53:51 PM EDT.
Dependencies resolved.
================================================================================
 Package           Arch   Version                               Repo       Size
================================================================================
Installing:
 postgresql-server x86_64 12.5-1.module+el8.3.0+109+eaf75cf7    appstream 5.6 M
Installing dependencies:
 libpq             x86_64 12.5-1.el8_2                          appstream 194 k
 postgresql        x86_64 12.5-1.module+el8.3.0+109+eaf75cf7    appstream 1.5 M

Creating a New PostgreSQL Database Cluster

Initialize a database storage area on disk by using the following command:

postgresql-setup --initdb

Output:

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

Start the PostgreSQL service:

systemctl start postgresql

Enable the PostgreSQL service:

systemctl enable postgresql

Output:

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

Verify the PostgreSQL service:

systemctl status postgresql

Output:

[root@server ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor >
   Active: active (running) since Fri 2021-05-14 16:18:20 EDT; 19s ago
 Main PID: 11270 (postmaster)
    Tasks: 8 (limit: 4835)
   Memory: 19.7M
   CGroup: /system.slice/postgresql.service
           ├─11270 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─11272 postgres: logger
           ├─11274 postgres: checkpointer
           ├─11275 postgres: background writer
           ├─11276 postgres: walwriter

PostgreSQL Roles

We’ll switch to postgres account for this.

sudo -i -u postgres

you can access a postgresql prompt using the psql utility.

psql

Output:

[postgres@server ~]$ psql
psql (12.5)
Type "help" for help.

postgres=#

To exit out of the postgresql shell type.

\q

Change to your original account by using the following command:

exit

To create postgresql Role:

createuser --interactive

Output:

[postgres@vps ~]$ createuser --interactive
Enter name of role to add: jones
Shall the new role be a superuser? (y/n) y

PostgreSQL Database

Create a Database by using the following command:

createdb db_name

Enter the Database name something like:

[postgres@vps ~]$ createdb jones

Opening a PostgreSQL Prompt with the New Role

Add new user by using the following command:

sudo adduser jones

To switch over and connect to the database:

sudo -i -u jones

psql

Output:

[jones@vps ~]$ psql
psql (12.5)
Type "help" for help.

Once you logged in as jones and check your current connection information.

\conninfo

Output:

jones=# \conninfo
You are connected to database "jones" as user "jones" via socket in "/var/run/postgresql" at port "5432".
jones=#