How to Install PostgreSQL on AlmaLinux/Rocky Linux

AlmaLinux and Rocky Linux ship PostgreSQL in their own repositories, but usually not the newest major version. This guide installs a current release from the official PostgreSQL (PGDG) repository on AlmaLinux or Rocky Linux 9 and 10, sets it up securely, and avoids the permissions trap that catches most people on PostgreSQL 15 and later.

Distribution Package or PGDG?

• Distribution package (dnf install postgresql-server): simplest, updated with the OS, but one major version for the life of the release.
• PGDG repository (this guide): any supported major version, installed side by side if you need two, with updates straight from the PostgreSQL project.

The commands below use a PGVER variable so you can choose the version. Check the current major releases at postgresql.org before you start.

Step 1 — Add the PGDG Repository

PGVER=17
EL=$(rpm -E %rhel)        # 9 or 10
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-${EL}-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Step 2 — On EL9 Only, Disable the Built-in Module

[ "$EL" = 9 ] && sudo dnf -qy module disable postgresql

AlmaLinux and Rocky 9 ship PostgreSQL as a dnf module, which would otherwise shadow the PGDG packages. Version 10 no longer uses modules, so there is nothing to disable.

Step 3 — Install and Initialise

sudo dnf install -y postgresql${PGVER}-server postgresql${PGVER}
sudo /usr/pgsql-${PGVER}/bin/postgresql-${PGVER}-setup initdb
sudo systemctl enable --now postgresql-${PGVER}

Unlike Ubuntu and Debian, RHEL-family systems do not create the database cluster automatically — the service will not start until initdb has run.

Step 4 — Create a Database and an Application User

sudo -u postgres psql

CREATE USER appuser WITH PASSWORD 'CHANGE_ME_STRONG_PASSWORD';
CREATE DATABASE myapp OWNER appuser;
\q

Make the application user the owner of its database. Since PostgreSQL 15, ordinary users can no longer create tables in the public schema of a database they do not own, so the familiar GRANT ALL PRIVILEGES ON DATABASE alone leads to “permission denied for schema public” the first time the application runs its migrations. Owning the database avoids that. For an existing database, run ALTER DATABASE myapp OWNER TO appuser; and, connected to it, ALTER SCHEMA public OWNER TO appuser;.

Leave the postgres superuser without a password and use it only through sudo -u postgres on the server.

Step 5 — Use Password Authentication for Applications

By default local connections use peer and TCP connections to 127.0.0.1 use ident, both of which require the database user to match an operating-system user. Applications need password authentication instead:

sudo nano /var/lib/pgsql/${PGVER}/data/pg_hba.conf

# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                  peer
host    all       all   127.0.0.1/32   scram-sha-256
host    all       all   ::1/128        scram-sha-256

sudo systemctl reload postgresql-${PGVER}
psql -h 127.0.0.1 -U appuser -d myapp -c 'select version();'

Step 6 — Remote Access, Only If You Need It

Keep PostgreSQL on localhost whenever the application runs on the same server. If another server must connect, allow that one address — never the whole internet:

# postgresql.conf
listen_addresses = 'localhost,203.0.113.10'

# pg_hba.conf
host  myapp  appuser  198.51.100.20/32  scram-sha-256

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="198.51.100.20/32" port port="5432" protocol="tcp" accept'
sudo firewall-cmd --reload
sudo systemctl restart postgresql-${PGVER}

An SSH tunnel is often simpler and safer than opening 5432 at all: ssh -L 5432:127.0.0.1:5432 user@your-vps.

Step 7 — Basic Tuning

The defaults are sized for a very small machine. As a starting point, set shared_buffers to about a quarter of RAM and effective_cache_size to about half, then restart. On a database server, storage speed usually matters more than core count — see How to Choose the Right VPS Specs for a Database Server and the NVMe VPS range.

Step 8 — Backups

sudo -u postgres pg_dump -Fc myapp > /root/myapp-$(date +%F).dump

Schedule it and copy the dumps off the server — we do not hold backups of customer servers. Restore one now and then to prove it works. More in How to Back Up and Restore PostgreSQL Databases.

SELinux

The PGDG packages work with SELinux enforcing. If you move the data directory, restore its context with semanage fcontext and restorecon rather than turning SELinux off. Extensions that make outbound connections need sudo setsebool -P postgresql_can_network_connect 1.

Common Problems

“Peer authentication failed for user” — you are connecting over the local socket as an OS user that does not match. Connect with -h 127.0.0.1 after Step 5, or use sudo -u postgres.

“permission denied for schema public” — the application user does not own the database; see Step 4.

The service will not start — initdb was not run, or the version number in the service name does not match the package you installed: journalctl -u postgresql-${PGVER} -n 50.

Two PostgreSQL versions conflict — on EL9 the dnf module was not disabled before installing; disable it, remove the module packages, and install from PGDG again.

Continue Reading

• How to Configure firewalld on AlmaLinux/Rocky Linux
• Database Security Checklist
• How to Use dnf on AlmaLinux and Rocky Linux
• AlmaLinux VPS · Rocky Linux VPS

Browse more articles in AlmaLinux & Rocky Linux.