LINUX

How to Install and Configure PostgreSQL on CentOS

0

PostgreSQL is a robust and highly scalable database system that runs on both Linux and Windows machines. This enterprise-level software offers excellent reliability and data integrity for creating applications.

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.

su

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:

sudo yum check-update

Output:

Update-packages-on-CentOS

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

sudo yum update

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

sudo reboot

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.

Check-default-PSQL-version
dnf module list 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.

sudo dnf module enable postgresql:13
Enable-PSQL-version-13

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.

sudo dnf install postgresql-server
Install-PSQL-Server-on-CentOS-4

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

postgresql-setup --initdb
Initialize-database-in-PSQL

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

systemctl enable postgresqlsystemctl start postgresql
Activate-PSQL-on-system-boot

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

systemctl status postgresql

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:

passwd postgres

Output:

Change-password-PSQL

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:

su - postgres

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:

createuser --interactive

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.

sudo -u postgres createuser --interactive
Create-a-new-role

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.

man createuser

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.

sudo adduser test

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

sudo -i -u test

Creating New Databases

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

createdb databasename

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.

CREATE TABLE table_name (column_name1 col_type (field_length),column_name2 col_type (field_length),column_name3 col_type (field_length));

…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:

CREATE TABLE hotel (hotel_id serial PRIMARY KEY,star varchar (50) NOT NULL,paint varchar (25) NOT NULL,location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),date date);

..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:

INSERT INTO table (column_name1, column_name2, column_name3) VALUES ('value1', 'value2', 'value3');

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

INSERT INTO hotel (hotel, star, location, install_date) VALUES ('Plaza', 'Five', 'northwest', '2018-08-16')

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.

admin

Score Up to $100 Off OnePlus 9-Series Smartphones

Previous article

The 6 Best Ways to Create Forms Online

Next article

You may also like

Comments

Leave a reply

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

More in LINUX

Login/Sign up