How to Install PostgreSQL on CentOS 7 

Nowadays, most modern applications include a database. However, because there are so many database management systems available, selecting the best one for your needs takes time. PostgreSQL is one of the most popular options.

PostgreSQL is a powerful relational database management system (DBMS) that is free and open source. It is scalable, dependable, community-driven, and equipped with advanced optimization features.

In this tutorial, we’ll show you two methods for installing PostgreSQL on a CentOS 7 VPS and go over the fundamentals of this database management system.

How Does PostgreSQL Work

Unlike NoSQL databases, PostgreSQL stores data in rows, columns, and tables. For relational queries, PostgreSQL uses SQL, while non-relational queries are processed with JSON.

One of the main advantages of PostgreSQL is its large community. Being open-source, it attracts many developers that actively contribute to this DBMS’ growth. Therefore, it’s no surprise that PostgreSQL offers features such as advanced optimization and data types similar to companies like SQL Server and Oracle.

You can install PostgreSQL on CentOS 7 by using one of the following two methods:

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

Let’s review each of these methods in more detail.

How to Install PostgreSQL on CentOS 7 Using the PostgreSQL Repository

The first method is to download PostgreSQL from its official repository. This way, users can be sure they are getting the latest version. Keep in mind that choosing an older version from the official PostgreSQL website is also possible.

1. Access Your Server

The first step is to access your virtual server via an SSH client such as PuTTY.

2. Download PostgreSQL Repository RPM

Then, download and install the repository RPM for PostgreSQL by running the command below:

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

3. Install PosgreSQL on CentOS 7

Next is installing the PostgreSQL 15 server on your machine:

sudo yum install -y postgresql15-server

4. Initialize the Database

Then, initialize the database:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

5. Start the Database

After that, launch PostgreSQL with the following command:

sudo systemctl start postgresql-15

6. (Optional) Enable PostgreSQL Launch on Reboot

If you want PostgreSQL to launch automatically after a system reboot, use this command:

sudo systemctl enable postgresql-15

How to Install PostgreSQL on CentOS 7 Using the CentOS Repositories

The CentOS 7 repository contains PostgreSQL by default. However, note that it may not offer the latest version. Currently, the repository hosts PostgreSQL version 9.2.24.

1. Access Your Server

First, access your virtual private server via SSH.

2. Install PostgreSQL on CentOS 7

Then, install PostgreSQL with the following command:

sudo yum install postgresql-server

Note that installation might take some time to finish.

3. Initialize the Database

Once the process is done, initialize the database by using the Linux command below:

sudo postgresql-setup initdb

4. Start the Database

Now, it’s time to activate the PostgreSQL server by running the following command:

sudo systemctl start postgresql.service

5. (Optional) Enable PostgreSQL

With the installation complete and initialized, you can configure PostgreSQL to start on every system reboot automatically. To do that, use this command:

sudo systemctl enable postgresql.service

PostgreSQL Basic Setup

In this section, we will go over the basic configuration for PostgreSQL.

Connect to PostgreSQL

In order to start using PostgreSQL, you will need to connect to its prompt. Start by switching to the postgres user:

sudo su postgres

Then, start PostgreSQL:

psql

Here, it’s possible to check the list of all available commands by typing \h. Alternatively, you can use \h followed by a command for which you need more information. To exit the whole environment, use \q.

Change User Password

By default, Linux will create a new postgres user whenever you install PostgreSQL. If you wish to modify the postgres user password, use the following command:

sudo passwd postgres

A prompt to enter the new password twice will appear. Next, switch to the PostgreSQL prompt and finish changing the password for the PostgreSQL postgres user:

su - postgres

If you receive an error, set a correct shell for the user with the command below:

su --shell /bin/bash postgres

Then, perform the same command:

su - postgres

Alternatively, it’s also possible to change the user password from the PostgreSQL prompt. To do this, you’ll need to first connect to the prompt:

sudo -u postgres psql

Then, to change the password, use the below command. Make sure to input your new password to replace NewPassw0rd:

ALTER USER postgres PASSWORD 'NewPassw0rd';

Lastly, restart the PostgreSQL service to enable these changes:

sudo service postgresql restart

Create New User

The easiest way to create a new user is via interactive mode. First, switch to the postgres user:

su - postgres

Now, create a new user by entering the command below and choosing a name and user role:

createuser --interactive

To validate the new user, log in to PostgreSQL:

psql

From here, you can check all currently active users:

\du

Create a Database

Since you now have a user, all there is left to do is create a database with the createdb command.

Keep in mind that PostgreSQL assumes that the role name should be the same as the database name. Therefore, let’s create a database with the same name as our user:

sudo -u postgres createdb newrole

To log in to the newly created database, you need to make a Linux user account under the same name:

sudo adduser newrole

Then, run the following command to connect:

sudo -u newrole psql

You can use \l or \list commands to show all databases. To identify your current database, type \c. In case you want more information about connections, such as the socket or port, use \conninfo.

Delete a Database

You can delete a database using the dropdb command with the following syntax:

dropdb yourdatabasename

Apart from the functions used in this article, PostgreSQL is very similar to other database management systems and has many familiar features:

• Table creation
• Table deletion
• Table updates
• Column addition
• Drop column
• Query table
• Alter commands
• Grant privileges

The syntax for these is similar to most other database management commands. For example, you can list all tables by using the \dt command. To list all roles, run the \du command.

To learn more, we encourage you to read PostgreSQL’s official documentation.

Conclusion

If you need a reliable DBMS, consider PostgreSQL. It’s a powerful and scalable database management system that can suit anyone’s needs, as it offers enterprise-grade features for free.

In this tutorial, we have shown you two methods of how to install PostgreSQL on CentOS 7. We have also covered the basic PostgreSQL server setup process, from user creation to database deletion.

We hope that you found this tutorial useful. In case you have any questions, make sure to check out the official PostgreSQL documentation or leave a comment below.