Introduction

PostgreSQL is one of the world's most popular open-source relational database management systems (RDBMS). Known for its reliability, extensibility, SQL compliance, and enterprise-grade performance, PostgreSQL is widely adopted by startups, Fortune 500 companies, government organizations, and cloud providers.

With native support across AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL, Oracle Cloud Infrastructure (OCI), and Kubernetes, PostgreSQL has become a must-have skill for modern Database Administrators and Cloud Engineers.

This guide demonstrates how to install PostgreSQL 16 on Red Hat Enterprise Linux (RHEL) 8, initialize the database cluster, configure remote access, create databases and users, and perform basic administration tasks. The installation steps in this article are based on the commands and workflow from the accompanying installation guide.

Why PostgreSQL?

PostgreSQL offers enterprise capabilities without licensing costs.

Key Advantages

100% Open Source
ACID Compliant
Cross-platform support
Advanced SQL features
JSON and JSONB support
MVCC (Multi-Version Concurrency Control)
High Availability and Streaming Replication
Logical Replication
Partitioning
Full-text Search
Rich extension ecosystem

Popular extensions include:

PostGIS
TimescaleDB
pgAudit
pg_stat_statements
pg_cron

Why Should Oracle DBAs Learn PostgreSQL?

As organizations modernize their database infrastructure, PostgreSQL is increasingly used alongside Oracle in hybrid and cloud-native environments.

Learning PostgreSQL helps Oracle professionals:

Expand into cloud database administration.
Participate in Oracle-to-PostgreSQL migration projects.
Manage open-source databases alongside Oracle.
Qualify for broader DBA and Cloud Engineer roles.
Reduce reliance on proprietary database platforms.

Installation Overview

The installation process includes:

Verify the operating system.
Disable the default PostgreSQL module.
Install the official PostgreSQL repository.
Install PostgreSQL Server.
Initialize the database cluster.
Start and enable the PostgreSQL service.
Connect as the postgres user.
Set a password for the superuser.
Enable remote connections (optional).
Configure the firewall (optional).
Create a database.
Create an application user.
Connect using psql.

Step 1: Verify the Operating System

Confirm the RHEL version before installation.

cat /etc/redhat-release

This ensures that the correct PostgreSQL repository package is used.

Step 2: Disable the Default PostgreSQL Module

RHEL includes a built-in PostgreSQL module. Disable it before installing the latest PostgreSQL release.

sudo dnf -qy module disable postgresql

Verify:

dnf module list postgresql

The module should appear as disabled.

Step 3: Install the Official PostgreSQL Repository

Install the PostgreSQL Global Development Group (PGDG) repository.

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

Using the official repository ensures access to the latest PostgreSQL releases and updates.

Step 4: Install PostgreSQL 16

Install both the PostgreSQL server and client packages.

sudo dnf install -y postgresql16-server postgresql16

Verify the installation:

psql --version

Expected output:

psql (PostgreSQL) 16.x

Step 5: Initialize the Database Cluster

Before PostgreSQL can start, initialize the data directory.

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

This creates the database cluster, configuration files, and system catalogs.

Step 6: Start and Enable PostgreSQL

Enable PostgreSQL to start automatically during boot.

sudo systemctl enable postgresql-16

Start the service:

sudo systemctl start postgresql-16

Verify status:

sudo systemctl status postgresql-16

Or:

sudo systemctl is-active postgresql-16

Expected output:

active

Step 7: Connect as the PostgreSQL Superuser

Switch to the PostgreSQL operating system user.

sudo su - postgres

Launch the PostgreSQL interactive terminal.

psql

You should now see the PostgreSQL prompt:

postgres=#

Step 8: Set the Superuser Password

Configure a password for the postgres database user.

ALTER USER postgres PASSWORD 'StrongPassword';

Using a strong password is essential, especially if remote access is enabled.

Step 9: Enable Remote Connections (Optional)

By default, PostgreSQL accepts only local connections.

To allow remote clients:

Edit postgresql.conf:

listen_addresses='*'

Update pg_hba.conf:

host    all    all    0.0.0.0/0    md5

Restart PostgreSQL:

sudo systemctl restart postgresql-16

These settings enable password-based authentication for remote clients. Restrict the allowed IP ranges in production for better security.

Step 10: Configure the Firewall (Optional)

Open PostgreSQL's default port (5432).

sudo firewall-cmd --permanent --add-port=5432/tcp

Reload firewall rules:

sudo firewall-cmd --reload

Verify:

sudo firewall-cmd --list-ports

Step 11: Verify the Listening Port

Confirm PostgreSQL is listening on port 5432.

