How to Install and Configure PostgreSQL 17 on Rocky Linux 9 and AlmaLinux 9

PostgreSQL 17 is a powerful open-source relational database used for web apps, analytics platforms, and backend services. In this guide, you will install and configure PostgreSQL 17 on Rocky Linux 9 or AlmaLinux 9, then secure it for production use.

By the end, you will know how to:

Install PostgreSQL 17 from the official PostgreSQL RPM repository
Start and enable the database service with systemd
Create users and databases
Enable secure remote access
Open firewall ports safely
Run basic backup and restore commands
Troubleshoot common startup and connection issues

Prerequisites

Before you begin, make sure you have:

A server running Rocky Linux 9 or AlmaLinux 9
A user with sudo privileges
Internet access to download PostgreSQL packages
Basic Linux terminal familiarity

1. Update the Server

Update installed packages first:

sudo dnf update -y

2. Install PostgreSQL 17

Add the PostgreSQL repository

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

Disable the built-in PostgreSQL module

Rocky/Alma include a default PostgreSQL module. Disable it to avoid package conflicts:

sudo dnf -qy module disable postgresql

Install PostgreSQL server and contrib packages

sudo dnf install -y postgresql17-server

Install postgresql17-contrib for useful extensions and utilities:

sudo dnf install -y postgresql17-contrib

3. Initialize and Start PostgreSQL Service

Initialize the database cluster:

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

Initializing database ... OK

Start PostgreSQL:

sudo systemctl start postgresql-17

Enable it at boot:

sudo systemctl enable postgresql-17

Verify service status:

sudo systemctl status postgresql-17

● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Mon 2025-01-27 08:15:19 UTC; 40s ago
       Docs: https://www.postgresql.org/docs/17/static/
   Main PID: 57858 (postgres)
      Tasks: 7 (limit: 22434)
     Memory: 17.6M
        CPU: 59ms
     CGroup: /system.slice/postgresql-17.service
             ├─57858 /usr/pgsql-17/bin/postgres -D /var/lib/pgsql/17/data/
             ├─57859 "postgres: logger "
             ├─57860 "postgres: checkpointer "
             ├─57861 "postgres: background writer "
             ├─57863 "postgres: walwriter "
             ├─57864 "postgres: autovacuum launcher "
             └─57865 "postgres: logical replication launcher "

Jan 27 08:15:19 lab.citizix.com systemd[1]: Starting PostgreSQL 17 database server...
Jan 27 08:15:19 lab.citizix.com postgres[57858]: 2025-01-27 08:15:19.539 UTC [57858] LOG:  redirecting log output to logging collector process
Jan 27 08:15:19 lab.citizix.com postgres[57858]: 2025-01-27 08:15:19.539 UTC [57858] HINT:  Future log output will appear in directory "log".
Jan 27 08:15:19 lab.citizix.com systemd[1]: Started PostgreSQL 17 database server.

If you see Active: active (running), PostgreSQL is running correctly.

Check the installed version:

sudo -u postgres psql -c "SELECT version();"

Output:

$ sudo -u postgres psql -c "SELECT version();"
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-2), 64-bit
(1 row)

4. Basic PostgreSQL Setup (User and Database)

Switch to the postgres system user:

sudo -iu postgres

Open the PostgreSQL prompt:

psql

Create an application user and database (replace placeholders):

CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPasswordHere';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

Exit:

\q

Then exit the shell:

exit

5. Configure Local and Remote Access

PostgreSQL connection behavior is controlled by:

postgresql.conf (server settings)
pg_hba.conf (client authentication rules)

On Rocky/Alma with PGDG packages, these files are usually under:

/var/lib/pgsql/17/data/postgresql.conf
/var/lib/pgsql/17/data/pg_hba.conf

Allow PostgreSQL to listen on server IP

Edit postgresql.conf:

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

Update or uncomment:

listen_addresses = '*'

For stricter security, use a specific IP instead of *.

Add client authentication rule

Edit pg_hba.conf:

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

Add a rule for your trusted network:

host    all             all             10.10.0.0/24            scram-sha-256

Use your real subnet. Avoid broad ranges like 0.0.0.0/0 in production.

Restart PostgreSQL after changes:

sudo systemctl restart postgresql-17

6. Open Firewall Port 5432

If firewalld is enabled, allow PostgreSQL traffic:

sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

Confirm:

sudo firewall-cmd --list-services

7. Secure PostgreSQL for Production

Apply these hardening steps:

Use strong passwords for all login roles
Restrict pg_hba.conf to known IP ranges only
Use scram-sha-256 authentication (recommended)
Keep OS and PostgreSQL packages updated
Enable TLS for encrypted client/server traffic when needed
Rotate credentials periodically

Optional: in postgresql.conf, confirm password encryption:

password_encryption = scram-sha-256

Reload config:

sudo systemctl reload postgresql-17

8. Backup and Restore Essentials

Backup one database

sudo -u postgres pg_dump app_db > app_db.sql

Backup all databases

sudo -u postgres pg_dumpall > all_databases.sql

Restore a database dump

sudo -u postgres psql app_db < app_db.sql

9. Useful Verification Commands

Check listening port:

sudo ss -lntp | rg 5432

Check PostgreSQL logs (via systemd journal):

sudo journalctl -u postgresql-17 -n 100 --no-pager

List databases:

sudo -u postgres psql -c "\l"

List roles:

sudo -u postgres psql -c "\du"

10. Common Troubleshooting

Service fails to start

Check logs: sudo journalctl -u postgresql-17 -xe --no-pager
Verify data directory permissions
Confirm configuration syntax in postgresql.conf and pg_hba.conf

Cannot connect remotely

Ensure listen_addresses is set correctly
Ensure pg_hba.conf has a matching host rule
Confirm firewall allows port 5432
Confirm cloud/network security rules allow inbound traffic

Authentication failed

Verify username/password
Confirm matching pg_hba.conf rule and auth method (scram-sha-256)
Reset password if needed:

sudo -u postgres psql -c "ALTER ROLE app_user WITH PASSWORD 'NewStrongPassword';"

Final Thoughts

You now have a complete PostgreSQL 17 setup on Rocky Linux 9 or AlmaLinux 9, including installation, service management, remote access, hardening, and backup basics.

If this server is for production, the next best step is to configure TLS, monitoring, and automated backups.