How to Install PostgreSQL on CentOS?

How To Install PostgreSQL On CentOS

In this tutorial, we will learn how to install PostgreSQL on CentOS.

Let’s first discuss what is PostgreSQL! Have you ever visited the PostgreSQL’s official site? If not, then do visit it first. Don’t worry folks. I have provided the link: PostgreSQL. You might be thinking why I asked this question? It’s because if you log on to the site, you would see PostgreSQL is The World’s Most Advanced Open Source Relational Database. Basically, The software which comes under the category of open-source has its source code available for modifications. Wow! It’s great.

Talking about what is PostgreSQL, it is an open-source relational database management system which supports a wide variety of SQL standards such as:

  • Complex SQL queries
  • Triggers
  • Views
  • Transactions
  • Multiversion Concurrency Control(MVCC)
  • Streaming Replication
  • Hot Standby
  • Foreign Keys
  • SQL sub-subjects

It also supports four standard procedural languages. Do you know about them? These procedural languages are-

  • PL/pgSQL
  • PL/Tcl
  • PL/Perl
  • PL/Python

It also supports some of the non-procedural languages such as PL/PHP, PL/V8, PL/Ruby, PL/Java and so on.

PostgreSQL is used to process structured data. Basically it works on OLTP (Online Transactional Processing). Where Oracle, IBM DB2, SQL Server all provide paid services i.e. you have to buy the license to use various services while PostgreSQL is open-source, you don’t need to pay anything. Wow! It’s superb. I hope you now understood why PostgreSQL is widely used.

Let’s discuss how to install PostgreSQL on CentOS. We will install the latest version of PostgreSQL on CentOS 8 (the latest operating system).

Install PostgreSQL on CentOS using Official Repositories

Here, we will discuss how to install PostgreSQL from PostgreSQL repositories. I might say this method is one of the easiest ways to install PostgreSQL. I hope you too find it easy. Let’s discuss the steps regarding this.

Install the repository RPM

The first step is to install the repository RPM. RPM stands for Redhat Package Management. You might be thinking from where did I get this link? Folks, it’s simple. Just visit the official website of PostgreSQL and you would find the download option. Just select your operating system and download it! I have provided the link below :

Click here to download

On this link, you can get the link of any version of PostgreSQL on any version of OS.

After getting the link, use it with the DNF command. DNF command is a software package manager that installs, updates and removes packages on RPM-based Linux distributions. The best part about the DNF command is that it automatically computes dependencies and also determines the action requires to install packages. How great is this! Let’s discuss the command regarding this:

# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install Postgre Through PRepo Step1
Install through link

Disable the module

After installing the RPM, the next step is to disable the built-in PostgreSQL module in the system. For this also, we will use the DNF command. I have already discussed the uses of the DNF command above. Let’s discuss the command for this:

dnf -qy module disable postgresql
Disable Postgre Prepo Step2
Disable PostgreSQL module

Install PostgreSQL on CentOS – Client Packages

Okay, till now we have installed the PostgreSQL related repositories and disabled the modules. Discussing the next step is to download the client packages. We are installing the latest version i.e. 12 of PostgreSQL. Similarly, we will use the DNF command to install the client packages. The command for this is as follows:

# dnf install postgresql12
Install Postgre Packages Prepo Step3
Install the client packages

Install PostgreSQL on CentOS – Server Packages

So, after installing the client packages we have to install the server packages. For this, we use the DNF command to install PostgreSQL on CentOS.

# dnf install postgresql12-server
Postgresql Server Prepo Step4
install the server packages

This command will successfully install the server packages required for PostgreSQL.

Initialize the PostgreSQL Database

After installing the server packages, the next step is to initialize the database. This can be done by running the setup file of PostgreSQL located in /usr/pgsql-12/bin/ directory named PostgreSQL-12-setup file. This file will initialize the database. Let’s discuss the command for this:

sudo  /usr/pgsql-12/bin/postgresql-12-setup initdb
Initialize Db Prepo Step5
Initialize Db

If the response for this is OK on your screen too, that means you have successfully installed the PostgreSQL.

Enable PostgreSQL Service

After successfully initializing the database, the next step is to enable the services. This can be done using systemctl command with enable keyword. I have described the command below:

# systemctl enable postgresql-12
Enable Postgresql Prepo Step6
Enable Postgresql services

Start PostgreSQL Service

After enabling the PostgreSQL services, the next step is to start the services. For this, we will use systemctl command with the start keyword. I have described the command below:

systemctl start postgresql-12
Start Postgre Prepo Step7
Start PostgreSQL services

Begin with the PostgreSQL

After successfully installing and initializing the services, Let’s begin with PostgreSQL. It will also verify that the PostgreSQL has been successfully installed or not. If you want to find out the version, you can use this command to do so. Let’s look at the command given below:

