RHEL in Practice #2: Database Operations — PostgreSQL on RHEL

Infrastructure Linux RHEL Red Hat
9 min read

In RHEL in Practice #1 we stood up the web tier with nginx; this time we move to the data tier behind it. On RHEL, bringing up a database is not the simple one-package affair you might expect — just as with a web server. PostgreSQL, however, adds one RHEL-specific step: choosing a version via an AppStream module.

RHEL ships multiple versions of the same package bundled into units called modules. For software whose major version moves fast — PostgreSQL being a prime example — you have to go through this module structure to install the version you want cleanly. This post starts from that installation and follows one full operational cycle: initialization, remote access, SELinux, and backup.

Choosing a Version via AppStream Module #

First, check which PostgreSQL versions are offered as modules. This shows the available streams (version lines) and the default at a glance.

# list the offered postgresql modules and streams
dnf module list postgresql

In the output, a stream tagged [d] is the default, and one tagged [e] is already enabled. If the version you want differs from the default, enable it explicitly and then install. Here we’ll take version 16 as the example.

# enable the 16 stream
sudo dnf module enable -y postgresql:16

# install the server packages
sudo dnf install -y postgresql-server postgresql-contrib

postgresql-server is the database engine itself; installing only postgresql brings in just the client (psql and friends). To stand up a server you must install postgresql-server. postgresql-contrib carries extensions you’ll often reach for in practice, such as pg_stat_statements, so it’s good to install it alongside.

Once you enable a stream, subsequent updates happen only within that version line. Moving to a new major version requires resetting the module or enabling a different stream, so on a running database this needs to be handled carefully.

Initialization and Service Registration #

Right after installation the data directory is empty, so the service won’t start as-is. The RHEL package provides a dedicated initialization command.

# initialize the data directory
sudo postgresql-setup --initdb

This command creates the configuration files and system catalogs in the default data directory, /var/lib/pgsql/data. Once initialization finishes, register it with systemd and start it immediately.

# auto-start on boot + start now
sudo systemctl enable --now postgresql

# check status
systemctl status postgresql

Handling both in one shot with enable --now is the same flow as with nginx. If it came up cleanly, let’s confirm a connection locally first. Installation creates an OS user named postgres, and switching to that user to connect is the simplest path.

# switch to the postgres user, then connect with psql
sudo -u postgres psql

If the psql prompt appears, the database engine itself is healthy. Use \l to list databases and \q to quit.

The Data Directory #

The default data directory is /var/lib/pgsql/data. The key files and locations are as follows.

  • postgresql.conf: server-wide settings (listen address, memory, logging, etc.)
  • pg_hba.conf: client authentication rules (who connects from where and by what method)
  • base/, global/: the actual data files
  • pg_wal/: the transaction log (WAL)

In production it’s common to move the data directory to a separate disk, and if you don’t bring the SELinux context along when you do, the service won’t start. We cover that in the SELinux section below.

Configuring Remote Access #

A fresh PostgreSQL install accepts connections only from localhost. To connect from outside you have to edit two files: open the listen address in postgresql.conf, and add an authentication rule in pg_hba.conf — two steps.

First, edit listen_addresses in postgresql.conf. The default is localhost, which blocks external connections.

# /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'        # listen on all interfaces
port = 5432

'*' means “accept on every network interface.” If you want only a specific IP, write that address directly. Next, add an authentication rule to pg_hba.conf. This file is read top to bottom, and the first matching rule wins.

# /var/lib/pgsql/data/pg_hba.conf
# TYPE  DATABASE  USER  ADDRESS          METHOD
host    all       all   192.168.1.0/24   scram-sha-256

host means a TCP/IP connection, and scram-sha-256 is RHEL’s default password-based authentication method. It’s safer than the older md5, so for newly built environments scram-sha-256 is the right choice. In ADDRESS you write the network range you want to allow. After editing both files, you have to make the service re-read them for the changes to apply.

# reload the configuration (a reload is enough for most settings)
sudo systemctl reload postgresql

Some items, like listen_addresses, don’t take effect on a reload and need a restart. If you’re not sure whether your change applies via reload, just restart with sudo systemctl restart postgresql.

Opening 5432 with firewalld #

PostgreSQL’s default port is 5432. RHEL has firewalld enabled, so this port is blocked — open it permanently.

# permanently allow the postgresql service (5432/tcp)
sudo firewall-cmd --add-service=postgresql --permanent

# apply
sudo firewall-cmd --reload

# verify
sudo firewall-cmd --list-all

firewalld has a predefined service named postgresql, so you don’t have to write the port number yourself. If you use a non-standard port, open it by port directly with something like --add-port=5433/tcp. The two-step flow of creating a permanent rule with --permanent and then applying it with --reload is the basic pattern for RHEL firewall work.

Where SELinux Blocks You #

As long as it runs at the standard location (/var/lib/pgsql/data) on the standard port (5432), SELinux stays quiet. But exactly as with the web server, the moment you stray to a non-standard directory or a non-standard port, it blocks you immediately.

Non-standard data directory #

If you move the data directory to a path like /data/pgsql, that directory’s SELinux context isn’t the PostgreSQL data type, so the service won’t start. Assign the postgresql_db_t type permanently and apply it.

