How to install PostgreSQL on Rocky Linux 10

Master How to install PostgreSQL on Rocky Linux 10 in minutes! Learn the exact steps to install, configure, and secure your database for peak performance. Don’t risk downtime—follow this guide now before your next project demands it! #centlinux #linux #postgres

---

---

Introduction

What is PostgreSQL?

PostgreSQL, often called Postgres, is an advanced open-source relational database management system (RDBMS) known for its stability, flexibility, and strong feature set. It supports complex queries, ACID compliance, JSON data types, and full-text search capabilities, making it a top choice for developers and enterprises. What sets PostgreSQL apart from other databases is its extensibility—you can create your own data types, functions, and even write code in multiple languages inside the database itself. PostgreSQL is also highly standards-compliant, following the SQL standard closely while adding innovative capabilities that go beyond typical database systems.

If you’re building anything from a small app to a large-scale enterprise system, PostgreSQL provides the reliability and scalability you need. It’s used by major companies like Apple, Cisco, and Instagram, and is trusted in financial, government, and educational institutions.

Overview of Rocky Linux 10

Rocky Linux is an enterprise-grade, open-source Linux distribution designed as a community-driven replacement for CentOS. Rocky Linux 10 continues this tradition with long-term support, stability, and compatibility with Red Hat Enterprise Linux (RHEL). It’s ideal for servers and production workloads where reliability is essential. Since it’s binary-compatible with RHEL, you get access to the same security updates and packages without the licensing costs.

Why Choose PostgreSQL for Rocky Linux 10?

PostgreSQL and Rocky Linux make a powerful combination for database hosting. Rocky Linux’s stability ensures that PostgreSQL runs smoothly without unexpected OS-level changes breaking compatibility. This is critical for production environments where uptime is essential. Additionally, Rocky Linux’s security-first approach complements PostgreSQL’s robust role-based access control and encryption features. Together, they create a secure, scalable, and high-performance environment perfect for both small-scale and enterprise-level applications.

---

Prerequisites to Install PostgreSQL on Rocky Linux 10

System Requirements

Before diving into the installation process, ensure that your system meets the basic requirements:

• Operating System: Rocky Linux 10 (64-bit)
• CPU: 1 GHz or faster processor
• RAM: Minimum 1 GB (2 GB recommended for production)
• Disk Space: At least 1 GB free space for PostgreSQL installation (more if you store large datasets)
• Network Access: Required for downloading PostgreSQL packages

If you’re deploying PostgreSQL for a production database, aim for at least 4 GB RAM and SSD storage for better performance.

Recommended Training: SQL and PostgreSQL for Beginners: Become a SQL Expert from Jon Avis

Updating Your Rocky Linux 10 System

Before installing PostgreSQL, update your system to ensure you have the latest packages and security patches. Run:

sudo dnf update -y

This step ensures that your installation is stable and avoids compatibility issues with outdated system libraries.

Installing Required Dependencies

PostgreSQL typically needs certain tools and libraries. Install them with:

sudo dnf install -y gcc make wget tar

These tools are especially useful if you plan to compile PostgreSQL from source or need extra utilities for database management.

---

Step-by-Step Guide to Installing PostgreSQL

Checking the Default PostgreSQL Version in Rocky Linux 10

Rocky Linux 10’s default repository often includes a stable but slightly older version of PostgreSQL. To check what’s available, run:

sudo dnf module list postgresql

You’ll see a list of available versions, each marked with a default stream.

Installing PostgreSQL from the Default Rocky Linux Repositories

If you’re fine with the default version, installation is straightforward:

sudo dnf install -y postgresql-server postgresql-contrib

The postgresql-contrib package adds extra utilities and extensions like pgcrypto and uuid-ossp.

Installing a Specific Version from the PostgreSQL Repository

If you need a newer version, enable the official PostgreSQL repository:

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

Then, disable the default module to avoid conflicts:

