Configure and Install PostgreSQL on CentOS 7 and 8

Posted on January 20th, 2020

Configure and Install PostgreSQL on CentOS 7 and 8

PostgreSQL is also known as Postgres, and it is a free, powerful, and open-source relational database management system. PostgreSQL provides an implementation of the SQL querying language, and it is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. PostgreSQL answers the structured query language similar to MySQL.

In this tutorial, you learn how to install PostgreSQL on CentOS from the CentOS repository and PostgreSQL repository. You should log in as a non-root user and have sudo privileges as the prerequisites.

 

Install PostgreSQL on CentOS from CentOS repository

You can install PostgreSQL on the CentOS repository by running the following command with -contrib package. The -contrib package adds additional functionality and features to the installed PostgreSQL.

$ sudo yum install postgresql postgresql-contrib

After the installation, you need to initialize the PostgreSQL database by running the following command.

$ sudo postgresql-setup initdb

After initializing the database, you need to start the PostgreSQL service and then enable it to make sure that the service starts up during the system boot-up. Run the following commands to start and enable PostgreSQL service.

$ sudo systemctl start postgresql

$ sudo systemctl enable postgresql

To check the PostgresSQL version and confirm installation, run the following command.

$ sudo -u postgres psql -c “SELECT version();”

The psql utility in a command helps to interact with the PostgreSQL database easily.

 

Install PostgreSQL 12 version on CentOS from PostgreSQL repository

To install the PostgreSQL 12 on CentOS, run the following commands.

First, you need to enable the PostgreSQL repository on CentOS by running the following command.

 

On CentOS 7:

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

 

On CentOS 8:

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

After installation, you can check the information about the installed package by running the following command.

$ rpm -qi pgdg-redhat-repo

After enabling the PostgreSQL repository, run the following command to install PostgreSQL 12 on CentOS 7 and 8. You can add the -contrib package along with the installation command to add additional functionalities and features to your PostgreSQL.

$ sudo yum -y install epel-release yum-utils

$ sudo yum-config-manager –enable pgdg12

$ sudo yum install postgresql12-server postgresql12-contrib

The configuration file of PostgreSQL database gets stored in the ‘/var/lib/pgsql/12/data/postgresql.conf’.

After installation, run the following command to initialize the database.

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

After initializing the database, you need to start the PostgreSQL service and then enable it to make sure that the service starts up during the system boot-up. Run the following commands to start and enable PostgreSQL service.

$ sudo systemctl start postgresql

$ sudo systemctl enable postgresql

To check the status of PostgreSQL service, run the following command.

$ sudo systemctl status postgresql-12

To check the PostgresSQL version and confirm installation, run the following command.

$ sudo -u postgres /usr/pgsql-12/bin/psql -c “SELECT version();”

The psql utility in a command helps to interact with the PostgreSQL database easily. If you have a firewall and remote clients on your database server, then you need to allow PostgreSQL service by running the following command.

$ sudo firewall-cmd –add-service=postgresql –permanent

$ sudo firewall-cmd –reload

 

Set PostgreSQL Admin User’s Password

During installation, a postgres user gets created by default. Log in to PostgreSQL as an admin user and set the password for the admin user by running the following command.

$ sudo su – postgres

~]$ psql -c “alter user postgres with password ‘StrongPassword’”

ALTER ROLE

 

PostgreSQL Databases and Roles

There are different methods to authenticate PostgreSQL, such as Ident, Trust, Password, and Peer. The Ident method is used on TCP/IP connection, while the Trust method is used to connect to the server without a password using the given credential in the pg_hba.conf (PostgreSQL configuration) file.

To log in to PostgreSQL, run the following command.

$ sudo su -postgres

psql

And to exit from PostgreSQL, run the following.

\q

 

PostgreSQL Database and Role Creation

The createdb and createuser methods are used to create database and user in PostgreSQL. You can create a new database in PostgreSQL called ‘test_database’ by running the following command.

$ sudo su – postgres -c “createdb test_database”

After creating the database, if you want to create a new role called ‘test_usr’, run the following command.

$ sudo su – postgres -c “createuser test_usr”

Next, you should grant permission to the newly created user ‘test_usr’ for the newly created database ‘test_database’. First, log in to PostgreSQL and run the following query in PostgreSQL shell.

$ sudo -u postgres psql

grant all privileges on database test_database to test_usr;

 

Open PostgreSQL Prompt with New Role

Make sure that you have the same Linux user available as PostgreSQl user and database to open the PostgreSQL prompt with the new role. To create a new user named ‘test_usr’ if the user is not available on CentOS, run the following command.

$ sudo adduser test_usr

After creating the user, you can connect to the database by running the following command.

$ sudo -u test_usr psql

 

Enable Remote Access to PostgreSQL Server

If you want to enable remote access to the PostgreSQL server, follow the below steps.

First, edit the ‘/etc/postgresql/12/main/posgresql.conf’ file and replace the ‘listen_address’ value to your server IP or * to allow all the interfaces in the CONNECTION AND AUTHENTICATION section.

$ sudo nano /etc/postgresql/12/main/posgresql.conf

listen_addresses = ‘<Your_Server_IP or *’

After making the changes, restart the PostgreSQL service to reflect the changes. Run the following systemctl command to restart the PostgreSQL service.

$ sudo systemctl restart postgresql

To verify the changes made, run the following command.

$ ss -nlt | grep 5432

Finally, you should make the following changes in the pg_hba.conf file to enable the remote connection to the server.

# TYPE             DATABASE        USER            ADDRESS                 METHOD

# The user test_usr can access all the databases from all location using an md5 (Message-Digest algorithm) password

host                   all                    test_usr            0.0.0.0/0                md5

 

# The user test_usr can access only the test_database from all locations using an md5 password

host                 test_database          test_usr            0.0.0.0/0                md5

 

# The user test_usr can access all databases from a trusted location (IP) without a password

host                 all             test_usr            <IP_address>            trust

 

If you need any further help, please do reach our support department.

 

 

Leave a Reply