How To Install PostgreSQL on CentOS/RHEL 8

This guide will walk you through the steps to install PostgreSQL database on CentOS/RHEL 8. These steps can also be applied if you are running Fedora or Oracle Linux.

Note: With CentOS 8 release, yum command has been replaced with dnf and in near future yum package manager will be discontinued. It is now recommended to use dnf for installing packages but if you still wish to use yum you can use it.

Prerequisite

You will need one (physical or virtual) machine with CentOS/RHEL 8 minimal installed having sudo non-root user privileges.

Set Timezone

It is important to set the correct timezone on your fresh installed server before proceeding to install database:

You can find the correct timeones with below command:

sudo timedatectl list-timezones

Type below command to set the time zone, replace Asia/Karachi with yours:

sudo timedatectl set-timezone Asia/Karachi

Adding PostgreSQL/EPEL Repo

Now, we will add postgresql official repository to get the most latest version of postgresql database:

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

We will also add EPEL repository:

sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
sudo dnf config-manager --set-enabled PowerTools

sudo dnf -y install yum-utils
sudo yum-config-manager --enable pgdg12

Installing PostgreSQL Database

At the time of writing this tutorial, the most latest release of postgresql was 12. You can install the most latest release of postgresql database with below commands:

sudo dnf -qy module disable postgresql
sudo dnf -y install postgresql12-server postgresql12

Now that the postgresql server is installed, you will perform some basic steps to prepare a new database cluster.

Creating PostgreSQL Database Cluster

A database cluster is a collection of databases that are managed by a single server instance. Creating a database cluster consists of creating the directories in which the database data will be placed, generating the shared catalog tables, and creating the template and postgres databases.

You have to create a new PostgreSQL database cluster before you can use your Postgres database.

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

You will see the output similar to below:

Initializing database ... OK

Now, start the postgresql database service with below command:

sudo systemctl start postgresql-12
sudo systemctl enable postgresql-12

Update firewall rules to allow postgresql if you need to access database from remote clients.

sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

Now that PostgreSQL is up and running, you will go over roles to learn how Postgres works and how it is different from similar database management systems you may have used in the past.

Creating PostgreSQL Roles and Databases

By default, Postgres uses a concept called roles to handle in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term role.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log in to that account.

There are several ways to use this account to access Postgres.

Switch over to the postgres account on your server by typing:

sudo -i -u postgres

Access a Postgres prompt by typing:

psql

This will log you into the PostgreSQL prompt, and from here you can interact with the database management system right away.

You can log out from the PostgreSQL prompt by typing:

postgres=# exit

This will bring you back to the original Linux sudo prompt.

Accessing a Postgres Prompt Without Switching Accounts

For instance, in the earlier example, you were instructed to get to the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. You could do this in one step by running the single command psql as the postgres user with sudo, like below:

sudo -u postgres psql

This will log you directly into Postgres prompt.

Creating a New Role in Postgres

With fresh installation, you just have the postgres role configured within the database. You can create new roles using the createrole command. The --interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.

To create a user on the default database with prompting additional attributes:

sudo -u postgres createuser --interactive

Enter name of role to add: peter
Shall the new role be a superuser? (y/n) y

To delete a user from the default database with prompting additional attributes:

sudo -u postgres dropuser --interactive

Enter name of role to drop: peter
Role "peter" will be permanently removed.
Are you sure? (y/n) y

Creating a New Database in PostgreSQL

If the user you created in the last section is called peter, that role will attempt to connect to a database which is also called peter by default.

You can create the appropriate database with the createdb command.

sudo -u postgres createdb peter

Now that you’ve created a new database, you will log in to it with your new role.

Opening a Postgres Prompt with the New Role

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

sudo adduser peter

Once this new account is available, you can switch over and connect to the default database by typing:

sudo -i -u peter psql -d postgres

If you want your user to connect to its own database, you can do so by specifying the database like this:

sudo -i -u peter psql -d peter

Wrapping up

We hope this guide was helpful to install and configure PostgreSQL on your CentOS or RHEL 8 server.