How to Install PostgreSQL on CentOS 7

In this article, we will have explained the necessary steps to install and configure PostgreSQL on CentOS 7. 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 PostgreSQL is a powerful and open source relational database platform. It’s scalable across multiple platforms and is a widely used and well-loved tool.

Install PostgreSQL on CentOS 7

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.

Now run following command to install PostgreSQL with -contrib package which adds additional features and functionalities to PostgreSQL:

sudo yum install postgresql-server postgresql-contrib

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

sudo postgresql-setup initdb

To start and enable PostgreSQL service after boot run following commands:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 4. Create PostgreSQL Database and Role.

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

  sudo su - postgres -c "createuser meilanamaria"

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

  sudo su - postgres -c "createdb meilanamariadb"

• Grant privilegesTo 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 meilanamariadb to meilanamaria;

Step 5. Enable remote access to 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 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             meilanamaria            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         meilanamaria            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             meilanamaria            192.168.1.146            trust

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