How to Install PostgreSQL in AlmaLinux 8

161

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@vps ~]# dnf module list postgresql
Last metadata expiration check: 0:02:54 ago on Fri 19 Feb 2021 01:38:26 PM EST.
AlmaLinux 8.3 - 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 PostgreSQL module of version 12 by using the following command:

dnf module enable postgresql:12

Output:

[root@vps ~]# dnf module enable postgresql:12
Last metadata expiration check: 0:03:07 ago on Fri 19 Feb 2021 01:38:26 PM EST.
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@vps ~]# dnf install postgresql-server
Last metadata expiration check: 0:03:23 ago on Fri 19 Feb 2021 01:38:26 PM EST.
Dependencies resolved.
================================================================================
 Package           Arch   Version                               Repo       Size
================================================================================
Installing:
 postgresql-server x86_64 12.5-1.module_el8.3.0+2046+664538f4   appstream 5.6 M
Installing dependencies:
 libicu            x86_64 60.3-2.el8_1                          baseos    8.8 M
 libpq             x86_64 12.5-1.el8_3                          appstream 194 k
 postgresql        x86_64 12.5-1.module_el8.3.0+2046+664538f4   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@vps ~]# 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@vps ~]# 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@vps ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor >
   Active: active (running) since Fri 2021-02-19 13:44:05 EST; 58min ago
 Main PID: 57294 (postmaster)
    Tasks: 8 (limit: 23721)
   Memory: 45.7M
   CGroup: /system.slice/postgresql.service
           ├─57294 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─57295 postgres: logger

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@vps ~]$ 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 Postgres 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".