How to Install PostgreSQL on CentOS 8

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows 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 to install, make sure that your applications support it.

We will also explore the basics of PostgreSQL database administration.

Prerequisites #

To be able to install packages, you need to be logged in as root or user with sudo privileges
.

Installing PostgreSQL on CentOS 8 #

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

To list the available PostgreSQL module streams, type:

dnf module list postgresql

The output shows that the postgresql module is available with two streams. Each stream has two profiles: server and client. Stream 10 with the profile server is the default one:

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 

1. To install the default stream, PostgreSQL server version 10.0 type:

   sudo dnf install @postgresql:10

2. To install the PostgreSQL server version 9.6 type:

   sudo dnf install @postgresql:9.6 

You may also want to 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 initdb

Initializing database ... OK

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

sudo systemctl enable --now postgresql

Use the psql tool to verify the installation by connecting to the PostgreSQL database server and print 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 20180905 (Red Hat 8.2.1-3), 64-bit

PostgreSQL Roles and Authentication Methods #

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

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.

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

The postgres user is automatically created when you install the PostgreSQL server. This user is the superuser for the PostgreSQL instance. It is equivalent to the MySQL root user.

To log in to the PostgreSQL server as the postgres user, first switch to the user
and then access the PostgreSQL prompt using the psql utility:

sudo su - postgrespsql

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

q

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

sudo -u postgres psql

Typically the postgres user is used only from the localhost.

Creating PostgreSQL Role and Database #

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

In the following example, we will create a new role named john, a database named johndb, and grant privileges on the database.

1. First, connect to the PostgreSQL shell:

   sudo -u postgres psql

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

3. Create a new database:

4. Grant privileges to the user on the database by running the following query:

   GRANT ALL PRIVILEGES ON DATABASE johndb TO john;

Enable remote access to PostgreSQL server #

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

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

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

Scroll down to the CONNECTIONS AND AUTHENTICATION section 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:

sudo systemctl restart postgresql

Verify 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
on 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 different use cases:

/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 PostgreSQL versions: 9.6 and 10.0.

For more information on this topic visit the PostgreSQL Documentation

If you hit a problem or have feedback, leave a comment below.