CentOS

How to Install PostgreSQL 12 on CentOS 8

What is PostgreSQL?

PostgreSQL or Postgres is one of the most popular RDBMS (Relational Database Management System). It’s free and open-source RDBMS emphasizing extensibility and technical standards compliance.

It’s designed to handle a range of workloads. It is an advanced RDBMS with an enterprise-class grade, supports both SQL (relational) and JSON (non-relational) querying.

PostgreSQL can be used for simple applications to the data warehouse and web application with many concurrent users.

What we will do?

In this tutorial, we will show you how to install and configure PostgreSQL 12 on CentOS 8 server.

Prerequisites

For this tutorial, make sure you have got a CentOS 8 server with root privileges.

Below is the tutorial about the CentOS 8 server installation.

Install CentOS 8 Server

Before going any further, log in to your CentOS server and type the sudo command to get the root privileges on your system.

sudo su

Step 1 – Add EPEL and PostgreSQL Repository

First, you must add the EPEL and the official PostgreSQL repository to the CentOS 8 system, because the PostgreSQL 12 is only available on the official PostgreSQL repository.

Add the EPEL (Extra Packages for Enterprise Linux) repository using the command below.

dnf install -y epel-release

After that, add the official PostgreSQL repository for CentOS 8 using the following command.

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Now check list of enabled repository on the CentOS system using the command below.

dnf repolist

Below is the result you will get.

Check list repository CentOS 8

As a result, you’ve added the EPEL and PostgreSQL repository to the CentOS 8 system.

Step 2 – Enable PostgreSQL Repository

In this step, you will enable the PostgreSQL 12 repository on the CentOS 8 system. because there is multiple version of PostgreSQL on your system, you must set up the default version of PostgreSQL repository.

Check all available PostgreSQL on the CentOS system using the following command.

dnf module list postgresql

Below is the result you will get.

Repository Module CentOS

As can be seen, you have the following result:

  • You’ve multiple versions of PostgreSQL repository, the PostgreSQL 9.5, 10, and 12.
  • The PostgreSQL 10 is the default version of PostgreSQL repository, the ‘[d]’ sign as default.

Next, you must enable the PostgreSQL 12 repository, so that you can install the PostgreSQL 12 to your CentOS system.

Enable PostgreSQL 12 repository using the dnf command below.

dnf module enable postgresql:12

Type ‘y‘ to enable the PostgreSQL 12 repository.

Afterward, check the default version of PostgreSQL that can be installed using the following command.

dnf info postgresql

Below is the result you will get.

Enable PostgreSQL 12 Repository

As a result, you’ve enabled the PostgreSQL 12 repository. In that case, you can continue to install the PostgreSQL 12 on the CentOS 8 system.

Step 3 – Install PostgreSQL 12 on CentOS 8

Now you can install the PostgreSQL 12 to the CentOS 8 system using the dnf command below.

dnf install postgresql-server postgresql-contrib

Once the installation is complete, initialize the PostgreSQL database using the following command.

sudo postgresql-setup initdb

After that, start the PostgreSQL service and add it to the system boot, then check the status of PostgreSQL service.

systemctl enable --now postgresql
systemctl status postgresql

Below is the result you will get.

Install PostgreSQL 12 on CentOS 8

As can be seen, the PostgreSQL 12 is up and running on CentOS 8. And by default, it’s running on the TCP port ‘5432’, check the PostgreSQL port using netstat command below.

netstat -plntu

Check Port with Netstat

As a result, you’ve successfully installed the PostgreSQL 12 on the CentOS 8 server.

Step 4 – Setup PostgreSQL Password

By default, PostgreSQL comes with the default user/role called ‘postgres’. In this step, you will set up the password for the ‘postgres’ user.

Log in to the PostgreSQL shell with ‘postgres’ user.

sudo -i -u postgres psql

Change the password of the ‘postgres’ user using the following query.

ALTER USER postgres WITH PASSWORD 'PostgreSQLPass';
\q

As a result, you’ve changed the password of default ‘postgres’ user.

Step 5 – Setup Authentication PostgreSQL

The PostgreSQL database supports for multiple authentication methods, which is controlled by the configuration file ‘/var/lib/pgsql/data/pg_hba.conf’.

The most commonly used authentication methods for PostgreSQL are below.

  • Trust – This method allows users/roles to connect without any password, as long as the requirement is met in the configuration ‘pg_hba.conf’.
  • Password – This method will ask the user/role for its password when connecting to the PostgreSQL. supported options are md5, scram-sha-256, or password as plain-text.
  • Ident – This method works by obtaining the client operating system username. Only supports for TCP/IP connection and additional options ‘map’ for user mapping.
  • Peer – This method is similar to Ident, but only works on local connections.

In this step, you will learn how to set up the PostgreSQL authentication using the password authentication method, and you will use the ‘md5’ as a supported method.

To set up the authentication method, go to the ‘/var/lib/pgsql/data’ directory and edit the configuration ‘pg_hba.conf’.

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

Change the authentication method for local connection to md5 as below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Save and close.

Now restart the PostgreSQL service using the command below.

systemctl restart postgresql

So that the PostgreSQL authentication method has been changed.

Next, log in to the PostgreSQL shell with the user ‘postgres’.

sudo -i -u postgres psql

Type the postgres password that you’ve changed and check the connection info.

\conninfo

Enable Password Authentication PostgreSQL

As a result, you’ve successfully changed the PostgreSQL authentication method with the ‘md5’ password.

Step 6 – Basic PostgreSQL Management

In this step, you will learn about the basics of PostgreSQL operations, such as create a user/role, create a database, create a table, insert data, etc.

Log in to the PostgreSQL shell using the following command.

sudo -i -u postgres psql

Type the password for the ‘postgres’ user.

– Create User/Role

To create user/role with login permission, run the following query.

CREATE ROLE gahyeon WITH LOGIN ENCRYPTED PASSWORD 'gahyeonpasswd';

After that, check all users/roles on PostgreSQL.

\du

As a result, you’ve created a new role named ‘gahyeon’ with permission to log in and connect to the database.

Create Role PoastgreSQL

– Create Database and Grant Privileges

To create the database on the PostgreSQL, run the following query.

CREATE DATABASE gahyeon;

Next, grant the user ‘gahyeon’ to access the database ‘gahyeon’.

GRANT ALL PRIVILEGES ON DATABASE gahyeon TO gahyeon;

Now check available databases on PostgreSQL.

\l

As a result, you’ve created a new database named ‘gahyeon’ and give the user ‘gahyeon’ permission to access the database.

Create Database

– Connect to PostgreSQL Shell as Specific Role

To connect to the PostgreSQL shell using the specific user/role, log in to the postgres user using the following command.

su - postgres

Next, connect to the PostgreSQL shell as user ‘gahyeon’ using the following command.

psql -U gahyeon

Once you’ve logged in, check the connection info.

\conninfo

As a result, you’ve connected to the PostgreSQL shell as a specific user ‘gahyeon’ to the database ‘gahyeon’.

Connect as Specific Role

– Create Table and Insert Data

To create the table, run the following queries.

CREATE TABLE account(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP
);

After that, insert the data to the table ‘account’ using the queries below.

INSERT INTO account VALUES(1,'gahyeon','passgahyeon','[email protected]', '2019-09-01', '2019-12-10');

Now check the table an show all its data.

\dt
select * from account;

As a result, you’ve created a new table ‘account’ and inserted new data into it.

Create Table and Insert Data PostgreSQL

And finally, you’ve successfully installed the PostgreSQL 12 on the CentOS 8 Server.

Write A Comment