# sudo -u postgres psql 
Begin With Postgre Step 8
Begin With PostgreSQL

In the above image, you can see the version of PostgreSQL i.e. 12.2.

Set the user authentication

Security is the major concern of every database. We store our jewelry ornaments in Bank because we know that the ornaments will be safe. No one can steal them. Banks have the best security. Similarly, we store our data in a database because it provides the user authentication feature. Let’s learn how to set the password for the database. The command for this is as follows:

\password postgres
Set Password Prepo Step9
Set the Password

Now, you can set the password for your database.

Exit PostgreSQL database

There is always a command to exit from the database. We use exit() in MySQL, quit() in MongoDB. But here it is something different. We will use “\q” to make an exit from the database. The command for this is as follows:

\q
To Exit Step11
To Exit

Structure of the PostgreSQL directory

If you want to look at the directory of PostgreSQL, we can use the tree command to check it. Using tree command will print the structure in tree format. Seems interesting! Let’s discuss the command to view the structure of PostgreSQL directory:

tree -l 1 /var/lib/pgsql/
Tree Step11
view the structure

Steps to Install pgadmin4

After you install PostgreSQL on CentOS, we will move on to the installation of pgadmin. It is the management tool for PostgreSQL and derivative relational databases. The best feature of pgAdmin is that it can run as both web applications and desktop applications. Don’t get confused between PostgreSQL and pgAdmin, both are different things.

PostgreSQL is the database while pgAdmin is a sort of client. You can manipulate schema and data of the PostgreSQL through pgAdmin. Let’s discuss how to connect to pgAdmin.

Install the package

After you install PostgreSQL on CentOS, some supporting packages are also installed. Simply use DNF Command to install the pgAdmin packages. I have already discussed the significance of the DNF command. Below, I have discussed the command to be used to install pgAdmin:

sudo dnf install pgadmin4

It will install all the related packages to pgAdmin.

Start the HTTP services

Here, we need to start or enable HTTP services. As we will access the pgAdmin4 through the browser, so it’s important to enable the HTTP services. We will use systemctl command to start the services. The command used to enable the services is:

# systemctl start httpd
Start Httpd Step 17
Start the service

Configure pgAdmin

Firstly, you need to rename the pgAdmin configuration file. We will use cp command as it will copy the content of the file from one to another and rename it.

What we’re essentially doing is copying the sample configuration file and using that as our base to start with our configuration so we don’t have to redo everything from scratch.

# cp /etc/httpd/conf.d/pgadmin4.conf.sample /etc/httpd/conf.d/pgadmin4.conf
Create Config Pdagminstep1
Rename file

Restart the HTTP services

As we have made the changes, therefore we need to start the HTTP service again and check whether the HTTP syntax is correct or not. The output of checking the syntax should be OK. We will restart the HTTP services again using systemctl command. Below, I have described both the commands:

# httpd -t
# systemctl restart httpd
Httpd Config Syntax Pgadmin Step2
restart services

Declare the path of logs

Here, we need to set the path of the logs and library. But before this, we need to make the directories. For this, we will use the mkdir command. This command is used to define the directories. The command is given as follows:’

# mkdir -p /var/lib/pgadmin4/
# mkdir -p /var/log/pgadmin4/
Mkdir Pgadmin Step3
Make directories

After making directories, you need to edit the configurations. We will use the vi command to open the file. Here, we will declare the path of the log file, SQLITE, session database and storage directories. The command to load the file is:

# vi /usr/lib/python3.6/site-packages/pgadmin4-web/config_distro.py
Vi Pgadmin Step4

Add the following code shown below in the file:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

Make sure you have save the file. To save the file, press Esc key followed by “:wq! “. This command will save the content and exit from the text editor. If you use “:wa” , it will only make the exit without saving the contents of the file. Be careful while using the commands.

Create user authentication

Here, you need to create the user authentication to log in to the pdAdmin. Be careful while adding email id and passwords. These user details you have to use while login to the pgAdmin page. I have provided the command below:

# python3 /usr/lib/python3.6/site-packages/pgadmin4-web/setup.py
Set Emailid Pgadmin Step5
Set Emailid and password

Accessing the web interface

As of now, we have successfully installed pgAdmin. Let’s connect to the pgAdmin login page through the web browser. All you need to do is type the address to access the web interface. I have provided the command below:

http://SERVER_IP/pgadmin4
OR
http://localhost/pgadmin4
Pgadmin4 Login Page Cropped
Pgadmin4 Login Page Cropped

Hence, you have successfully installed pgAdmin on your system

Conclusion

That’s it. Here we are complete this tutorial on how to install PostgreSQL on CentOS. If you face any issue, do let us know in the comment.

Leave a Comment

Your email address will not be published. Required fields are marked *