PostgreSQL is a versatile software that can handle a wide array of workloads ranging from single machines to large data warehouses. It offers features like transactions with ACID properties, updatable views, foreign keys, and cross-platform integrations.

How to Install PostgreSQL in CentOS

CentOS works a little differently and you won’t have root access right after booting your system. To log in as the root user, use the su command.

The system will log you in as the superuser with all the administrative permissions.

Step 1: Update and Upgrade Pre-Installed Packages

As a part of the next step, you need to update and upgrade your existing packages within CentOS. To do so, run the following command in the terminal:

Output:

Type the following command in the terminal to upgrade all the listed packages:

Once you have upgraded the packages, you need to reboot your system. Use the following command:

The system is ready with the newly updated packages. It’s time to install PostgreSQL on your CentOS machine.

Step 2: Check for Any Existing Default Versions

Before any new installations, it is important to check the available default versions of PostgreSQL.

By default, in case you proceed with the installation, the install commands will install PostgreSQL version 10.

Since version 13 is available at the time of writing this guide, we will restrict the default installation and use the following command to enable the latest version of PostgreSQL.

Step 3: Install the PostgreSQL Server Package

After changing the default version using the aforementioned command, it’s time to install the PostgreSQL server and client packages.

Once you have installed the server, you need to initialize the PostgreSQL database. To initialize, run the following command:

Post initialization, start the PostgreSQL service. To enable it automatically at system boot, use the following commands:

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

If the output displays “Active”, then the service is up and running.

Step 4: Configuring PostgreSQL Database

Post-installation, it’s best to configure your PostgreSQL database. To secure your account, create a password for the postgres system user account using the passwd utility:

Output:

The system will ask you to enter the password two times. Make sure you enter the correct password both times.

​​​After setting the user account password, log in to the postgres account using su:

Step 5: Creating a New User Role

Since the use of PostgreSQL is not limited to just one user, you have the liberty to add a few more users to the existing list.

Use the createuser command along with the –interactive flag to add more users. Also, specify the access type you want to set for them. If you are logged in with the postgres account, simply type the following:

If you are not keen on moving in and out of the system account too often, there is always an option to add users with the sudo command.

In both scenarios, the system will ask you for the role name to be added, along with the access type for this newly created user. Choose between y and n for the superuser role type.

To check out some additional flags, you can always refer to the createuser command man page.

How to Use PostgreSQL to Create New Databases

Now that you’ve configured PostgreSQL on your machine, it’s time to create a new database and add new tables.

Access the PostgreSQL Prompt With the New Role

Once you have added a user, obviously you would want to start using PostgreSQL to your advantage. But before you can do that, you would need to create a user with the same name as your PostgreSQL role and database.

If you don’t have such a user ID available, use the adduser command to create a new user named test.

Log in as the new user using the -i and -u flags.

Creating New Databases

To create a new database within PostgreSQL, use the following command:

Note that you need to be logged in as the postgres system user to issue administrative commands.

Creating New Tables Within the Database

Let’s create a new table to store some data. The basic syntax to add fields in the new table is quite simple.

…where table_name is the desired name set by the user, column_name1, column_name2, etc. are the column names, col_type is the type of column, and field_length is the size of the data structures to make the values more robust.

For example, you can create a table hotel with the following columns:

..where the table name is hotel, and columns are hotel_id, star, paint, location, and date with their respective lengths and column constraints.

Adding Values to the Database

Once your table structure is ready, you can add some data to an existing table. Use the INSERT INTO statement to add data in the following format:

For example, add a row of data to the hotel table that you created above.

Configuring PostgreSQL on CentOS

To configure PostgreSQL, it’s best to install it in the right manner. With the proper configurations in place, you can easily install PostgreSQL and use it within your system efficiently.

Apart from CentOS, you can also set up PostgreSQL to work with other Linux distributions, Ubuntu, for example.