# permanently register /data/pgsql and below as the PostgreSQL data type
sudo semanage fcontext -a -t postgresql_db_t "/data/pgsql(/.*)?"

# apply to the actual labels on disk
sudo restorecon -Rv /data/pgsql

The two key steps are adding the rule to the policy with semanage fcontext and applying it to the real files with restorecon. Separately, since the systemd unit points at the default directory, when you move the data directory you also need to update the unit’s Environment=PGDATA= value accordingly.

Non-standard port #

If you run PostgreSQL on a different port like 5433, then even with the configuration correct, SELinux won’t recognize that port as a database port and blocks it.

# check the ports currently registered under postgresql_port_t
sudo semanage port -l | grep postgresql

# permanently register 5433 as a PostgreSQL port
sudo semanage port -a -t postgresql_port_t -p tcp 5433

If semanage is missing, install the policycoreutils-python-utils package. After registering the port, restart the service and it comes up cleanly.

Creating Users and Databases #

To actually use the database, you need to create an application user (role) and a database. Do this as the postgres superuser. There are two ways: with a shell command, or with SQL inside psql.

First, the way that creates them straight from the shell.

# create a role interactively (with a password prompt)
sudo -u postgres createuser --pwprompt appuser

# create a database owned by appuser
sudo -u postgres createdb --owner=appuser appdb

You can do the same work with SQL inside psql. This is the more convenient side when you want to grant privileges in fine detail.

-- after connecting with sudo -u postgres psql
CREATE ROLE appuser WITH LOGIN PASSWORD 'change_this_password';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

The LOGIN attribute is what makes the role a user that can actually connect. When you connect remotely as this appuser, the rule you added earlier to pg_hba.conf and the scram-sha-256 authentication have to line up together for the connection to succeed.

Backup and Recovery #

On a production database, backup isn’t optional — it’s a baseline. PostgreSQL provides pg_dump and pg_dumpall as logical backup tools. pg_dump captures a single database; pg_dumpall captures the entire cluster, roles included.

# back up a single database (custom format, good for compression and parallel restore)
sudo -u postgres pg_dump -Fc appdb -f /var/backups/appdb.dump

# back up the whole cluster (roles and privileges included, plain SQL)
sudo -u postgres pg_dumpall -f /var/backups/all.sql

-Fc is the custom format; it allows per-table selection and parallel processing at restore time, so it’s recommended for single-database backups. Recovery uses different tools depending on the format: the custom format restores with pg_restore, plain SQL with psql.

# restore the custom format (create the target DB in advance)
sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore -d appdb_restore /var/backups/appdb.dump

# restore plain SQL
sudo -u postgres psql -f /var/backups/all.sql

These backups are snapshots of a specific point in time, so in a failure, changes after the last backup are lost. To minimize loss you’d have to set up point-in-time recovery (PITR) based on WAL archiving separately, but that topic is deep enough to warrant its own post, so here we’ll stop at logical backups. Backup files only mean something if you run them on a schedule via cron or a systemd timer and move them to separate storage rather than the same host.

Diagnosis When You Get Stuck #

When you can’t connect to the database, narrowing it down in the following order usually surfaces the cause.

  1. Is the service up? systemctl status postgresql, and on failure check the startup log with journalctl -u postgresql.
  2. Does it open locally? Connect with sudo -u postgres psql to separate a server-side problem from a remote-access problem.
  3. Is the firewall open? Does firewall-cmd --list-all show postgresql or 5432?
  4. Is authentication blocking? Try connecting from outside with psql -h host -U appuser -d appdb, and if it’s rejected, check the rules and their order in pg_hba.conf.
  5. Is SELinux blocking? Does sudo ausearch -m AVC -ts recent show postgresql-related denials?

In particular, pg_hba.conf is read top to bottom and the first matching rule applies, so if a broader rule sits above, the intended rule below it can be masked. Authentication error messages also record which rule blocked the connection in the PostgreSQL log (/var/lib/pgsql/data/log/), so on a refused connection, looking at this log first is the fastest route.

Operational Takeaways #

What this post pinned down:

  • Versions via module. Check with dnf module list postgresql, run dnf module enable postgresql:16, then install postgresql-server.
  • Initialization is a separate step. postgresql-setup --initdb, then systemctl enable --now postgresql; data lives in /var/lib/pgsql/data.
  • Remote access is two files. listen_addresses in postgresql.conf, the scram-sha-256 authentication rule in pg_hba.conf, plus the firewalld opening for 5432.
  • The two spots SELinux blocks. Non-standard data directory (semanage fcontext -t postgresql_db_t), non-standard port (semanage port -t postgresql_port_t).
  • Backup. A single DB with pg_dump -Fc + pg_restore, the whole cluster with pg_dumpall + psql, kept on separate storage.
  • Diagnosis order. Service → local psql → firewall → authentication (pg_hba) → SELinux.

Next: Container Workloads #

Having put the web tier and the data tier directly on RHEL, we now move to packaging the same workloads as containers.

In #3 Container Workloads: Podman, systemd (quadlet) we’ll run containers with Podman, RHEL’s default container engine, and go all the way to the quadlet approach for registering them as systemd services — pulling containers into RHEL’s operational framework in one cycle.

X