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.
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
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:
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.
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
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
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:
systemctl enable postgresqlsystemctl start postgresql
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:
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:
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
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.
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:
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.