How to Install PostgreSQL on Rocky Linux 9.2
To Install PostgreSQL On Rocky Linux 9.2
Introduction
PostgreSQL, an open-source object-relational database known for its high performance and reliability, is regarded as one of the most advanced databases. Several features make it easier for developers and administrators to deploy data-driven applications.
Installation Steps:
Step 1: Check the OS version by using the below command
[root@Linuxhelp ~]# cat /etc/os-release
NAME="Rocky Linux"
VERSION="9.2 (Blue Onyx)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="9.2"
PLATFORM_ID="platform:el9"
PRETTY_NAME="Rocky Linux 9.2 (Blue Onyx)"
ANSI_COLOR="0;32"
LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:rocky:rocky:9::baseos"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
SUPPORT_END="2032-05-31"
ROCKY_SUPPORT_PRODUCT="Rocky-Linux-9"
ROCKY_SUPPORT_PRODUCT_VERSION="9.2"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="9.2"
Step 2: Update your system with the latest packages by using the below command
[root@Linuxhelp ~]# dnf update
Rocky Linux 9 - BaseOS 4.4 kB/s | 4.1 kB 00:00
Rocky Linux 9 - AppStream 4.1 kB/s | 4.5 kB 00:01
Rocky Linux 9 - AppStream 3.2 MB/s | 7.1 MB 00:02
Rocky Linux 9 - Extras 3.2 kB/s | 2.9 kB 00:00
Dependencies resolved.
=====================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================
Upgrading:
open-vm-tools x86_64 12.1.5-1.el9_2.1 appstream 851 k
open-vm-tools-desktop x86_64 12.1.5-1.el9_2.1 appstream 158 k
Transaction Summary
=====================================================================================================================
Upgrade 2 Packages
Total download size: 1.0 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): open-vm-tools-desktop-12.1.5-1.el9_2.1.x86_64.rpm 761 kB/s | 158 kB 00:00
(2/2): open-vm-tools-12.1.5-1.el9_2.1.x86_64.rpm 3.0 MB/s | 851 kB 00:00
---------------------------------------------------------------------------------------------------------------------
Total 935 kB/s | 1.0 MB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Upgrading : open-vm-tools-12.1.5-1.el9_2.1.x86_64 1/4
Running scriptlet: open-vm-tools-12.1.5-1.el9_2.1.x86_64 1/4
Upgrading : open-vm-tools-desktop-12.1.5-1.el9_2.1.x86_64 2/4
Running scriptlet: open-vm-tools-desktop-12.1.5-1.el9_2.1.x86_64 2/4
Running scriptlet: open-vm-tools-desktop-12.1.5-1.el9.x86_64 3/4
Cleanup : open-vm-tools-desktop-12.1.5-1.el9.x86_64 3/4
Running scriptlet: open-vm-tools-desktop-12.1.5-1.el9.x86_64 3/4
Running scriptlet: open-vm-tools-12.1.5-1.el9.x86_64 4/4
Cleanup : open-vm-tools-12.1.5-1.el9.x86_64 4/4
Running scriptlet: open-vm-tools-12.1.5-1.el9.x86_64 4/4
Verifying : open-vm-tools-desktop-12.1.5-1.el9_2.1.x86_64 1/4
Verifying : open-vm-tools-desktop-12.1.5-1.el9.x86_64 2/4
Verifying : open-vm-tools-12.1.5-1.el9_2.1.x86_64 3/4
Verifying : open-vm-tools-12.1.5-1.el9.x86_64 4/4
Upgraded:
open-vm-tools-12.1.5-1.el9_2.1.x86_64 open-vm-tools-desktop-12.1.5-1.el9_2.1.x86_64
Complete!
Step 3: Add PostgreSQL Repository to our system by running the below command
[root@Linuxhelp ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:00:15 ago on Sat 08 Jul 2023 06:56:20 AM IST.
pgdg-redhat-repo-latest.noarch.rpm 9.6 kB/s | 13 kB 00:01
Dependencies resolved.
=====================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-32 @commandline 13 k
Transaction Summary
=====================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 14 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-32.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-32.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-32.noarch
Complete!
Step 4: Install PostgreSQL by running the below command
[root@Linuxhelp ~]# dnf install -y postgresql15-server postgresql15-contrib
PostgreSQL common RPMs for RHEL / Rocky 9 - x86_64 135 B/s | 195 B 00:01
PostgreSQL common RPMs for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL common RPMs for RHEL / Rocky 9 - x86_64 151 kB/s | 401 kB 00:02
PostgreSQL 15 for RHEL / Rocky 9 - x86_64 139 B/s | 195 B 00:01
PostgreSQL 15 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL 15 for RHEL / Rocky 9 - x86_64 69 kB/s | 267 kB 00:03
PostgreSQL 14 for RHEL / Rocky 9 - x86_64 150 B/s | 195 B 00:01
PostgreSQL 14 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL 14 for RHEL / Rocky 9 - x86_64 185 kB/s | 519 kB 00:02
PostgreSQL 13 for RHEL / Rocky 9 - x86_64 128 B/s | 195 B 00:01
PostgreSQL 13 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL 13 for RHEL / Rocky 9 - x86_64 142 kB/s | 495 kB 00:03
PostgreSQL 12 for RHEL / Rocky 9 - x86_64 142 B/s | 195 B 00:01
PostgreSQL 12 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL 12 for RHEL / Rocky 9 - x86_64 163 kB/s | 450 kB 00:02
PostgreSQL 11 for RHEL / Rocky 9 - x86_64 105 B/s | 195 B 00:01
PostgreSQL 11 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
PostgreSQL 11 for RHEL / Rocky 9 - x86_64 167 kB/s | 448 kB 00:02
Dependencies resolved.
=====================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================
Installing:
postgresql15-contrib x86_64 15.3-1PGDG.rhel9 pgdg15 712 k
postgresql15-server x86_64 15.3-1PGDG.rhel9 pgdg15 6.0 M
Installing dependencies:
lz4 x86_64 1.9.3-5.el9 baseos 58 k
postgresql15 x86_64 15.3-1PGDG.rhel9 pgdg15 1.5 M
postgresql15-libs x86_64 15.3-1PGDG.rhel9 pgdg15 296 k
Transaction Summary
=====================================================================================================================
Install 5 Packages
Total download size: 8.5 M
Installed size: 36 M
Downloading Packages:
(1/5): postgresql15-libs-15.3-1PGDG.rhel9.x86_64.rpm 213 kB/s | 296 kB 00:01
(2/5): postgresql15-15.3-1PGDG.rhel9.x86_64.rpm 908 kB/s | 1.5 MB 00:01
(3/5): lz4-1.9.3-5.el9.x86_64.rpm 144 kB/s | 58 kB 00:00
(4/5): postgresql15-server-15.3-1PGDG.rhel9.x86_64.rpm 5.3 MB/s | 6.0 MB 00:01
(5/5): postgresql15-contrib-15.3-1PGDG.rhel9.x86_64.rpm 247 kB/s | 712 kB 00:02
---------------------------------------------------------------------------------------------------------------------
Total 2.3 MB/s | 8.5 MB 00:03
PostgreSQL 15 for RHEL / Rocky 9 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql15-libs-15.3-1PGDG.rhel9.x86_64 1/5
Running scriptlet: postgresql15-libs-15.3-1PGDG.rhel9.x86_64 1/5
Installing : lz4-1.9.3-5.el9.x86_64 2/5
Installing : postgresql15-15.3-1PGDG.rhel9.x86_64 3/5
Running scriptlet: postgresql15-15.3-1PGDG.rhel9.x86_64 3/5
Running scriptlet: postgresql15-server-15.3-1PGDG.rhel9.x86_64 4/5
Installing : postgresql15-server-15.3-1PGDG.rhel9.x86_64 4/5
Running scriptlet: postgresql15-server-15.3-1PGDG.rhel9.x86_64 4/5
Installing : postgresql15-contrib-15.3-1PGDG.rhel9.x86_64 5/5
Running scriptlet: postgresql15-contrib-15.3-1PGDG.rhel9.x86_64 5/5
Verifying : postgresql15-15.3-1PGDG.rhel9.x86_64 1/5
Verifying : postgresql15-contrib-15.3-1PGDG.rhel9.x86_64 2/5
Verifying : postgresql15-libs-15.3-1PGDG.rhel9.x86_64 3/5
Verifying : postgresql15-server-15.3-1PGDG.rhel9.x86_64 4/5
Verifying : lz4-1.9.3-5.el9.x86_64 5/5
Installed:
lz4-1.9.3-5.el9.x86_64 postgresql15-15.3-1PGDG.rhel9.x86_64
postgresql15-contrib-15.3-1PGDG.rhel9.x86_64 postgresql15-libs-15.3-1PGDG.rhel9.x86_64
postgresql15-server-15.3-1PGDG.rhel9.x86_64
Complete!
Step 5: Check the PostgreSQL version by using the below command
[root@Linuxhelp ~]# psql -V
psql (PostgreSQL) 15.3
Step 6: Initialize PostgreSQL Database by using the below command
[root@Linuxhelp ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
Step 7: Start & Enable the PostgreSQL Service by using the below command
[root@Linuxhelp ~]# systemctl start postgresql-15
[root@Linuxhelp ~]# systemctl enable postgresql-15
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service.
Step 8: Check the status of the PostgreSQL Service by using the below command
[root@Linuxhelp ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; preset: disabled)
Active: active (running) since Sat 2023-07-08 07:00:47 IST; 43s ago
Docs: https://www.postgresql.org/docs/15/static/
Main PID: 7201 (postmaster)
Tasks: 7 (limit: 22877)
Memory: 17.3M
CPU: 82ms
CGroup: /system.slice/postgresql-15.service
├─7201 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─7202 "postgres: logger "
├─7203 "postgres: checkpointer "
├─7204 "postgres: background writer "
├─7206 "postgres: walwriter "
├─7207 "postgres: autovacuum launcher "
└─7208 "postgres: logical replication launcher "
Jul 08 07:00:47 Linuxhelp systemd[1]: Starting PostgreSQL 15 database server...
Jul 08 07:00:47 Linuxhelp postmaster[7201]: 2023-07-08 07:00:47.219 IST [7201] LOG: redirecting log output to loggi>
Jul 08 07:00:47 Linuxhelp postmaster[7201]: 2023-07-08 07:00:47.219 IST [7201] HINT: Future log output will appear >
Jul 08 07:00:47 Linuxhelp systemd[1]: Started PostgreSQL 15 database server.
lines 1-21/21 (END)
Step 9: Connect to PostgreSQL server with the Postgres user and Create a New User & Database by using the below command
[root@Linuxhelp ~]# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (15.3)
Type "help" for help.
postgres=# create user linuxhelp with password 'Linux@123';
CREATE ROLE
postgres=# create database linuxhelp_db;
CREATE DATABASE
postgres=# grant all privileges on database linuxhelp_db to linuxhelp;
GRANT
These commands will create a new user called myuser with the password mypassword and a new database named mydb. The third command grants all privileges on the mydb database to the myuser user.
Step 10: Check the created Database by using the below command
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
--------------+----------+----------+-------------+-------------+------------+-----------------+---------------------
---
linuxhelp_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/postgres
+
| | | | | | | postgres=CTc/postgre
s +
| | | | | | | linuxhelp=CTc/postgr
es
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres
+
| | | | | | | postgres=CTc/postgre
s
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres
+
| | | | | | | postgres=CTc/postgre
s
(4 rows)
Conclusion:
We have reached the end of this article. In this guide, we have walked you through the steps required to Install PostgreSQL on Rocky Linux 9.2. Your feedback is much welcome.
Q
How do I log in as a Postgres user?
A
Log on to Postgres user by using the command "sudo -u postgres"
Q
What is the default Postgres user?
A
The default Postgres user is "Postgres"
Q
Differentiate PostgreSQL and MySQL?
A
Postgres is an object-relational database, while MySQL is a purely relational database.
Q
How to list Postgres users?
A
Use \du or \du+ COMMAND to list Postgres users.
Q
How to delete the Postgres database?
A
Use the DROP DATABASE command to delete a not-needed database.