How To Install PostgreSQL 14 on Rocky Linux 8

In this guide, we want to teach you How To Install PostgreSQL 14 on Rocky Linux 8.

PostgreSQL is an enterprise-class open-source database management system. It supports both SQL and JSON for relational and non-relational queries for extensibility and SQL compliance. PostgreSQL supports advanced data types and performance optimization features, which are only available in expensive commercial databases, like Oracle and SQL Server. It is also known as Postgres.

Alongside improved authentication, PostgreSQL 14 simplifies the process of assigning write-only and read-only capabilities to users on tables, views, and schemas by introducing two predefined roles, pg_write_all_data, and pg_read_all_data. The former role makes it convenient to create super-user-styled privileges.

Steps To Install PostgreSQL 14 on Rocky Linux 8

To complete this guide, you must log in to your server as a non-root user with sudo privileges. To do this, you can follow this guide on Initial Server Setup with Rocky Linux 8.

Import PostgreSQL 14 Repository on Rocky Linux 8

First, you need to update your local package index with the command below:

sudo dnf update -y

Then, use the following command to install the RPM repository:

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

Next, you need to disable the built-in PostgreSQL module by running the command below:

sudo dnf -qy module disable postgresql

Installing PostgreSQL 14 on Rocky Linux 8

At this point, you can use the following command to install PostgreSQL on Rocky Linux 8:

sudo dnf install -y postgresql14-server

When your installation is completed, you need to initialize the database and enable automatic start.

To do this, run the commands below:

# sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
# sudo systemctl enable postgresql-14
# sudo systemctl start postgresql-14

Verify that PostgreSQL is active and running on your Rocky Linux 8:

sudo systemctl status postgresql-14

Output
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vend>
   Active: active (running) since Wed 2022-09-28 04:03:13 EDT; 32s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 6215 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGD>
 Main PID: 6221 (postmaster)
    Tasks: 8 (limit: 23699)
   Memory: 16.7M
   CGroup: /system.slice/postgresql-14.service
...

Switch To PostgreSQL 14 Account

As you know, only superusers and roles with create role privileges can create new roles in Postgres. Postgres’s user account associated with the default Postgres role was created during the installation.

To access PostgreSQL 14 shell on Rocky Linux 8, run the following commands:

sudo -i -u postgres

Output
[postgres@olivia ~]$

Then, simply type:

[postgres@olivia ~]$ psql

Output
psql (14.5)
Type "help" for help.

postgres=# 

This means you have successfully connected to the database.

To exit the Postgres database, you can do this by typing the following:

exit

Also, you can use the following command to access your PostgreSQL Shell directly:

sudo -u postgres psql

Output
psql (14.5)
Type "help" for help.

postgres=# 

Create User and Database with PostgreSQL 14

At this point, you can create a user and database. Only superusers and roles with “createrole” privilege can create new roles.

To create a new user, use the following command:

sudo su - postgres -c "createuser <name>"

For example:

sudo su - postgres -c "createuser orcauser"

Now you can create a PostgreSQL database for the new user you created:

sudo su - postgres -c "createdb <namedb>"

For example:

Now you can create a PostgreSQL database for the new user you created:

sudo su - postgres -c "createdb orcadb"

Then, you need to grant the privileges to your database and user.

To do this, switch to your PostgreSQL shell on Rocky Linux 8:

sudo -u postgres psql

Next, grant access to your database and username:

postgres=#
 GRANT ALL PRIVILEGES ON DATABASE orcadb TO orcauser;

Output
GRANT

Also, you can check your current connection information with the command below:

postgres=# \conninfo

To exit from your PostgreSQL shell, run the command below:

exit

For more information, you can visit the PostgreSQL Documentation Page.

Conclusion

At this point, you have learned to Install PostgreSQL 14 on Rocky Linux 8 and create a user and database with your PostgreSQL account.

Hope you enjoy it.

Please subscribe to us on Facebook and Twitter.