sudo dnf -qy module disable postgresql

Finally, install your desired version, for example PostgreSQL 16:

sudo dnf install -y postgresql16-server postgresql16-contrib

---

---

Initializing and Starting PostgreSQL Service

Initializing the Database Cluster

Once PostgreSQL is installed, you need to initialize the database cluster (data directory):

For default repo installs:

sudo postgresql-setup --initdb

For version-specific installs:

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

This prepares the system to store and manage databases.

Read Also: Install PostGIS Extension in PostgreSQL 13

Enabling and Starting PostgreSQL Service

Enable PostgreSQL to start on boot:

sudo systemctl enable postgresql
sudo systemctl start postgresql

For version-specific services:

sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

Checking PostgreSQL Status

Verify that PostgreSQL is running:

sudo systemctl status postgresql

A green “active (running)” status means you’re good to go.

---

Configuring PostgreSQL for First-Time Use

Switching to the PostgreSQL User

By default, PostgreSQL creates a postgres system user. Switch to it:

sudo -i -u postgres

Accessing PostgreSQL Prompt (psql)

From the postgres user shell, enter:

psql

You’ll now see the PostgreSQL command prompt where you can manage databases.

Changing Default Password and Creating a New Database

Set a password for the postgres database user:

\password postgres

Create a new database:

CREATE DATABASE mydb;

Create a new user with a password:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

Grant the new user privileges:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

---

---

Enabling Remote Connections to PostgreSQL

Editing postgresql.conf

By default, PostgreSQL only listens for local connections. If you need to allow connections from other machines (for example, to connect from a web server or developer machine), you must update the postgresql.conf file.

The location of the file depends on your PostgreSQL version. For PostgreSQL 16 installed from the official repo, it might be in:

/var/lib/pgsql/16/data/postgresql.conf

Open it with vi or nano:

sudo nano /var/lib/pgsql/16/data/postgresql.conf

Find the line:

#listen_addresses = 'localhost'

Uncomment it and change it to:

listen_addresses = '*'

This allows PostgreSQL to listen on all available network interfaces. If you want to restrict to a specific IP, replace * with that IP address.

Read Also: How to install PostgreSQL on Ubuntu Server 18

---

Configuring pg_hba.conf

The pg_hba.conf file controls which clients can connect and how they authenticate. It’s usually located in the same directory as postgresql.conf.

Open it:

sudo nano /var/lib/pgsql/16/data/pg_hba.conf

At the bottom, add a line like this to allow password authentication from any IP:

host    all             all             0.0.0.0/0               md5

For more security, replace 0.0.0.0/0 with a specific subnet or IP address. For example, if your web server’s IP is 192.168.1.50:

host    all             all             192.168.1.50/32         md5

---

Restarting PostgreSQL Service

After making these changes, restart PostgreSQL so they take effect:

sudo systemctl restart postgresql-16

If you installed the default repo version:

sudo systemctl restart postgresql

At this point, PostgreSQL should accept remote connections from allowed IP addresses using the configured authentication method.

---

Securing Your PostgreSQL Installation

Setting Strong Passwords

The most basic security step is to ensure that all database users have strong, unique passwords. Avoid common words and short passwords—use a combination of uppercase, lowercase, numbers, and special characters.

Example of a strong password:
Xy!4gTq@9Kz

To update a user’s password in PostgreSQL:

ALTER USER myuser WITH PASSWORD 'new_strong_password';

---

Configuring Firewall for PostgreSQL

PostgreSQL uses port 5432 by default. If your Rocky Linux 10 server uses firewalld, you need to allow this port:

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

Only open this port to trusted networks. If you know the IP address of your application server, restrict access like this:

sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='192.168.1.50' port protocol='tcp' port='5432' accept"
sudo firewall-cmd --reload

---

Limiting Access to Trusted IPs

Even with strong passwords, you should limit database access to only the servers that need it. This is done in the pg_hba.conf file, as shown earlier, by replacing 0.0.0.0/0 with a more specific IP range.

