How to Install and Use PostgreSQL 13 on Rocky Linux

PostgreSQL is a free and open-source database management system focusing on extensibility and SQL compliance. PostgreSQL is an advanced and enterprise-class RDBMS (Relational Database Management System) supports both SQL (relational) and JSON (non-relational) querying.

It's a highly stable database management system backed by more than 20 years of development by the open-source community and has a strong reputation among developers and system admins for its reliability, flexibility, feature robustness, and performance.

PostgreSQL is used as a primary database for web applications, mobile applications, and analytic applications. Some notable PostgreSQL users around the globe are Reddit, Skype, Instagram, Disqus, The Guardian, Yandex, Bloomberg, Nokia, BMW, etc.

In this guide, you will learn how to install the PostgreSQL database on Rocky Linux, securing the PostgreSQL database deployment, and basic usage of PostgreSQL queries for managing users and databases.

Prerequisites

  • An Rocky Linux system.
  • A user with root or sudo privileges. This user will be used for installing new packages and make changes system-wide.

Adding PostgreSQL Repository

For this guide, you will be installing the latest stable version of PostgreSQL v13.4.This version is available on the official PostgreSQL repository, so you need to add the PostgreSQL repository to your Rocky Linux system.

1. Add the official PostgreSQL repository for the Rocky Linux system by running the following command.

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

2. Next, execute the following command below to verify the PostgreSQL repository are available on your system.

sudo dnf -qy repolist

If the PostgreSQL repository is available on your system, you will see multiple versions of PostgreSQL repositories as the screenshot below.

Add and Check PostgreSQL Repository

Installing PostgreSQL 13 on Rocky Linux

To be able to install PostgreSQL from the official PostgreSQL repository (not from the Rocky Linux repository), you need to disable the built-in PostgreSQL repository module.

1. Execute the following command below to disable the built-in PostgreSQL module repository.

sudo dnf module -qy disable postgresql

The command will run the DNF command on quiet mode and accept all gpg keys (-qy option) for PostgreSQL repositories.

Disable built-in PostgreSQL repository rocky linux

2. After that, install the latest version of PostgreSQL 13.4 from the PostgreSQL repository using the dnf command below.

sudo dnf install postgresql13-server

Type 'y' and press 'Enter' to install the PostgreSQL 13.x.

Install PostgreSQL 13 on Rocky Linux

Then wait for the PostgreSQL installation.

PostgreSQL Database Initialization

Next, after the PostgreSQL installation is complete, you must initialize the PostgreSQL configuration and then start and enable the PostgreSQL service.

1. Execute the following command to initialize the PostgreSQL database configuration.

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

Initialize PostgreSQL database configuration

2. After that, start and enable the PostgreSQL service using the command below.

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

Now the PostgreSQL service is active and running, and it will run automatically on every boot.

3. Now execute the command below to verify the PostgreSQL service.

sudo systemctl status postgresql-13

If your PostgreSQL service is running, you will see the green output such as "active(running)" as below. Otherwise, you will see the red output such as "failed" following by the error message logs.

Start and enable and checking PostgreSQL service status

Securing PostgreSQL Deployment

During the installation, PostgreSQL will create a new system user and database user name as "postgres". And for this stage, you will be setting up a new password for the "postgres" user, both for the system user and database user.

1. Change the password for default system user "postgres" using the following command.

passwd postgres

Now type the new password for the system user "postgres".

2. Next, to change the password for the "postgres" database user, you must log in to the PostgreSQL shell.

First, log in as a system user "postgres" using the following command.

su - postgres

Now login to the PostgreSQL shell using the psql command below.

psql

Execute the following query to create a new password for the default "postgres" database user.

ALTER USER postgres WITH PASSWORD 'strongpostgrespassword';

Now type exit and press "Ctrl+d" to exit and log out from the 'postgres' user shell.

Change the postgres password for System user and Database user

Change Authentication Method

By default, local PostgreSQL users will connect to the PostgreSQL shell using the 'peer' method. The peer authentication method will work only for local connections. In the development environment, you can use this type of authentication, but for production, consider using the password-based authentication method.

For this stage, you will learn how to change the default peer authentication method to password authentication using 'scram-sha-256'.

1. First, log in to the PostgreSQL shell using the following command.

sudo -u postgres psql

Now execute the following query to check the location of the PostgreSQL configuration 'pg_hba.conf'.

SHOW hba_file;
SHOW password_encryption;

You will see the output as below.

