In this article I will show you, how you can install a PostgreSQL Database-Server with the App Stream Repository on a RedHat System.

What are Module Streams / App Streams

Red Hat Systems recieve updates for about 10 years. In the first 5 years these are feature and security updates, but in the last 5 years there are only security updates and backports. That is not a problem for the OS itself but for applications packaged in the base-repository. To overcome this, Red Hat introduced Module Streams or App Streams. These are a special part of the repository and will deliver feature updates for this specific application in a more fast paced manner than the OS itself.

Activate the PostgreSQL Module

First you should check if the PostgreSQL module is available on your system and in which version. To do this execute the module list command.

[tux@server]$ sudo dnf module list
AlmaLinux 9 - AppStream
Name                                Stream                          Profiles                                                       Summary
maven                               3.8                             common [d]                                                     Java project management and project comprehension tool
nginx                               1.22                            common [d]                                                     nginx webserver
nodejs                              18                              common [d], development, minimal, s2i                          Javascript runtime
php                                 8.1                             common [d], devel, minimal                                     PHP scripting language
postgresql                          15                              client, server                                                 PostgreSQL server and client module
ruby                                3.1                             common [d]                                                     An interpreter of object-oriented scripting language

You can see there is a postgresql module and the streamversion is 15. Now we want to enable the module to use it.

[tux@server]$ sudo dnf module enable postgresql:15

Install the PostgreSQL Server

Now that our module is activated we can install postgresql-server in version 15. This is done with dnf just like you would install every other package.

[tux@server]$ sudo dnf install postgresql-server

Setup of the PostgreSQL-Server

Now the database server is installed, but we still have to do some setup to use it.

Initialize and start the Database-server

To create the needed directories and config-files you have to initialize the database-server. You can use the postgresql-setup command for that.

[tux@server]$ sudo postgresql-setup --initdb

After that we can start the systemdservice. Since I want my service to start on system-boot, I will enable it at the same time.

# enable -> set the service to start on system-boot
# --now -> start the service if not running; equivalent to systemctl start postgresql
[tux@server]$ sudo systemctl enable --now postgresql

Open Firewall-Ports

I want to connect to my database from a separat backend-server. So I need to open the firewall-port for Postgres. The port is 5432.

# open the port
[tux@server]$ sudo firewall-cmd --add-port="5432/tcp"
success

# make active configuration persistent across reboots
[tux@server]$ sudo firewall-cmd --runtime-to-permanent

Allow remote connections

By default Postgres only allows connections from localhost. That is great for security, but not so great if you want connect from a separat backend-server. To change this behavior we need to edit the config file under /var/lib/pgsql/data/pg_hba.conf. I will allow connections from everywhere, since my server is only accessable via LAN. If you want more security you can use smaller subnets instead of 0.0.0.0/0. If you want to only allow a single ip, use /32 as the subnetmask.

# search for the line
host    all    all    127.0.0.1/32    md5

# change it to the following. Replace the ip-range with your desired one
# I will use scram-sha-256 encryption, since it is more secure than md5
host    all    all    0.0.0.0/0    scram-sha-256

Now postgres would accept connections from everywhere, but at the moment only requests for 127.0.0.1 are processed by postgres. To change that we need to edit the /var/lib/pgsql/data/postgresql.conf

# search for this line and uncomment it
listen_address='*'

For these changes to take effect, we need to restart the postgres-service one last time.

[tux@server]$ sudo systemctl restart postgresql

Connect to Database

Initially you can only connect with the postgres user from the Linux-Terminal.

[tux@server]$ su - postgres

If you get an Authentication failure, your postgres user does not have a password yet.

[tux@server]$ sudo passwd postgres

After that you can use the psql commandline tool, to connect to your PostgreSQL.

[postgres@server]$ psql
psql (15.3)
Type "help" for help.

postgres=#

Now you can use SQL Command to create and manage users.

postgres=# ALTER USER postgres PASSWORD 'mypassword';

postgres=# CREATE USER tux LOGIN PASSWORD 'mypassword';

Tuning

The default settings for PostgreSQL are quite conservative. If you want to improve your performance, consider tuning them for your use-case. If have created a short overview of the most important parameters here.