For example, to allow only connections from a corporate network:

host    all             all             203.0.113.0/24          md5

This prevents unauthorized access attempts from the wider internet.

---

Useful PostgreSQL Administration Commands

Creating and Managing Databases

PostgreSQL makes it easy to create and drop databases:

createdb mydatabase
dropdb mydatabase

From inside psql:

CREATE DATABASE exampledb;
DROP DATABASE exampledb;

---

---

Managing Roles and Permissions

Roles in PostgreSQL can act as users or groups. To create a new role:

CREATE ROLE devuser WITH LOGIN PASSWORD 'securepass';

Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE exampledb TO devuser;

Revoke privileges if needed:

REVOKE ALL PRIVILEGES ON DATABASE exampledb FROM devuser;

---

Backup and Restore in PostgreSQL

To back up a database:

pg_dump mydatabase > mydatabase.sql

To restore:

psql mydatabase < mydatabase.sql

For compressed backups:

pg_dump mydatabase | gzip > mydatabase.sql.gz

Restore compressed backups:

gunzip < mydatabase.sql.gz | psql mydatabase

---

Troubleshooting Common PostgreSQL Issues

---

Fixing Connection Refused Errors

A “connection refused” error usually means PostgreSQL is not listening on the IP address you’re trying to connect to or that the Linux firewall is blocking the port.

Steps to fix:

Check if PostgreSQL is running:

sudo systemctl status postgresql-16

or for the default version:

sudo systemctl status postgresql

Ensure PostgreSQL is listening on all interfaces:
Open postgresql.conf and verify listen_addresses = '*' or the correct IP.

Verify firewall rules:

sudo firewall-cmd --list-all

Make sure port 5432/tcp is open.

Confirm pg_hba.conf allows your IP:
Add a proper host entry if missing.

---

Resolving Permission Denied Errors

If you see a “permission denied” error when trying to access a database, it usually relates to role privileges.

To fix:

Ensure your user has privileges for that database:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

For table-level permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

For schema-level access:

GRANT USAGE ON SCHEMA public TO myuser;

Also, make sure you are connecting with the right username and authentication method.

---

Checking Logs for Errors

PostgreSQL logs are an invaluable troubleshooting tool. They are usually stored in:

/var/lib/pgsql/16/data/log/

Or, if logging is configured via journald, check:

sudo journalctl -u postgresql-16

Review logs after restarting the service to catch startup errors, authentication failures, or syntax mistakes in configuration files.

---

Conclusion

Installing PostgreSQL on Rocky Linux 10 is straightforward when you follow the right steps. From preparing your system and installing the database to configuring remote connections and securing it, you now have a robust PostgreSQL environment ready for production use. PostgreSQL’s flexibility, reliability, and performance make it a perfect match for Rocky Linux’s stability. Whether you’re running a small application or a large enterprise system, this setup provides a solid foundation.

Need expert AWS and Linux system administration? From cloud architecture to server optimization, I provide reliable and efficient solutions tailored to your needs. Hire me on Fiverr today!

---

Frequently Asked Questions (FAQs)

1. What port does PostgreSQL use by default?
PostgreSQL uses port 5432 by default for incoming connections.

2. How do I restart PostgreSQL after configuration changes?
Use:

sudo systemctl restart postgresql-16

(or postgresql if using the default version).

3. Can I run multiple PostgreSQL versions on Rocky Linux 10?
Yes. By using the official PostgreSQL Yum repository, you can install multiple versions side-by-side.

4. How do I completely remove PostgreSQL from Rocky Linux 10?
Run:

sudo dnf remove postgresql* -y

and optionally delete the data directory.

5. Is PostgreSQL better than MySQL for Rocky Linux 10?
It depends on your needs—PostgreSQL offers more advanced features and standards compliance, while MySQL is often simpler for small applications.

---