Install PostgreSQL 13 on CentOS, RHEL 7, 8

This guide will walk you through the steps to install PostgreSQL release 13 on a CentOS 7 or 8. These instructions can also be applied if you are running a Fedora or Oracle Linux in your environment.

 

Prerequisite

You will need one (physical or virtual) machine with CentOS 7 or 8 minimal installed having sudo non-root user privileges. You must set correct timezone on your server before proceeding with the installation:

sudo timedatectl set-timezone Asia/Karachi

 

Install EPEL Repository

For CentOS 7, type below command to install epel repository:

sudo yum install epel-release

For CentOS 8, type below command to install epel repository:

sudo dnf install epel-release
sudo dnf config-manager --set-enabled PowerTools

 

Install PostgreSQL

For CentOS 7, type below command to install PostgreSQL release 13:

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

For CentOS 8, type below command to install PostgreSQL release 13:

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -y install yum-utils
sudo yum-config-manager --enable pgdg13
sudo dnf -qy module disable postgresql
sudo dnf -y install postgresql13 postgresql13-server

 

Initialize PostgreSQL Cluster

A PostgreSQL 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. 

Type below command to initialize PostgreSQL database cluster:

sudo /usr/bin/postgresql-13-setup initdb
Initializing database ... OK

Type below command to start PostgreSQL server:

sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13
sudo systemctl status postgresql-13

Now that your database is up and running, let's learn how PostgreSQL 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 default 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. 

Let's begin with the switching over to the postgres account:

sudo -i -u postgres

Type below to access a postgres prompt:

psql

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

postgres=#

Type below to list default databases:

postgres=# \list

 

Type below to logout from the postgres prompt:

postgres=# \q

Type below to exit from the postgres user shell

-bash-4.2$ exit

 

Access PostgreSQL without Switching the Account

In the above example, you were asked to access PostgreSQL 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 as the postgres user with sudo, like below:

sudo -u postgres psql

This will log you directly into the postgres prompt.

Type below to logout from postgres prompt:

postgres=# \q

 

Creating or Removing a Role

With fresh installation, you just have the default 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.

sudo -u postgres createuser --interactive

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

You can also interactively remove a role from the default database:

sudo -u postgres dropuser --interactive

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

 

Creating a New Database

You can create a new database and its associated user like below:

sudo -u postgres createuser testuser
sudo -u postgres createdb -O testuser testdb

 

Access PostgreSQL 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 testuser

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

sudo -i -u postgres psql -d testdb

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

sudo -i -u testuser psql -d testdb

 

Allow Remote Access 

By default PostgreSQL access is bound to localhost only. To make the PostgreSQL accept outside connection, you will need to make following changes in the configuration files:

sudo vi /var/lib/pgsql/13/data/postgresql.conf

Locate, uncomment and change listen_addresses = 'localhost' to

listen_address ='*'

Save and close the editor when you are finished. 

Next, edit pg_hba.conf file to make the changes:

sudo vi /var/lib/pgsql/13/data/pg_hba.conf

Add the following line at the end of the file to allow PostgreSQL to accept connection from anywhere:

host    all             all             0.0.0.0/0               md5

You can also limit PostgreSQL to accept connection only from specified IPs:

host    all             all             your_client_machine1_ip/32               md5
host    all             all             your_client_machine2_ip/32               md5
host    all             all             your_client_machine3_ip/32               md5

Save and close the editor when you are finished.

 

Update Firewall Rules

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

 

Wrapping up 

We hope this guide was helpful to set up PostgreSQL release 13 on your CentOS 7 or 8.