CentOS PostgreSQL

Installing PostgreSQL on CentOS 8

In this article, I am going to show you how to install PostgreSQL database and how to do basic PostgreSQL CRUD operations on CentOS 8. So, let’s get started.

Installing PostgreSQL Database:

PostgreSQL is available in the official package repository of CentOS 8. So, you can easily install it using DNF or YUM package manager.

First, update the CentOS 8 package repository cache with the following command:

$ sudo dnf makecache

The CentOS 8 package repository cache should be updated.

Now, install PostgreSQL database server with the following command:

$ sudo dnf install postgresql postgresql-server

To confirm the installation, press Y and then press <Enter>.

PostgreSQL database server should be installed.

Once PostgreSQL database server is installed, run the following command to verify whether the PostgreSQL database server is working.

$ postgres --version

Initializing PostgreSQL Database Server:

Once PostgreSQL database server is installed, you must initialize the PostgreSQL database directory.

You can initialize the PostgreSQL database directory with the following command:

$ sudo postgresql-setup --initdb

PostgreSQL database directory should be initialized.

Managing PostgreSQL Database Service:

PostgreSQL database service will not be running by default as you can see in the screenshot below.

$ sudo systemctl STATUS postgres

Once the PostgreSQL database directory is initialized, you have to start the PostgreSQL service manually as follows:

$ sudo systemctl START postgresql

Once you start PostgreSQL database service, you can verify whether it’s running as follows:

$ sudo systemctl STATUS postgres

As you can see, the PostgreSQL database service is running.

If you need to stop the PostgreSQL database service for some reason, run the following command:

$ sudo systemctl stop postgres

If you need to restart the PostgreSQL database service for some reason, run the following command:

$ sudo systemctl restart postgres

Starting PostgreSQL Server on System Startup:

If you don’t want to start PostgreSQL database server manually every time you boot your CentOS 8 machine, you must add it to the system startup of CentOS 8.

To add PostgreSQL database server to the system startup, run the following command:

$ sudo systemctl enable postgresql

PostgreSQL database server should be added to the system startup.

You can also remove PostgreSQL database server from the system startup as follows:

$ sudo systemctl disable postgresql

Login in as posgres User:

In order to run any PostgreSQL database service, you will have to login as the postgres user.

You can login as the postgres user as follows:

$ sudo su – postgres

Creating a PostgreSQL Database:

You can create a PostgreSQL database (let’s call it linuxhint) with the following command:

$ created linuxhint

Now, you can use the linuxhint database as follows:

$ psql linuxhint

Let’s run a SQL query and test whether it works or not.

SELECT CURRENT_DATE;

It works.

Once you’re done using the database, exit out of the database as follows:

\q

Creating PostgreSQL Database:

Now, let’s create a new table users in our linuxhint database.

Here is the SQL statement to create the table users. Copy and paste the SQL statements in the PostgreSQL shell to create a new table users.

CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
pin VARCHAR(4) NOT NULL
);

The users table should be created.

You can list all the available tables in your database as follows:

# \d

As you can see, the users table is in the list.

You can also see the schema of the users table as follows.

# \d users

Inserting Data into Tables:

Now, let’s insert some new rows into the users table.

To insert new rows into the users table, you can run the following INSERT SQL statements.

INSERT INTO users VALUES(1, 'alex25', '2596');
INSERT INTO users VALUES(2, 'lisa11', '9645');
INSERT INTO users VALUES(1, 'mira45', '8513');

I’ve inserted 3 new rows into the users table.

Reading Data from Tables:

Now, to select all the rows from the users table and print them on the console, run the following SQL statement.

SELECT * FROM  users;

As you can see, the data I’ve inserted earlier is printed on the console in tabular format.

Removing Data from Tables:

To delete a row from the users table with the name ‘lisa11’ (let’s say), run the following SQL statement:

DELETE FROM users WHERE name='lisa11';

As you can see, that row is not in the users table anymore.

SELECT * FROM users;

Updating Data from Tables:

Let’s say, you want to update the id of ‘mira45’ of the users table from 1 to 2.

To do that, run the following SQL statement:

UPDATE users SET id=2 WHERE name='mira45';

As you can see, the id of ‘mira45’ is updated from 1 to 2.

Removing Tables:

To remove the table users, run the following SQL statement:

DROP TABLE users;

The table should be removed.

\d

Deleting Databases:

To delete the database linuxhint, run the following command as postgres user.

$ dropdb linuxhint

The database linuxhint should be removed.

So, this is how you install PostgreSQL and do basic PostgreSQL CRUD operations on CentOS 8. Thanks for reading this article.

About the author

Shahriar Shovon

Shahriar Shovon

Freelancer & Linux System Administrator. Also loves Web API development with Node.js and JavaScript. I was born in Bangladesh. I am currently studying Electronics and Communication Engineering at Khulna University of Engineering & Technology (KUET), one of the demanding public engineering universities of Bangladesh.