How To Install PostgreSQL on Rocky Linux 10

PostgreSQL stands as one of the most powerful and feature-rich open-source relational database management systems available today. With its advanced functionality, ACID compliance, and robust architecture, PostgreSQL has become the preferred choice for enterprises and developers worldwide who demand reliability and performance from their database solutions.

Rocky Linux 10, as an enterprise-grade operating system that maintains binary compatibility with Red Hat Enterprise Linux, provides an ideal foundation for hosting PostgreSQL databases. This combination delivers the stability and security required for production environments while offering long-term support and predictable update cycles that database administrators value.

This comprehensive guide will walk you through the complete process of installing PostgreSQL on Rocky Linux 10, from initial system preparation to final configuration and testing. Whether you’re a system administrator setting up a new database server or a developer preparing a local development environment, this tutorial provides the detailed instructions and best practices you need to succeed.

Prerequisites and System Requirements

Before beginning the PostgreSQL installation process, ensure your system meets the necessary requirements for optimal performance and compatibility.

Hardware Requirements

Your Rocky Linux 10 system should have adequate resources to support PostgreSQL operations effectively. A minimum of 1GB RAM is required, though 4GB or more is recommended for production environments. Storage requirements depend on your intended database size, but allocate at least 10GB of free disk space for the installation and initial database operations.

Consider the storage type carefully, as PostgreSQL performance benefits significantly from faster storage solutions. SSDs provide superior performance compared to traditional hard drives, particularly for write-intensive operations and index scanning.

Software Prerequisites

Verify that your Rocky Linux 10 installation is current and properly configured. You’ll need administrative access through sudo privileges to execute system-level commands throughout this installation process.

Ensure your system has reliable network connectivity for downloading packages and accessing repositories. A stable internet connection is essential for retrieving PostgreSQL packages and their dependencies from both the Rocky Linux repositories and the official PostgreSQL repository.

Pre-installation Checklist

Confirm your system’s current status by checking the firewall configuration and SELinux settings. These security features may require adjustment to allow PostgreSQL connections, and understanding their current state helps prevent configuration conflicts later in the installation process.

System Preparation

Proper system preparation ensures a smooth installation process and helps prevent common issues that can arise from outdated packages or incomplete system configurations.

Updating the System

Begin by updating all system packages to their latest versions. This critical step ensures you have the most recent security patches and bug fixes installed:

sudo dnf update -y

The update process may take several minutes depending on the number of available updates. If kernel updates are included, plan to reboot the system to ensure all changes take effect properly. A system reboot after major updates helps prevent compatibility issues and ensures all services start with the updated configurations.

Setting System Hostname

Configure a fully qualified domain name (FQDN) for your system if you haven’t already done so. While not strictly required for local installations, proper hostname configuration benefits database administration and network identification:

sudo hostnamectl set-hostname your-hostname.example.com

Verify the hostname change took effect by running hostname -f to display the fully qualified domain name.

Installing Prerequisites

Install essential packages that support the PostgreSQL installation process:

sudo dnf install -y wget curl nano

These utilities provide download capabilities, text editing functions, and general system administration tools that prove useful throughout the database setup and maintenance process.

PostgreSQL Repository Configuration

Rocky Linux 10 includes PostgreSQL packages in its default repositories, but using the official PostgreSQL repository ensures access to the latest versions and more frequent updates.

Understanding Repository Options

The default Rocky Linux repositories typically include older PostgreSQL versions that prioritize stability over cutting-edge features. While these versions receive security updates, they may lack the newest functionality and performance improvements available in recent PostgreSQL releases.

The official PostgreSQL Global Development Group repository provides access to multiple PostgreSQL versions, including the latest stable releases. This repository maintains packages specifically for Red Hat-based distributions, ensuring compatibility with Rocky Linux 10.

Adding Official PostgreSQL Repository

Install the PostgreSQL repository configuration package to enable access to official PostgreSQL packages:

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

This command downloads and installs the repository configuration, automatically adding the necessary GPG keys for package verification. The repository supports multiple PostgreSQL versions simultaneously, allowing you to choose the specific version that meets your requirements.

Verify the repository installation by listing available PostgreSQL packages:

dnf list available | grep postgresql

If you encounter conflicts with existing PostgreSQL modules, disable the default PostgreSQL module to prevent package conflicts:

sudo dnf -qy module disable postgresql

PostgreSQL Installation Methods

Rocky Linux 10 supports multiple PostgreSQL installation approaches, each with distinct advantages depending on your specific requirements and preferences.

Method 1: Installation from Official Repository

Installing PostgreSQL from the official repository provides access to the latest stable version, currently PostgreSQL 17, with regular updates and comprehensive package options:

sudo dnf install -y postgresql17-server postgresql17

This installation includes the PostgreSQL server daemon, client utilities, and essential libraries required for database operations. The package manager automatically resolves dependencies and installs any additional components needed for proper functionality.

The official repository packages follow naming conventions that include the version number, allowing multiple PostgreSQL versions to coexist on the same system when necessary. This flexibility proves valuable in development environments or during database migration projects.

