How to Install PostgreSQL on CentOS 8

In this article, we will have explained the necessary steps to install and configure PostgreSQL on CentOS 8. Before continuing with this tutorial, make sure you are logged in as a user with sudo privileges. All the commands in this tutorial should be run as a non-root user.

PostgreSQL is a powerful, open-source object-relational database management system that uses and expands the SQL language combined with numerous features that safely keep and scale the most complex data workloads.

PostgreSQL ships with a number of features intended to help programmers to develop applications, administrators to safeguard data integrity and create fault-tolerant environments, and assist you to manage your data no matter how large or small the dataset

Install PostgreSQL on CentOS

Step 1. The first command will update the package lists to ensure you get the latest version and dependencies.

sudo yum install epel-release
sudo yum update

Step 2. Install PostgreSQL.

PostgreSQL is included in the default repositories of CentOS 8 and can be installed using the following dnf command:

dnf install @postgresql

Once the installation is done, you can initialize the database using the below command:

/usr/bin/postgresql-setup --initdb

To start and enable PostgreSQL you need to use the systemctl command:

sudo systemctl start postgresql    ## <-- start the server ##
sudo systemctl stop postgresql     ## <-- stop the server ##
sudo systemctl restart postgresql  ## <-- resstart the server ##
sudo systemctl status postgresql   ## <-- get status of the server ##

Step 4. Create PostgreSQL Database and Role.

• Create a new PostgreSQL Role The following command will create a new role named ramona:

  sudo su - postgres -c "createuser intanramona"

• Create a new PostgreSQL DatabaseCreate a new database named ramona using the createdb command:

  sudo su - postgres -c "createdb intanramona"

• Grant privileges to grant permissions to the ramona user on the database we created in the previous step, connect to the PostgreSQL shell:

  sudo -u postgres psql

  and run the following query:

  grant all privileges on database intanramona db to intanramona;

Step 5. Enable remote access to the PostgreSQL server.

Use the following steps to enable remote access to the PostgreSQL server:

sudo nano /etc/postgresql/10/main/postgresql.conf

Update listen_addresses like given below:

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

# - Connection Settings -

listen_addresses = '*'

Now restart the PostgreSQL service with systemctl command:

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                [::]:*

Finally, update pg_hba.conf file to configure server remote connections using the below examples:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user test_user will be able access all databases from all locations using a md5 password
host    all             intanramona            0.0.0.0/0                md5

# The user test_user will be able access only the test_db from all locations using a md5 password
host    test_db         intanramona            0.0.0.0/0                md5

# The user test_user will be able access all databases from a trusted location (192.168.43.108) without a password
host    all             intanramona            192.168.1.146            trust

Congratulation, you have learned how to install and configure PostgreSQL on CentOS 8. If you have any questions, please leave a comment below.