How to Install Postgres 14 on Rocky/Alma Linux 8

In this guide we are going to install Postgresql 14 in Centos 8/Rocky Linux 8/Alma Linux. This will also work in RHEL 8 and its derivatives.

Postgresql is an open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications. PostgreSQL can store structured and unstructured data in a single product.

Check these also:

Prerequisites

To follow along, ensure you have the following:

Rocky Linux/ Alma Linux/Centos 8 or RHEL 8 based server
Root access to the server or user with root access
Internet access from the server
Basic knowledge of Linux terminal

Ensure the server is up to date
Installing and starting Postgres Server
PostgreSQL Roles and Databases Authentication Methods
Connecting to postgres database
Configuring postgres 14 instance for remote access
User management

1. Ensure the server is up to date

Before proceeding, let us ensure that our server has up to date packages. Use this command:

$ sudo dnf -y update
Last metadata expiration check: 2:52:07 ago on Wed 06 Oct 2021 01:26:21 AM UTC.
Dependencies resolved.
Nothing to do.
Complete!

2. Installing and starting Postgres Server

By default the postgres module will have an older version of postgres enabled. But the current module does not include postgresql 14. Confirm with this command:

$ sudo dnf module list postgresql
Last metadata expiration check: 2:52:36 ago on Wed 06 Oct 2021 01:26:21 AM UTC.
CentOS Linux 8 - AppStream
Name                                        Stream                                  Profiles                                            Summary
postgresql                                  9.6                                     client, server [d]                                  PostgreSQL server and client module
postgresql                                  10 [d]                                  client, server [d]                                  PostgreSQL server and client module
postgresql                                  12                                      client, server [d]                                  PostgreSQL server and client module
postgresql                                  13                                      client, server [d]                                  PostgreSQL server and client module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Installing from the modules will install an older version of postgres (10) but that is not what we want.

We will use the repo provided by the postgres team to set up repositories for postgres 14 then install the package.

Let us Install the repository RPM using this command:

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

Then to avoid conflicts, let us disable the built-in PostgreSQL module:

sudo dnf -qy module disable postgresql

Finally install PostgreSQL 14 server:

sudo dnf install -y postgresql14-server

Let’s also install the Contrib package which provides several additional features for the PostgreSQL database system:

sudo dnf install -y postgresql14-contrib

Once the installation is complete, initialize the PostgreSQL database with the following command:

$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

Start the postgres service with this command:

sudo systemctl start postgresql-14

Then enable the service so it starts when the server reboots.

$ sudo systemctl enable postgresql-14
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.

Confirm that Postgres is running:

$ sudo systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-10-06 04:26:52 UTC; 33s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 1018631 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1018637 (postmaster)
    Tasks: 8 (limit: 23800)
   Memory: 17.2M
   CGroup: /system.slice/postgresql-14.service
           ├─1018637 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─1018638 postgres: logger
           ├─1018640 postgres: checkpointer
           ├─1018641 postgres: background writer
           ├─1018642 postgres: walwriter
           ├─1018643 postgres: autovacuum launcher
           ├─1018644 postgres: stats collector
           └─1018645 postgres: logical replication launcher

Oct 06 04:26:52 test-db-server systemd[1]: Starting PostgreSQL 14 database server...
Oct 06 04:26:52 test-db-server postmaster[1018637]: 2021-10-06 04:26:52.204 UTC [1018637] LOG:  redirecting log output to logging collector process
Oct 06 04:26:52 test-db-server postmaster[1018637]: 2021-10-06 04:26:52.204 UTC [1018637] HINT:  Future log output will appear in directory "log".
Oct 06 04:26:52 test-db-server systemd[1]: Started PostgreSQL 14 database server.

The Active: active (running) shows that the service is up and running.

Next, let us verify that the installation was successful by connecting to the PostgreSQL database server and printing its version:

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

Output:

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

3. PostgreSQL Roles and Databases Authentication Methods

PostgreSQL uses a concept called roles to handle client authentication and authorization. By default, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default postgres role. In order to use PostgreSQL, you can log in to that account.

PostgreSQL supports multiple authentication methods . The most commonly-used methods are:

Trust – A role can connect without a password, as long as the conditions defined in the pg_hba.conf are met.
Password – A role can connect by providing a password. The passwords can be stored as scram-sha-256, md5, and password (clear-text).
Ident – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
Peer – Same as Ident, but it is supported on local connections only.

4. Connecting to postgres database

By Switching to postres user

Switch over to the postgres account on your server by typing this in the terminal;:

sudo -i -u postgres

You can now access a Postgres prompt immediately by typing:

$ psql
psql (14.0)
Type "help" for help.

postgres=#

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

By running the command as postgres user

Use this to run the command directly as the postgres user using sudo

sudo -u postgres psql

Output:

$ sudo -u postgres psql
psql (14.0)
Type "help" for help.<br>postgres=#

5. Configuring postgres 14 instance for remote access

To achieve this, we will modify postgres configuration files. We need to open the files and adjust the configs are required. The main configuration file for Postgresql 14 can be found in this path /var/lib/pgsql/14/data/pg_hba.conf

Let’s change peer identification to trust:

sed -i '/^local/s/peer/trust/' /var/lib/pgsql/14/data/pg_hba.conf

Change ident identification to md5 to allow password login.

sed -i '/^host/s/ident/md5/' /var/lib/pgsql/14/data/pg_hba.conf

Add a block to allow access from everywhere:

Add this content to the file /var/lib/pgsql/14/data/pg_hba.conf

host    all             all             0.0.0.0/0                md5

Ensure PostgreSQL is listening on *

Add this line to the config here /var/lib/pgsql/14/data/postgresql.conf

listen_addresses='*'

Enable and restart postgresql server to reload the configs

sudo systemctl restart postgresql
sudo systemctl enable postgresql

6. User management

Creating Superuser

Now that everything is set up, let us create a super user.
Connect to the DB as postres role:

$ sudo -u postgres psql
psql (14.0)
Type "help" for help.

postgres=#

Create super user with name root:

CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'passwordhere';

Output:

postgres=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'passwordhere';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB                          | {}

postgres=#

Managing Application Users

Use this to create a database, create a user and grant that user all accesss to that database:

create database app_db_name;
create user app_user with encrypted password 'dbpassword';
grant all privileges on database app_db_name to app_user;

Checkout this comprehensive guide on user and permission management in postgres here.

Connecting to the instance from remote host

Use this command to connect to the postgres instance from local machine:

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

# like
psql 'postgres://root:passwordhere@192.160.1.20:5432/postgres?sslmode=disable'

Conclusion

Up to this point we have managed to install Postgresql 14 on a Rocky Linux server 8, do some basic configurations then do basic user management.

Prerequisites

Table of contents

1. Ensure the server is up to date

2. Installing and starting Postgres Server

3. PostgreSQL Roles and Databases Authentication Methods

4. Connecting to postgres database

5. Configuring postgres 14 instance for remote access

6. User management

Creating Superuser

Managing Application Users

Connecting to the instance from remote host

Conclusion