Verify the installation by checking the installed package versions:

rpm -qa | grep postgresql

Method 2: Installation from Rocky Linux Default Repository

Alternative installations can use the default Rocky Linux repositories, which provide PostgreSQL packages optimized for the distribution:

sudo dnf install -y postgresql-server postgresql

While this method may install an older PostgreSQL version, it ensures tight integration with the Rocky Linux ecosystem and follows the distribution’s standard update cycle. This approach suits environments that prioritize system consistency over having the latest database features.

The default repository method typically requires less configuration and experiences fewer compatibility issues with other system components, making it suitable for straightforward installations where advanced PostgreSQL features aren’t critical.

Database Initialization and Configuration

After successful package installation, PostgreSQL requires database cluster initialization before it can accept connections and store data.

Database Cluster Initialization

Initialize the PostgreSQL database cluster using the setup utility provided with the installation:

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

For installations from the default repository, use:

sudo postgresql-setup --initdb

The initialization process creates the default database cluster in /var/lib/pgsql/17/data/ (or /var/lib/pgsql/data/ for default repository installations). This directory contains all database files, configuration files, and transaction logs essential for PostgreSQL operations.

During initialization, the system creates the default postgres database and user account. These provide the foundation for database administration and serve as templates for additional databases and users you’ll create later.

Monitor the initialization output for any error messages. Successful initialization displays confirmation messages indicating the database cluster creation and initial configuration completion.

Service Management

Enable the PostgreSQL service for automatic startup during system boot:

sudo systemctl enable postgresql-17

For default repository installations:

sudo systemctl enable postgresql

Start the PostgreSQL service to begin database operations:

sudo systemctl start postgresql-17

Verify the service status to ensure PostgreSQL is running properly:

sudo systemctl status postgresql-17

The status command should display “active (running)” along with process information and recent log entries. If the service fails to start, check the system logs using journalctl -u postgresql-17 to identify and resolve any issues.

Initial Security Configuration

PostgreSQL’s default configuration prioritizes security, but initial setup requires specific steps to establish proper authentication and access controls.

Default Security Settings

PostgreSQL installs with restrictive default settings that allow only local connections using peer authentication. This configuration means only the system user postgres can initially connect to the database using the same username.

The default authentication configuration prevents network connections and requires specific configuration changes to enable remote access. Understanding these security defaults helps you make informed decisions about which restrictions to maintain and which to modify based on your requirements.

Review the initial authentication configuration in /var/lib/pgsql/17/data/pg_hba.conf to understand the current access rules and plan any necessary modifications.

Setting PostgreSQL Admin Password

Switch to the PostgreSQL system user to perform administrative tasks:

sudo -i -u postgres

Connect to the PostgreSQL command-line interface:

psql

Set a secure password for the PostgreSQL administrative user:

ALTER USER postgres PASSWORD 'your_secure_password';

Choose a strong password that includes uppercase and lowercase letters, numbers, and special characters. Avoid common passwords or easily guessable combinations that could compromise database security.

Exit the PostgreSQL interface and return to your regular user account:

\q
exit

Document the administrative password securely, as you’ll need it for future database administration tasks.

Database and User Management

With PostgreSQL running and secured, create your first database and user accounts to establish the foundation for application development or production use.

Creating Your First Database

Switch to the PostgreSQL user account to execute database creation commands:

sudo -i -u postgres

Create a new database using the createdb utility:

createdb mydatabase

Alternatively, create databases through the PostgreSQL command interface:

psql -c "CREATE DATABASE mydatabase;"

Database names should follow PostgreSQL naming conventions, using lowercase letters, numbers, and underscores. Avoid spaces and special characters that can cause quoting issues in SQL statements and command-line operations.

Consider specifying encoding and locale settings during database creation if your application requires specific character set support:

createdb --encoding=UTF8 --locale=en_US.UTF-8 mydatabase

Creating Database Users

Create dedicated user accounts for application connections rather than using the administrative postgres user:

createuser --interactive myuser

The interactive mode prompts for user privileges, allowing you to specify whether the user can create databases, create other users, or has superuser privileges. For most applications, standard user privileges without superuser access provide appropriate security.

Alternatively, create users with specific privileges using SQL commands:

psql -c "CREATE USER myuser WITH PASSWORD 'user_password';"

Grant the new user appropriate permissions on your database:

psql -c "GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;"

Return to your regular user account:

exit

Configuration File Management

PostgreSQL’s behavior is controlled through several configuration files that require understanding for proper database administration and optimization.

PostgreSQL Configuration Files

The main PostgreSQL configuration file, postgresql.conf, contains server settings that control memory usage, connection limits, logging, and performance parameters. This file’s location varies based on your installation method but typically resides in the data directory.

The client authentication file, pg_hba.conf, defines which users can connect from which locations using which authentication methods. This file provides granular control over database access security.

Before modifying configuration files, create backup copies to enable quick recovery if configuration changes cause issues:

