How to Install PostgreSQL on CentOS 8

PostgreSQL or Postgres is an open source relational database management system. It includes many advanced features which allows you to create complex web applications. It is a popular and has many advanced features like reliable transactions and concurrency without read locks. This tutorial explains how to install PostgreSQL database server on CentOS 8.

Before start installing ensure that you are login as a root or user with sudo privileges.

Install PostgreSQL on CentOS 8#

Currently, in from the CentOS repositories, there are two versions 10.0 and 9.6 of PostgreSQL server are available for installation. Run the below command, to get list of available PostgreSQL module streams:

dnf module list postgresql

This will show output as below. It is showing two streams of postgresql module. Each stream includes two profiles server and client. The [d] next to stream shows the default stream of PostgreSQL. To install default stream version 10.0, execute below command:

sudo dnf install @postgresql:10

To install a specific PostgreSQL server version 9.6, type:

sudo dnf install @postgresql:9.6

You should install contrib package which will gives several additional features for the PostgreSQL database:

sudo dnf install postgresql-contrib

On the completion of the installation, you should initialize the PostgreSQL database using below command:

sudo postgresql-setup initdb

Initializing database ... OK

If you would like to start PostgreSQL service on boot and then enable it:

sudo systemctl enable --now postgresql

Now verify the installation by connecting the to the PostgreSQL database server:

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

It will display output as below:

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

PostgreSQL Roles and Authentication Methods#

PostgreSQL roles give the permission to access database. A role can represent a database user or a group of database users.

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

• Trust – This role can connect without a password and the conditions defined in the pg_hba.conf are met.
• Password – 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.

The configuration file named pg_hba.conf holds the PostgreSQL client authentication. By default, for local connections, PostgreSQL is set to use the peer authentication method.

Once you install the PostgreSQL server it will automatically create a postgres user. This user is the superuser for the PostgreSQL instance. We can consider same as root user of MySql server.

You can login as a postgres user, you should first switch to the user or you can use direct sudo command. Then access the PostgreSQL prompt using the psql utility:

sudo su - postgres

psql

To quite type q.

Create PostgreSQL Database and Role#

The superuser and users with CREATEROLE privileges can create new roles.

Perform the below steps to create role and database:

At first, connect to the PostgreSQL shell:

sudo -u postgres psql

Create new role sweta using below command:

create role sweta;

To create new database type:

create database tecnstuff;

Now you should grant the permission on the database by executing below command:

grant all privileges on database tecnstuff to sweta;

Enable remote access to PostgreSQL server#

When you are using PostgreSQL server in network you should enable remote access. By default, the PostgreSQL server listens only on the local interface 127.0.0.1.

Open the configuration file, to enable the remote access to your PostgreSQL server.

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

Find CONNECTIONS AND AUTHENTICATION section and add or edit below line:

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

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

sudo systemctl restart postgresql

You can test the changes using ss utility:

ss -nlt | grep 5432

It will show output as given below:

LISTEN   0    128    0.0.0.0:5432    0.0.0.0:*       
LISTEN   0    128    [::]:5432      [::]:*  

At last, edit the pg_hba.conf file to configure the server to accept remote connections.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

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

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

Conclusion#

You have successfully learned how to install PostgreSQL versions: 9.6 and 10.0 on CentOS 8 system. You can get more details about PostgreSQL at PostgreSQL Documentation.