How to Install PostgreSQL on Rocky Linux

On Rocky Linux, you can install PostgreSQL using the postgresql-server package from the default repository. After installation, you need to initialize the database cluster and enable the service, and for remote access, you must configure listen_addresses, pg_hba.conf, the firewall, and TLS together.

The major version of PostgreSQL and the service name provided may vary depending on the Rocky Linux version and the active repositories. Check the available packages and the version required by your application before installation.

Checking which PostgreSQL versions are available

cat /etc/rocky-release
dnf info postgresql-server
dnf module list postgresql

dnf module may not be available depending on the Rocky Linux and DNF version. If a specific major version of PostgreSQL is required, first decide whether to use the Rocky default repository’s lifecycle or the PGDG repository provided by the PostgreSQL project, and do not mix server packages from different repositories.

Installing and initializing PostgreSQL

Installing server and client

sudo dnf install postgresql-server postgresql

Verify the installed version and packages.

psql --version
rpm -q postgresql-server postgresql

Initialize the database cluster

In the Rocky default package, the following tools are generally used.

sudo postgresql-setup --initdb

Initialization is performed only once on an empty data directory. If you reinitialize on a server with existing data, a separate cluster may be created or there is a risk of data corruption, so check the data directory and backups first.

sudo postgresql-setup --help

Service start and automatic execution

sudo systemctl enable --now postgresql
systemctl status postgresql

Version-specific packages in the PGDG repository may have different service names and initialization commands, such as postgresql-16. Check the official instructions for the installed package and the result of systemctl list-unit-files 'postgresql*'.

Local access and basic security settings

Connect as the postgres operating system account

sudo -iu postgres psql

Initial local authentication may use the peer method, which links the operating system account to the database role. The PostgreSQL postgres role password and the Linux postgres account password are different.

Set passwords securely

Using \password within psql allows you to avoid leaving the password in SQL statements and shell history.

\password postgres

Do not use administrative roles in applications; create purpose-specific roles and databases instead.

CREATE ROLE appuser LOGIN;
\password appuser
CREATE DATABASE appdb OWNER appuser;

Check the actual location of the configuration file.

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

Since the path may vary depending on the package and version, do not just assume the path from online documents; use the value reported by the server.

Allow remote connections securely.

Restrict receiving addresses.

It is recommended to limit listen_addresses in postgresql.conf to a specific private IP of the server.

listen_addresses = '192.0.2.20'

192.0.2.20 is an example for documentation. Replace it with the actual server address. * means all interfaces, which is convenient but may expose it to unnecessary networks. The default for PostgreSQL is local loopback access. For more details, see the official PostgreSQL connection settings documentation.

Restricting Clients and Authentication in pg_hba.conf

For example, if you want only a specific application server to connect to appdb as appuser, you can review the following records.

hostssl  appdb  appuser  192.0.2.10/32  scram-sha-256

pg_hba.conf is checked from top to bottom, and only the first matching rule is used. If you place broad allowance rules at the top, subsequent restriction rules will not apply. For detailed fields and order, refer to the official PostgreSQL pg_hba.conf documentation.

Restrict Source Addresses with Firewall

sudo firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.0.2.10/32" port port="5432" protocol="tcp" accept'

After testing new client connections, make the rules permanent.

sudo firewall-cmd --runtime-to-permanent

Do not expose database ports directly to the entire internet. Use private networks, VPNs, security groups, and firewalls together.

Applying and Verifying Settings

Reload authentication rules

Settings that can be reloaded, like pg_hba.conf, can be re-read as follows.

sudo -iu postgres psql -c 'SELECT pg_reload_conf();'

The rule syntax can be checked in the system view.

sudo -iu postgres psql -c 'SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;'

Applying settings that require a restart

Settings that are applied at server start, like listen_addresses, require a restart.

sudo systemctl restart postgresql
sudo systemctl status postgresql

Testing listening ports and connections

ss -lntp | grep 5432
psql 'host=192.0.2.20 dbname=appdb user=appuser sslmode=verify-full'

To use verify-full, the client must have a trusted CA and a certificate matching the server name. If you only set up password authentication and skip TLS, it may not be sufficient against network threats.

Items to prepare before operation

• Regular logical and physical backups and actual recovery tests
• Plan for Applying Supported Minor Updates
• Minimum privilege roles and password rotation per application
• TLS certificate verification and use of private network or VPN
• Monitoring disk usage, connection count, latency, checkpoints, and error logs
• Compatibility review and preparation of separate procedures before major version upgrade

PostgreSQL major version upgrades are different from simple package updates. Plan the official upgrade path according to the version, such as pg_upgrade, logical replication, or dump and restore.

Frequently Asked Questions

Cannot find postgresql-setup command

Check the installed repository and package names. The initialization tools and service names may differ between Rocky default packages and PGDG version-specific packages.

External connections keep being refused

Check listen_addresses, the first matching rule in pg_hba.conf, the service listening port, firewalld, and cloud security groups in order.

Is it okay to allow 0.0.0.0/0 in pg_hba.conf?

It matches the entire Internet, so it is generally best to avoid it. Restrict to the fixed address of the actual application server or private subnet, and use TLS and minimal privileges.

After changing the configuration files, should I use reload or restart?

It depends on the item. pg_hba.conf can be reloaded, but listen_addresses requires a restart. Check pg_settings.pending_restart and the context in the official documentation.

Summary

On Rocky Linux, PostgreSQL is prepared in the order of package installation, cluster initialization, and service activation. For remote connections, apply specific listening addresses, narrow pg_hba.conf rules, firewall source restrictions, and TLS together.

Since configuration paths and service names can vary depending on the installation source and version, it is safe to work based on the paths reported directly by PostgreSQL and the documentation of the installed package.