Show PostgreSQL configuration and password encryption

You will notice the PostgreSQL configuration "pg_hba.conf" are located at the '/var/lib/pgsql/13/data' directory, and the default password encryption for PostgreSQL on RHEL based operating system is 'scram-sha-256'.

Now type '\q' to exit and quit the PostgreSQL shell.

2. Next, change the working directory to '/var/lib/pgsql/13/data' and edit the configuration 'pg_hba.conf' using nano editor.

cd /var/lib/pgsql/13/data/
nano pg_hba.conf

At the bottom of the line, change the local authentication method to 'scram-sha-256' as below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Now press 'Ctrl+x', type 'y', and press "Enter" to save and exit.

Using this configuration, you will be prompted for the password to log in to the PostgreSQL shell.

3. Next, apply the new configuration by restarting the PostgreSQL service using the following command.

sudo systemctl restart postgresql-13

Now every time you want to access the PostgreSQL shell, you must type the password for authentication.

4. To make sure of the password authentication configuration, log in to the PostgreSQL shell using the following command.

su - postgres
psql

Now you will be asked for a password for the default user 'postgres'.

Type the password for the 'postgres' database user and press 'Enter'. If your password is correct, you will see the PostgreSQL shell as follows. Otherwise, you will see the 'FATAL' error because the password is incorrect.

Verify PostgreSQL authentication

Additionally, you can use the one-line command to log in to the PostgreSQL shell as below.

# Log in as default "postgres" user
sudo -u postgres psql

# Log in as another user
sudo -u postgres psql -U username

Verify PostgreSQL authentiation with psql command

Creating New User and Database for your Application

At this stage, you will learn how to create a new user and database on PostgreSQL.

1. Log in to the PostgreSQL shell by executing the command below.

sudo -u postgres psql

Now type the password for PostgreSQL user 'postgres'.

2. Run the PostgreSQL query below to create a new user 'johndoe' with the password 'johndoestrongpassword' and give the user privileges for creating a new database and role.

CREATE USER johndoe WITH 
CREATEDB
CREATEROLE
PASSWORD 'johndoestrongpassword';

Create new user PostgreSQL

After that, verify the new user using the following query.

\du

Now you will see the new user 'johndoe' with the list of roles 'Create role' and 'Create DB' as below.

Display PostgreSQL user

3. Next, to create a new user database on PostgreSQL, run the following query.

CREATE DATABASE johndoe OWNER johndoe;

Now verify the new database using the following query.

\l

And you will see the new database 'johndoe' with the owner 'johndoe' as the screenshot below.

Create new database PostgreSQL and display available databases

Create Table and Insert Data

After creating a new database and user, you will learn how to create a table, input data, and showing data from the database.

1. Now log in to the PostgreSQL shell as user 'johndoe' using the following command.

su - postgres
psql -U johndoe

Or you can use the one-line command as below.

sudo -u postgres psql -U johndoe

Now type the password for PostgreSQL user 'johndoe' and press 'Enter' to login.

Connect to PostgreSQL shell with johndoe user

As you can see on the screenshot, you're now logged in to the PostgreSQL shell at the database 'johndoe=>'.

2. Now execute the following query to create a new table 'users' with some columns 'id, name, age, address, and salary'.

CREATE TABLE users (
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   salary         REAL
);

To display available tables on the current database, use the '\d' query as 'describe' as below.

\d

To display columns on the table, you can use the same query following by the table name as below.

\d users

Below is the similar output you will get.

Create table users and display available columns on the table

3. Next, add new data to the table using the 'insert' query as below.

INSERT INTO users (id,name,age,address,salary) VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO users (id,name,age,address,salary) VALUES (2, 'Jesse', 35, 'Mexico', 30000.00);
INSERT INTO users (id,name,age,address,salary) VALUES (3, 'Linda', 27, 'Canada', 40000.00);

Insert data to the database

4. After that, you can show all available data using the 'select' query as below.

SELECT * FROM users;

The option '*' will dump all columns inside the table.

To specify the column, you can use the 'select' query as below.

SELECT name,age,salary FROM users;

You will see a similar output as below.

Display data from the PostgreSQL database

Conclusion

Congratulations! Now you've learned how to install PostgreSQL on Rocky Linux. Also, you've learned how to deploy PostgreSQL in a secure way (minimal), and the basic usage of PostgreSQL queries for managing users, databases, and tables.

Share this page:

Suggested articles

0 Comment(s)

Add comment