PostgreSQL (often called Postgres) is an advanced open-source relational database with a 35-year development history. Unlike MySQL/MariaDB, PostgreSQL prioritises SQL standards compliance, data integrity, and extensibility over speed-at-any-cost. PostgreSQL excels at complex queries, full-text search, JSONB document storage, geospatial data (PostGIS extension), and write-heavy workloads that require strong ACID compliance. It is the preferred database for Django, Rails, and many enterprise applications. RHEL 9’s AppStream includes PostgreSQL 15 and 16 as stream modules. PostgreSQL 17 is available via the official PostgreSQL APT/YUM repository. This guide covers installing PostgreSQL 16 on RHEL 9, initialising the cluster, creating roles and databases, configuring authentication, and tuning basic performance parameters.

Prerequisites

  • RHEL 9 with sudo/root access

Step 1 — Install PostgreSQL 16

# Option A: AppStream (PostgreSQL 16)
dnf module enable -y postgresql:16
dnf install -y postgresql-server postgresql-contrib
postgresql-setup --initdb
systemctl enable --now postgresql

# Option B: Official PostgreSQL repo (latest versions)
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf module disable -y postgresql    # Disable AppStream version
dnf install -y postgresql16-server postgresql16-contrib
/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable --now postgresql-16

Step 2 — Connect and Create a Database

# PostgreSQL uses the 'postgres' OS user for initial access (peer authentication)
sudo -u postgres psql
-- Inside psql
SELECT version();

-- Create a database role (user)
CREATE ROLE myapp_user WITH LOGIN PASSWORD 'StrongPassword123!';

-- Create the database owned by that role
CREATE DATABASE myapp OWNER myapp_user ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

-- Grant usage
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;
q

Step 3 — Configure Authentication (pg_hba.conf)

# /var/lib/pgsql/16/data/pg_hba.conf (adjust path for AppStream: /var/lib/pgsql/data/)
# Allow myapp_user to connect to myapp database from localhost using password
host    myapp    myapp_user    127.0.0.1/32    scram-sha-256

# After editing, reload PostgreSQL
systemctl reload postgresql-16

Step 4 — Configure postgresql.conf for Performance

# /var/lib/pgsql/16/data/postgresql.conf
listen_addresses     = 'localhost'

# Memory
shared_buffers       = 256MB    # 25% of RAM
effective_cache_size = 1GB      # 75% of RAM
work_mem             = 8MB      # per sort/hash operation
maintenance_work_mem = 128MB

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers          = 16MB

# Connections
max_connections      = 100

# Logging
log_min_duration_statement = 1000  # Log queries taking > 1 second
log_line_prefix      = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
systemctl restart postgresql-16

Step 5 — Allow Firewall Access for Remote Connections

# Only if remote access is needed — prefer SSH tunnels for security
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload

# Update pg_hba.conf to allow remote hosts:
# host myapp myapp_user 192.168.1.0/24 scram-sha-256

Step 6 — Test Connectivity

psql -U myapp_user -h 127.0.0.1 -d myapp -c "SELECT current_database(), current_user;"

Conclusion

PostgreSQL 16 on RHEL 9 provides a full-featured, standards-compliant relational database suitable for complex queries, JSONB storage, full-text search, and write-intensive workloads. The pg_hba.conf authentication file gives fine-grained control over which users can connect from which hosts using which authentication methods. Configuring shared_buffers and effective_cache_size to match available RAM is the most impactful performance tuning step after installation.

Next steps: How to Configure PostgreSQL Streaming Replication on RHEL 9, How to Back Up PostgreSQL with pg_dump on RHEL 9, and How to Configure PostgreSQL Remote Access and SSL on RHEL 9.