sudo cp /var/lib/pgsql/17/data/postgresql.conf /var/lib/pgsql/17/data/postgresql.conf.backup
sudo cp /var/lib/pgsql/17/data/pg_hba.conf /var/lib/pgsql/17/data/pg_hba.conf.backup

Essential Configuration Changes

Edit the PostgreSQL configuration file to adjust basic settings:

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

Locate and modify the listen_addresses setting to enable network connections if needed:

listen_addresses = '*'  # Enable all interfaces

Adjust connection limits based on your expected usage:

max_connections = 100  # Adjust based on your needs

Configure shared memory settings for better performance:

shared_buffers = 256MB  # Adjust based on available RAM

Enable query logging for monitoring and troubleshooting:

log_statement = 'all'  # Log all SQL statements
log_min_duration_statement = 1000  # Log slow queries

Firewall and Network Configuration

Network access to PostgreSQL requires proper firewall configuration and authentication settings to balance accessibility with security requirements.

Firewall Configuration

Open the PostgreSQL port in the Rocky Linux firewall:

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

Reload the firewall configuration to apply changes:

sudo firewall-cmd --reload

Verify the port is open:

sudo firewall-cmd --list-ports

For enhanced security, consider restricting access to specific IP addresses or networks rather than opening the port globally:

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

Network Access Configuration

Modify the client authentication file to allow network connections:

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

Add entries for network access while maintaining security. For example, to allow password-authenticated connections from your local network:

host    all             all             192.168.1.0/24          md5

This configuration allows all users to connect to all databases from the specified network using password authentication. Adjust the network address and authentication method based on your security requirements.

Restart PostgreSQL to apply configuration changes:

sudo systemctl restart postgresql-17

Testing and Verification

Thorough testing ensures your PostgreSQL installation functions correctly and can handle expected workloads.

Local Connection Testing

Verify PostgreSQL installation and version:

psql --version

Test local connection using the PostgreSQL user:

sudo -i -u postgres psql

Execute basic commands to verify functionality:

SELECT version();
\l  -- List databases
\du -- List users
\q  -- Quit

These commands confirm PostgreSQL is responding to queries and can access system information properly.

Functionality Verification

Create a test table to verify database operations:

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert sample data:

INSERT INTO test_table (name) VALUES ('Test Record 1'), ('Test Record 2');

Query the data to confirm proper storage and retrieval:

SELECT * FROM test_table;

Clean up test data:

DROP TABLE test_table;

Test network connectivity if you’ve enabled remote access by attempting connections from remote systems using appropriate client tools.

Troubleshooting Common Issues

Understanding common installation and configuration issues helps resolve problems quickly and maintain system reliability.

Installation Problems

Repository conflicts may prevent package installation. If you encounter dependency issues, clean the package cache and retry:

sudo dnf clean all
sudo dnf makecache

Permission errors during database initialization often result from incorrect directory ownership. Verify the PostgreSQL data directory belongs to the postgres user:

sudo chown -R postgres:postgres /var/lib/pgsql/17/

Service startup failures typically indicate configuration errors or resource constraints. Check PostgreSQL logs for specific error messages:

sudo journalctl -u postgresql-17 -f

Connection Issues

Authentication failures suggest incorrect password or user configuration. Verify user accounts exist and have appropriate permissions:

sudo -i -u postgres psql -c "\du"

Network connectivity problems often stem from firewall restrictions or authentication configuration. Test local connections first, then gradually expand to network access while monitoring logs for authentication attempts and failures.

Configuration file syntax errors prevent PostgreSQL startup. Always validate configuration files after modifications and maintain backups for quick recovery.

Best Practices and Security Hardening

Implementing security best practices from the initial installation protects your database from common vulnerabilities and attack vectors.

Security Recommendations

Establish a regular update schedule to apply security patches promptly. Subscribe to PostgreSQL security announcements to stay informed about potential vulnerabilities:

sudo dnf update postgresql* -y

Implement principle of least privilege by creating specific user accounts for each application with only required permissions. Avoid using the administrative postgres account for application connections.

Configure network security carefully, restricting database access to only necessary systems and networks. Use SSL/TLS encryption for network connections in production environments.

Establish backup and recovery procedures before placing databases into production use. Regular backups protect against data loss and enable quick recovery from various failure scenarios.

Performance Optimization

Optimize PostgreSQL configuration based on your system’s hardware and expected workload. Key parameters include memory allocation, connection limits, and checkpoint settings.

Implement monitoring solutions to track database performance and identify potential issues before they impact operations. Tools like pg_stat_statements provide valuable insights into query performance and resource usage.

Schedule regular maintenance tasks including VACUUM and ANALYZE operations to maintain optimal database performance as data volumes grow.

Consider implementing connection pooling solutions like PgBouncer for applications with high connection requirements or variable load patterns.

Congratulations! You have successfully installed PostgreSQL. Thanks for using this tutorial for installing PostgreSQL 17 on your Rocky Linux 10 system. For additional help or useful information, we recommend you check the official PostgreSQL website.