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
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 postgresAs 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 postgresIf you need to restart the PostgreSQL database service for some reason, run the following command:
$ sudo systemctl restart postgresStarting 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 postgresqlLogin 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:
# \dAs 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.