How to Install and Configure Postgres 13 on Centos 8

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

PostgreSQL, is a powerful, 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:

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

# Table of contents

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!

# Installing and starting Postgres Server

List out the available streams for the postgresql module using the dnf command:

dnf module list postgresql

Output:

# dnf module list postgresql

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

the module marked with [d] is the default thus installing postgres without specifying Stream will install that. In ourcase the following command installs postgres 10

sudo dnf install @postgresql

This is the installed version

$ sudo dnf list installed | grep postgres
postgresql.x86_64                   10.17-1.module_el8.4.0+823+f0dbe136     @appstream
postgresql-server.x86_64            10.17-1.module_el8.4.0+823+f0dbe136     @appstream

Since that is not the package I want, I am going to uninstall it with this

sudo dnf erase -y @postgresql

Now let us install postgresql 13. We will first reset the postgresql mdule to ensure there is none installed.

sudo dnf module reset postgresql

sudo dnf install @postgresql:13

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

sudo dnf install postgresql-contrib

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

sudo postgresql-setup postgresql-setup --initdb --unit postgresql

Output:

 * Initializing database in &#039;/var/lib/pgsql/data&#039;
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Start the PostgreSQL service and enable it to start on boot:

sudo systemctl enable --now postgresql

Check status of the db

# systemctl status postgresql
&#x25CF; postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-08-05 08:15:07 UTC; 7s ago
  Process: 68683 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
 Main PID: 68686 (postmaster)
    Tasks: 8 (limit: 23506)
   Memory: 17.3M
   CGroup: /system.slice/postgresql.service
           &#x251C;&#x2500;68686 /usr/bin/postmaster -D /var/lib/pgsql/data
           &#x251C;&#x2500;68687 postgres: logger
           &#x251C;&#x2500;68689 postgres: checkpointer
           &#x251C;&#x2500;68690 postgres: background writer
           &#x251C;&#x2500;68691 postgres: walwriter
           &#x251C;&#x2500;68692 postgres: autovacuum launcher
           &#x251C;&#x2500;68693 postgres: stats collector
           &#x2514;&#x2500;68694 postgres: logical replication launcher

Aug 05 08:15:07 prod-db systemd[1]: Starting PostgreSQL database server...
Aug 05 08:15:07 prod-db postmaster[68686]: 2021-08-05 08:15:07.650 UTC [68686] LOG:  redirecting log output to logging collector process
Aug 05 08:15:07 prod-db postmaster[68686]: 2021-08-05 08:15:07.650 UTC [68686] HINT:  Future log output will appear in directory "log".
Aug 05 08:15:07 prod-db systemd[1]: Started PostgreSQL database server.

The Active: active (running) shows that our postgres server is up and runnig as expected.

Verify the installation by connecting to the PostgreSQL database server and print its version :

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

Output:

                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 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

There are a number of ways to connect to the postgres server as the postgres user:

By Switching to postres user
Switch over to the postgres account on your server by typing:
```
sudo -i -u postgres
```

You can now access a Postgres prompt immediately by typing:

    
    [postgres@db-server ~]$ psql
    psql (13.3)
    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.

2. By running the command as postgres user

Use this to run the command directly as the <code>postgres user using sudo
```bash
sudo -u postgres psql</code>

# 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 Postgresql 13 config file is located in this path /var/lib/pgsql/data/pg_hba.conf.

Change peer identification to trust:

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

Change ident identification to md5

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

Add a block to allow access from everywhere:

Add this content to the file /var/lib/pgsql/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/data/postgresql.conf

listen_addresses=&#039;*&#039;

Enable and restart postgresql server to reload the configs

sudo systemctl restart postgresql
sudo systemctl enable postgresql

# 6. User management

# Create Super user

Let us create a user with super user privileges that we can use to administer postgres.

Connect to the DB as postres role

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

postgres=#

Create super user with name root:

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

Check the user if its been created and granted necessary privileges:

postgres=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD &#039;MrBpR89Yskv3hofGLP&#039;;
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                          | {}

# 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 Postgresql13 on an Ubuntu server, do some basic configurations then do basic user management.