PostgreSQL is an open-source, object-relational database system that provides an implementation of the SQL querying language. It has all the standard features of traditional commercial database systems with additional enhancements in next-generation DBMS systems.

Most Linux platforms, such as Debian, Red Hat / CentOS, SUSE, and Ubuntu, have PostgreSQL integrated with their package management. It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system, including automatic patching and other update management functionality.

This tutorial will guide you through how to install PostgreSQL on CentOS systems. We will be using CentOS 7 for this tutorial.

This ensures compatibility with your system and allows for a smoother installation process. If you're unsure of your current CentOS version, refer to our guide on how to check the CentOS version for detailed instructions.

Prerequisites

  • A CentOS 7 Linux server
  • A root user account or a user account with sudo privileges
  • Access to the terminal window
  • Basic understanding of Linux commands

There are two different ways to install PostgreSQL on CentOS 7:

  1. Install PostgreSQL from existing CentOS repositories.
  2. Install from the PostgreSQL repository.

Method 1: Install from the CentOS repository

Step 1: Install PostgreSQL

Before we start the installation, we will update the system repository to have the latest PostgreSQL version installed on your system. However, remember that you will most likely not get the newest version of the package.

sudo yum update

To install PostgreSQL, enter the following command:

sudo yum install postgresql-server postgresql-contrib

Step 2: Start the database.

Once the installation is completed, you should initialize the database before starting PostgreSQL.

sudo postgresql-setup initdb

Now to start the database, enter the following command:

sudo systemctl start postgresql

The database installation has been concluded successfully. However, as an optional feature, you can automatically enable PostgreSQL to start during each reboot. To do that, simply enter the following command:

sudo systemctl enable postgresql

For those looking to enhance their server management capabilities, you might also want to explore how to install DirectAdmin on CentOS 7 to streamline your hosting environment.

Step 3: Verify PostgreSQL

We will try to connect to the PostgreSQL database server to verify the installation using the psql tool.

sudo -u postgres psql -c "SELECT version();"

Your installation is successful if you get an output with the PostgreSQL version.

Step 4: Connect to PostgreSQL

To connect to the PostgreSQL prompt:

 sudo -u postgres psql

Method 2: Install from the PostgreSQL repository

Step 1: Download PostgreSQL

In this method, we will be directly installing PostgreSQL from the PostgreSQL repository. You will be able to select the latest version of PostgreSQL. For this tutorial, we will be using PostgreSQL version 9.6.

To download PostgreSQL version 9.6.3, enter the below command. Similarly, you can download any version by replacing the URL with the preferred version, which can be found on the official PostgreSQL website.

wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Step 2: Add PostgreSQL to the system packages.

Now enter the following command to install PostgreSQL from the downloaded file. (remember to change the file name if you will be installing a different version).

sudo yum install pgdg-centos96-9.6-3.noarch.rpm epel-release

Once the installation is complete, run an update of the system packages.

sudo yum update

Step 3: Install PostgreSQL.

To complete the installation with all the needed functionalities and attributes, enter the following command:

sudo yum install postgresql96-server postgresql96-contrib

The -contrib will add some additional utilities and functionalities to the PostgreSQL installation.

If you want to start PostgreSQL on system reboot, enter the following command (optional).

sudo systemctl enable postgresql-9.6

Step 4: Initialize the database

To initialize the installed database, enter the following command:

sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

Now we will restart PostgreSQL to make all the changes made come to effect:

sudo systemctl start postgresql-9.6

Step 5: Verify PostgreSQL

We will try to connect to the PostgreSQL database server to verify the installation using the psql tool.

sudo -u postgres /usr/pgsql-10/bin/psql -c "SELECT version();"

Your installation is successful if you get an output with the PostgreSQL version.

Step 6: Connect to PostgreSQL

To connect to the PostgreSQL prompt:

 sudo -u postgres psql

Creating PostgreSQL roles and databases

Only superusers and roles with CREATEROLE privilege can create new roles in PostgreSQL. In the following example, we will create a new role named test a database named testdb and grant privileges to the database.

Connect to PostgreSQL shell by:

sudo -u postgres psql

Now we will create a new role. To create the new role, enter the following:

CREATE ROLE test;

The next step is to create a new database using this command.

CREATE DATABASE testdb;

To grant permissions to the user on the database we created in the previous step, run the following query:

GRANT ALL PRIVILEGES ON DATABASE testdb TO test;

PostgreSQL Change password

By default, Postgres uses a concept called “roles” to handle authentication and authorization. During the installation process, a user account called Postgres was created, and if you would like to change the user’s password enter the following:

To ensure the security of your PostgreSQL installation, it’s also crucial to be aware of how to reset the forgotten root password on your CentOS system. For detailed instructions, refer to our guide on how to reset the forgotten root password on CentOS.

sudo passwd postgres

You can also change the password through the PostgreSQL prompt. To do this, enter the following commands. Be sure to change the “newpassword” with your unique password.

su – postgres

psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Now you can enter the PostgreSQL client shell:

psql postgres

If you enter \h, you will receive a list of all available commands. To quit the shell environment back to the regular system terminal, enter \q.

Conclusions

This article should help you set up PostgreSQL on CentOS 7. Both installations are straightforward, whether you decide to install from the PostgreSQL repository or the local CentOS repository.

Once you've set up PostgreSQL, you might want to consider optimizing your database performance by hosting it on a CentOS VPS Hosting solution, which offers enhanced flexibility and scalability for database management.

If this article was of help, let us know in the comments below (we are always eager to hear your feedback). Additionally, if you're looking to enhance your server management experience, check out our guide on installing Webmin on CentOS for an easy-to-use web-based interface.

People also read: 

Save Save