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:

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.

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.