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.
Recent Comments