ss -tulpn | grep 5432

This confirms that the PostgreSQL service is ready to accept client connections.

Creating Your First Database

Create a new database.

createdb testdb

Connect:

psql testdb

You should now see:

testdb=#

Creating an Application User

Create a dedicated database user.

CREATE USER appuser
WITH PASSWORD 'Password123';

Grant privileges.

GRANT ALL PRIVILEGES
ON DATABASE testdb
TO appuser;

Using application-specific users instead of the postgres superuser improves security and follows the principle of least privilege.

Connecting Using Password Authentication

Use the new application user to connect.

psql -U appuser -d testdb -h localhost

You'll be prompted to enter the user's password before accessing the database.

Essential `psql` Commands

Command	Description
`\l`	List databases
`\du`	List users/roles
`\dt`	List tables
`\conninfo`	Display current connection details
`\q`	Exit `psql`

These built-in commands are useful for day-to-day administration.

PostgreSQL Security Best Practices

Use strong passwords for privileged accounts.
Restrict remote access to trusted IP addresses.
Avoid using the postgres account for applications.
Grant only the privileges required by each application.
Regularly apply PostgreSQL and operating system updates.
Enable SSL/TLS for remote connections in production.
Perform routine backups using pg_dump or pg_basebackup.

Real-World Use Cases

PostgreSQL is commonly deployed for:

Enterprise business applications
Financial systems
E-commerce platforms
Healthcare systems
GIS applications with PostGIS
Time-series workloads with TimescaleDB
Cloud-native microservices
Data warehousing and analytics

Troubleshooting Tips

Issue	Possible Cause	Resolution
Unable to connect	PostgreSQL service not running	Start the service with `systemctl`
Connection refused	`listen_addresses` not configured	Update `postgresql.conf` and restart
Authentication failed	`pg_hba.conf` rules or password mismatch	Review authentication rules and credentials
Port 5432 inaccessible	Firewall blocking traffic	Open the port using `firewall-cmd`

Conclusion

PostgreSQL 16 offers a modern, enterprise-grade database platform with the flexibility and cost advantages of open-source software. Following the installation process—disabling the default module, using the official repository, initializing the database cluster, configuring networking, and creating databases and users—provides a solid foundation for development, testing, and production deployments.

For Oracle DBAs, PostgreSQL is more than an alternative database. It is an increasingly important technology in cloud migrations, multi-database environments, and open-source modernization initiatives. Building proficiency in PostgreSQL alongside Oracle broadens technical expertise and prepares you for the growing demand for cross-platform database administration.

Muthukumar Cloud Oracle DBA & Exadata Knowledge Hub

Search This Blog

PostgreSQL 16 Installation on RHEL 8: Complete Step-by-Step Guide for DBAs

Introduction

Why PostgreSQL?

Key Advantages

Why Should Oracle DBAs Learn PostgreSQL?

Installation Overview

Step 1: Verify the Operating System

Step 2: Disable the Default PostgreSQL Module

Step 3: Install the Official PostgreSQL Repository

Step 4: Install PostgreSQL 16

Step 5: Initialize the Database Cluster

Step 6: Start and Enable PostgreSQL

Step 7: Connect as the PostgreSQL Superuser

Step 8: Set the Superuser Password

Step 9: Enable Remote Connections (Optional)

Step 10: Configure the Firewall (Optional)

Step 11: Verify the Listening Port

Creating Your First Database

Creating an Application User

Connecting Using Password Authentication

Essential `psql` Commands

PostgreSQL Security Best Practices

Real-World Use Cases

Troubleshooting Tips

Conclusion

Comments

Post a Comment

PostgreSQL 16 Installation on RHEL 8: Complete Step-by-Step Guide for DBAs

Introduction

Why PostgreSQL?

Key Advantages

Why Should Oracle DBAs Learn PostgreSQL?

Installation Overview

Step 1: Verify the Operating System

Step 2: Disable the Default PostgreSQL Module

Step 3: Install the Official PostgreSQL Repository

Step 4: Install PostgreSQL 16

Step 5: Initialize the Database Cluster

Step 6: Start and Enable PostgreSQL

Step 7: Connect as the PostgreSQL Superuser

Step 8: Set the Superuser Password

Step 9: Enable Remote Connections (Optional)

Step 10: Configure the Firewall (Optional)

Step 11: Verify the Listening Port

Creating Your First Database

Creating an Application User

Connecting Using Password Authentication

Essential psql Commands

PostgreSQL Security Best Practices

Real-World Use Cases

Troubleshooting Tips

Conclusion

Comments

Post a Comment

Essential `psql` Commands