How to install Postgresql on CentOS 8

PostgreSQL or Postgres is an open source, multi-purpose relational database management system. It has many advanced features that allow you to build fault-tolerant environments or complex applications.

In this guide, we will discuss how to install the PostgreSQL database server on CentOS 8. Before choosing which version you want to install, make sure your app supports it.

We will explore the basics of PostgreSQL database management.

1. Prerequisites##

In order to install the package, you need to log in to the system as root or another user with sudo privileges.

2. Install Postgresql on CentOS 8

At the time of writing this article, there are two PostgreSQL server versions available for installation: 9.6 and 10.0.

In order to list the available PostgreSQL modules, enter:

dnf module list postgresql

The output shows that there are two versions of postgresql. Each version has two parts: server and client. Server version 10 is the default version:

CentOS-8- AppStream
Name          Stream    Profiles              Summary
postgresql    10[d]    client, server [d]    PostgreSQL server and client module
postgresql    9.6       client, server [d]    PostgreSQL server and client module

To install the default version, PostgreSQL server version 10.0, enter:

sudo dnf install @postgresql:10

To install PostgreSQL server version 9.6, enter:

sudo dnf install @postgresql:9.6

You may also want to install the contrib packages, which provide some additional features to the PostgreSQL database:

sudo dnf install postgresql-contrib

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

sudo postgresql-setup initdb

Initializing database ... OK

Start the PostgreSQL service, and enable the PostgreSQL service at boot.

sudo systemctl enable --now postgresql

Use the psql tool to connect to the PostgreSQL database server and print its version number to verify the installation process.

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

PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc(GCC)8.2.120180905(Red Hat 8.2.1-3),64-bit

Three, PostgreSQL role and identity authentication method##

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

Trust-As long as the conditions defined by pg_hba.conf are met, a role can connect to the server without using a password
Password-With a password, a role can connect to the server. The password can be stored as scram-sha-256, md5, and password (plain text).
Ident-Only supports TCP/IP connection. It usually obtains the user name of the client operating system through an optional user name mapping table.
Peer-Like Ident, only local connections are supported.

PostgreSQL client authentication is usually defined in the pg_hba.conf file. By default, for local connections, PostgreSQL is set to authenticate against peers.

In order to log in to the PostgreSQL server as the postgres user, first switch the user, and then use the psql tool to access PostgreSQL.

sudo su - postgres
psql

From here, you can interact with the PostgreSQL instance. Exit the PostgreSQL Shell and enter:

\ q

You can also use the sudo command to access PostgreSQL without switching users:

sudo -u postgres psql

Usually, the postgres user is only used locally.

Fourth, create a PostgreSQL role and database##

Only super users and roles with CREATEROLE permission can create new roles.

In the following example, we create a role named john, a database named johndb, and grant permissions on the database

First, connect to the PostgreSQL shell:

sudo -u postgres psql

Create a new PostgreSQL role with the following command:

create role john;

Create a new database:

create database johndb;

Through the following query statement, grant the user all permissions on the database.

grant all privileges on database johndb to john;

Five, enable remote access to PostgreSQL server##

By default, the PostgreSQL server only listens on the local network interface: 127.0.0.1.

To allow remote access to your PostgreSQL server, open the configuration file:

sudo nano /var/lib/pgsql/data/postgresql.conf

Swipe down to the CONNECTIONS AND AUTHENTICATION section, and add or edit the following line:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses ='*'     # what IP address(es) to listen on;

Save the file and restart the PostgreSQL service using the following command:

sudo systemctl restart postgresql

Use the ss tool to verify this modification:

ss -nlt | grep 5432

LISTEN   01280.0.0.0:54320.0.0.0:*
LISTEN   0128[::]:5432[::]:*

The above output shows that the PostgreSQL server is already listening on the default port of all network interfaces (0.0.0.0).

The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Here are some examples showing different user examples:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane can access all databases from all locations using an md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane can access only the janedb database from all locations using an md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane can access all databases from a trusted location(192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Six, summary##

CentOS 8 provides two PostgreSQL versions: 9.6 and 10.0.

For more information on this topic, please visit: PostgreSQL Official Document.