How to Install PostgreSQL in Rocky Linux 9

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.

Update the system

First, we will update the system to the latest with the following commands,

dnf update

Install PostgreSQL Repository

Add the official PostgreSQL Repository for the Rocky Linux system by running the below command,

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

Install the PostgreSQL Server

At the time of writing this article, version 14.4 is the latest version available from PostgreSQL, we will manually select this version for installation in our case.

Check PostgreSQL official website to find out the latest version available at the time of installation.

Install the latest version of PostgreSQL with the below command,

dnf install -y postgresql14-server

Output:

[root@vps ~]# dnf install -y postgresql14-server
Last metadata expiration check: 0:00:36 ago on Thu 28 Jul 2022 04:52:12 PM UTC.
Dependencies resolved.
=================================================================================================================================
 Package                              Architecture            Version                              Repository               Size
=================================================================================================================================
Installing:
 postgresql14-server                  x86_64                  14.4-1PGDG.rhel9                     pgdg14                  5.8 M
Installing dependencies:
 libicu                               x86_64                  67.1-9.el9                           baseos                  9.6 M
 lz4                                  x86_64                  1.9.3-5.el9                          baseos                   58 k
 postgresql14                         x86_64                  14.4-1PGDG.rhel9                     pgdg14                  1.4 M
 postgresql14-libs                    x86_64                  14.4-1PGDG.rhel9                     pgdg14                  281 k

Transaction Summary
=================================================================================================================================
Install  5 Packages

Creating a New PostgreSQL Database Cluster

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

/usr/pgsql-14/bin/postgresql-14-setup initdb

Output:

[root@vps ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

Start the PostgreSQL service:

systemctl start postgresql-14

Enable the PostgreSQL service:

systemctl enable postgresql-14

Verify the PostgreSQL service:

systemctl status postgresql-14

Output:

[root@vps ~]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
     Active: active (running) since Thu 2022-07-28 16:53:54 UTC; 11s ago
       Docs: https://www.postgresql.org/docs/14/static/
    Process: 28389 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 28394 (postmaster)
      Tasks: 8 (limit: 11129)
     Memory: 16.4M
        CPU: 75ms
     CGroup: /system.slice/postgresql-14.service
             ├─28394 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
             ├─28395 "postgres: logger "
             ├─28397 "postgres: checkpointer "
             ├─28398 "postgres: background writer "
             ├─28399 "postgres: walwriter "
             ├─28400 "postgres: autovacuum launcher "
             ├─28401 "postgres: stats collector "
             └─28402 "postgres: logical replication launcher "

PostgreSQL Roles

We'll switch to a postgres account for this.

sudo -i -u postgres

you can access a PostgreSQL prompt using the psql utility.

psql

Output:

[postgres@vps ~]$ psql
psql (14.4)
Type "help" for help.

postgres=#

To exit out of the PostgreSQL shell type.

\q

Switch back to the root user account by exiting the current user mode with 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

[postgres@vps ~]$ createdb jones

Opening a Postgres Prompt with the New Role

Add a new user by using the following command:

sudo adduser jones

To switch over and connect to the database:

sudo -i -u jones

psql

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".