How to Install PostgreSQL on RHEL-9

PostgreSQL is a powerful, open-source object-relational database system that enhances SQL with advanced features to handle even the most complex data workloads. Its roots go back to 1986 as part of the POSTGRES project at the University of California, Berkeley, and it has benefited from over 35 years of continuous development.

Renowned for its solid architecture, reliability, data integrity, and extensibility, PostgreSQL is backed by a dedicated open-source community that consistently delivers high-performance and innovative solutions. It runs on all major operating systems, has been ACID-compliant since 2001, and supports powerful extensions like PostGIS for geospatial data.

With its versatility and robust feature set, PostgreSQL has become the go-to open-source relational database for individuals and organizations worldwide. Getting started is easier than ever—just choose a project, and let PostgreSQL securely and efficiently manage your data.

Why use PostgreSQL?

PostgreSQL: A Powerful, Extensible, and Reliable Database

PostgreSQL is a robust, open-source relational database designed to help developers build applications, empower administrators to maintain data integrity, and efficiently manage datasets of any size. Not only is it free to use, but it is also highly extensible—allowing users to define custom data types, create tailored functions, and even write code in multiple programming languages without recompiling the database.

Committed to SQL standards, PostgreSQL aims for maximum compliance without compromising its core strengths. As of version 16 (released in September 2023), it supports at least 170 out of the 177 mandatory features in SQL:2023 Core conformance—one of the highest levels among relational databases. However, no database yet achieves full compliance with the standard.

Key Features of PostgreSQL

Diverse Data Types

• Primitives: Integer, Numeric, String, Boolean
• Structured: Date/Time, Array, Range/Multirange, UUID
• Document-Based: JSON/JSONB, XML, Key-value (Hstore)
• Geometric: Point, Line, Circle, Polygon
• Custom Types: Composite types, user-defined data types

Data Integrity & Security

• Constraints: UNIQUE, NOT NULL, Primary Keys, Foreign Keys, Exclusion Constraints
• Locks: Explicit Locks, Advisory Locks
• Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate-based authentication
• Access Control: Robust permission system, row- and column-level security
• Multi-factor authentication (MFA) with certificates and additional security methods

High Performance & Concurrency

• Advanced indexing: B-tree, Multicolumn, Expressions, Partial, GiST, SP-GiST, KNN GiST, GIN, BRIN, Covering Indexes, Bloom Filters
• Sophisticated query planner and optimizer
• Index-only scans and multicolumn statistics
• Multi-Version Concurrency Control (MVCC)
• Support for all SQL transaction isolation levels, including Serializable
• Parallel query execution and B-tree index building
• Table partitioning
• Just-in-time (JIT) compilation for improved query performance

Reliability & Disaster Recovery

• Write-Ahead Logging (WAL)
• Replication: Asynchronous, Synchronous, Logical
• Point-in-Time Recovery (PITR) and active standby databases
• Tablespaces for better data organization

Extensibility & Customization

• Stored functions and procedures
• Support for multiple procedural languages: PL/pgSQL, Perl, Python, Tcl, and extensions for Java, JavaScript (V8), R, Lua, and Rust
• SQL/JSON functionality, including constructors, path expressions, and JSON_TABLE
• Foreign Data Wrappers (FDWs) to connect to external databases or data streams
• Custom storage interfaces for tables
• Extensive extensions, including PostGIS for geospatial analysis

Internationalization & Full-Text Search

• Support for multiple character sets and ICU collations
• Case-insensitive and accent-insensitive collation options
• Full-text search capabilities for advanced text-based queries

Scalability & Real-World Performance

PostgreSQL is highly scalable, both in terms of handling massive data volumes and supporting a high number of concurrent users. Production environments use PostgreSQL to manage terabytes—and even petabytes—of data while maintaining performance and reliability.

With each major release, PostgreSQL continues to expand its capabilities, making it one of the most powerful and flexible database solutions available. Whether you’re working on a small application or a large enterprise system, PostgreSQL provides the tools you need to store, manage, and query data efficiently.

Add the PostgreSQL Repository

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

Disable the built-in PostgreSQL module

# dnf -qy module disable postgresql

Install PostgreSQL

# dnf install postgresql17-server

Initialize the database as shown below

# sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

How to Manage the PostgreSQL Service

# systemctl start postgresql-17.service
# systemctl enable postgresql-17.service
# systemctl status postgresql-17.service

How to Enable Authentication in PostgreSQL

# sudo -u postgres psql

# postgres=# \q ##quit
# postgres=# \password postgres

Access the PostgreSQL access policy configuration file

# vim /var/lib/pgsql/17/data/pg_hba.conf
local all all md5
# systemctl reload postgresql-17.service

Access the postgres database

# sudo -u postgres psql
# postgres=# \du

Check list of databases

# postgres=# \l