In order to install PostgreSQL database and do some basic PostgreSQL CRUD operations on CentOS 8, we recommend you to follow the instructions described below.

 

– Install PostgreSQL database:

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

$ sudo dnf makecache

Update CentOS 8 package repository cache.

Then, install PostgreSQL database server using the following command:

$ sudo dnf install postgresql postgresql-server

Confirm the installation, pressing Y and then press Enter

PostgreSQL database server should be installed.

Once the database installed, run the following command to check whether the PostgreSQL database server is working:

$ postgres --version

 

– Initialize PostgreSQL database server:

After installing PostgreSQL database, you must initialize the PostgreSQL database directory. Do that by running the following command:

$ sudo postgresql-setup --initdb

The above command should initialize PostgreSQL database directory.

 

– Manage PostgreSQL database service:

PostgreSQL database service will not be running by default.

$ 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 the PostgreSQL database service started, verify whether it’s running using the command:

$ sudo systemctl STATUS postgres

If you want/need to STOP the PostgreSQL database service, run the command:

$ sudo systemctl stop postgres

If you want/need to RESTART the PostgreSQL database service, run the command:

$ sudo systemctl restart postgres

 

– Start PostgreSQL server on system startup:

In order 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.

If you want to REMOVE PostgreSQL database server from the system startup run the following command:

$ sudo systemctl disable postgresql

 

– Login in as postgres User:

You will need to login as the postgres user to run any PostgreSQL database service. Run the following command:

$ sudo su – postgres

– Create a PostgreSQL database

Use the following command to create a PostgreSQL database (let’s call it hostx):

$ created hostx

Next, you can use the hostx database as follows:

$ psql hostx

You can run a SQL query and test whether it works or not:

SELECT CURRENT_DATE;

Once you’re done using the database, exit out of the database using the following command:

\q

 

– Create PostgreSQL database:

Now we will create create a new table users in our hostx 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.

Then, list all the available tables in your database as running the following command:

# \d

See the schema of the users table using:

# \d users

 

– Insert data into tables

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, 'elon11', '9645');
INSERT INTO users VALUES(1, 'bob45', '8513');

We’ve just inserted 3 new rows into the users table.

 

– Read data from tables

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

SELECT * FROM users;

Observe the data inserted earlier is printed on the console in tabular format.

 

– Remove data from tables

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

DELETE FROM users WHERE name='elon11';

Observe that that row is not in the users table anymore.

SELECT * FROM users;

 

– Update data from tables

Let’s say, you want to update the id of ‘bob45’ of the users table from 1 to 2. To do that, run the following SQL statement:

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

You can see the id of ‘bob45’ is updated from 1 to 2.

 

– Remove tables

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

DROP TABLE users;

The table should be removed.

\d

 

– Delete databases

To delete the database hostx, run the following command as postgres user:

$ dropdb hostx

The database hostx should be removed.

 

And this is how you install PostgreSQL and do several basic PostgreSQL CRUD operations on CentOS 8.