How to install PostgreSQL on CentOS 8

Preamble

​​

PostgreSQL or Postgres is a universal object-relational database management system with open source code and many additional functions that allow you to create fault-tolerant environments or complex applications.
In this article, we will discuss how to install the PostgreSQL database server on CentOS 8. Before choosing the version to install, make sure that your applications support it.

Required components

To be able to install packages, you need to login as root or sudo user.

Installing PostgreSQL on CentOS 8

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

To display a list of available PostgreSQL module threads, type:

dnf module list postgresql

The output shows that the postgresql module is available with two threads. Each thread has two profiles: server and client. By default, thread 10 with a profile server is used:

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

• To set the default thread, enter PostgreSQL server version 10.0:

sudo dnf install @postgresql:10

• To install the PostgreSQL server version 9.6, type:

sudo dnf install @postgresql:9.6

You can also install the contrib package, which provides some additional features for a PostgreSQL database:

sudo dnf install postgresql-contrib

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

sudo postgresql-setup initdb

Initializing database ... OK

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

sudo systemctl enable --now postgresql

Use the psql tool to check the installation by connecting to the PostgreSQL database server and printing its version:

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

PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20

PostgreSQL Roles and Authentication Methods

PostgreSQL handles database access permissions using the concept of roles. A role may represent a database user or a group of database users.

PostgreSQL supports several authentication methods. The most commonly used methods:

• Trust – a role can connect without a password if the conditions defined in it pg_hba.conf are met.
• Password – a role that can connect by providing a password. Passwords can be stored as scram-sha-256,md5, and password (plain text).
• Ident – is supported only for TCP/IP connections. It works by obtaining the user name of the client operating system, with the additional mapping of user names.
• Peer is the same as Ident, but it is only supported on local connections.
  The PostgreSQL client authentication is defined in a configuration file named pg_hba.conf. By default, PostgreSQL is configured to use peer-to-peer authentication for local connections.

Postgres user is automatically created when you install PostgreSQL server. This user is a superuser for the PostgreSQL instance. This is equivalent to the user root MySQL.

To log on to the PostgreSQL server as a postgres user, first switch to a user and then open the PostgreSQL prompt using the psql utility:

sudo su - postgres
psql

From here you can interact with a PostgreSQL instance. To exit the PostgreSQL shell, type:

\q

You can also access the PostgreSQL command line without switching sudo users with this command:

sudo -u postgres psql

Usually the postgres user is only used from localhost.

Creating PostgreSQL Role and Database

Only superusers and roles with CREATEROLE privileges can create new roles.

In the following example, we will create a new role with the name andreyex, a database with the name andreyexdb and grant access rights to the database.

1. First, connect to the PostgreSQL shell:

sudo -u postgres psql

2. Create a new PostgreSQL role using the following command thr:

create role andreyex;

3. Create a new database:

create database andreyexdb;

4. Grant the user access rights to the database by performing the following query:

Grant all privileges on database andreyexdb to andreyex;

Enable remote access to the PostgreSQL server

By default, the PostgreSQL server only listens to the local interface 127.0.0.1.

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

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

Scroll down to CONNECTIONS AND AUTHENTICATION and add/edit the following line:

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

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

# - Connection Settings -

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

Save the file and restart the PostgreSQL service with the help:

sudo systemctl restart postgresql

Check the changes with the ss utility:

ss -nlt | grep 5432

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

The output above shows that the PostgreSQL server is listening to the default port on all interfaces (0.0.0.0). The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Below are some examples showing the different uses:

/var/lib/pgsql/data/pg_hba.conf

# 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

Conclusion

CentOS 8 provides two versions of PostgreSQL: 9.6 и 10.0.For more information on this subject, please visit the PostgreSQL documentation (https://www.postgresql.org/docs/10.0/static/index.html).

How to Install PostgreSQL 12 on CentOS 8 RHEL 8

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.