‍

PostgreSQL is a powerful and versatile open-source database management system that is widely used for web, mobile, and analytic applications. In this step-by-step guide, we will walk you through the process of installing PostgreSQL on AlmaLinux 9. By following these instructions, you will be able to set up PostgreSQL, configure basic authentication, create users and databases, and perform basic operations such as creating tables, inserting and retrieving data, and updating and deleting records.

Prerequisites

Before we begin, make sure you have the following prerequisites:

• An AlmaLinux 9 server
• A non-root user with sudo or root administrator privileges

Adding PostgreSQL Repository

To get the latest version of PostgreSQL, we will add the PostgreSQL repository to our AlmaLinux 9 server. This will allow us to install and update PostgreSQL packages from the repository.

1. Open a terminal and run the following command to add the PostgreSQL repository:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. After adding the repository, run the following command to refresh the package cache:

sudo dnf makecache -y

Installing PostgreSQL

With the PostgreSQL repository added, we can now proceed to install PostgreSQL on AlmaLinux 9.

1. Run the following command to install PostgreSQL server packages:

sudo dnf install postgresql15 postgresql15-server postgresql15-contrib

2. After the installation is complete, initialize the PostgreSQL data and configurations by running the following command:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

3. Start the PostgreSQL service and enable it to run automatically on system startup with the following commands:

sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

4. Verify the status of the PostgreSQL service by running the following command:

sudo systemctl status postgresql-15

If the service is running, you should see an output indicating that the service is active.

Basic PostgreSQL Authentication

Now that PostgreSQL is installed, let’s take a look at the basic authentication methods and how to configure them.

By default, PostgreSQL uses the “peer” authentication method for local connections, which relies on Unix socket permissions. This means that if you connect to PostgreSQL locally using the psql command, it will use the “peer” authentication method.

To connect to PostgreSQL using a different authentication method, such as password authentication, you need to specify the host and user explicitly. For example:

sudo -u postgres psql -h localhost -U postgres

If you try to connect without specifying the host and user, you will receive an error. This is because the default password for the “postgres” user is not set.

To set a password for the “postgres” user, follow these steps:

1. Log in to the PostgreSQL server as the “postgres” user:

sudo -u postgres psql

2. Run the following query to set a new password for the “postgres” user:

ALTER ROLE postgres WITH PASSWORD 'yourpassword';

Replace ‘NewP4ssw0rd’ with your desired password.

3. Type ‘q’ or ‘quit’ to exit the PostgreSQL server.

Now, when you connect to PostgreSQL using the “postgres” user, you will be prompted for the password you set.

Creating User and Database on PostgreSQL

Now that we have set up basic authentication, let’s create a new user and database on PostgreSQL.

1. Log in to the PostgreSQL server as the “postgres” user:

sudo -u postgres psql

2. Run the following query to create a new user called ‘appuser’ with the password ‘p4ssAppuser’:

CREATE USER appuser LOG IN CREATE DBPASSWORD 'p4ssAppuser';

Replace ‘p4ssAppuser’ with your desired password.

3. To verify that the user has been created, run the following command:

du

You should see the new user ‘appuser’ in the list.

4. Next, connect to the PostgreSQL server using the new user and create a new database called ‘appdb’:

sudo -u postgres psql -h localhost -U appuser -d postgres

5. Run the following query to create the ‘appdb’ database with the ‘appuser’ as the owner:

CREATE DATABASE appdb OWNER appuser;

6. To verify that the database has been created, run the following command:

l

You should see the ‘appdb’ database in the list.

7. Switch to the ‘appdb’ database using the following command:

connect appdb;

You should see that you are now connected to the ‘appdb’ database.

8. Type ‘q’ or ‘quit’ to exit the PostgreSQL server.

Congratulations! You have successfully created a new user and database on PostgreSQL.

Creating Table

Now that we have a database, let’s create a table inside it.

1. Log in to the PostgreSQL server as the ‘appuser’:

sudo -u postgres psql -h localhost -U appuser -d appdb

2. Run the following query to create a new table called ‘teachers’ with columns for ‘id’, ‘name’, ‘age’, ‘address’, and ‘salary’:

CREATE TABLE teachers (
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   salary         REAL
);

3. To verify that the table has been created, run the following command:

dt

You should see the ‘teachers’ table in the list.

4. To see the structure of the ‘teachers’ table, run the following command:

d+ teachers

You should see the columns and their data types.

Congratulations! You have successfully created a table in PostgreSQL.

Inserting Data into Table

Now that we have a table, let’s insert some data into it.

1. Run the following queries to insert new data into the ‘teachers’ table:

INSERT INTO teachers (id,name,age,address,salary) VALUES (1, 'Lea', 25, 'Spain', 20000.00);
INSERT INTO teachers (id,name,age,address,salary) VALUES (2, 'Lin', 35, 'Italy', 30000.00);
INSERT INTO teachers (id,name,age,address,salary) VALUES (3, 'Lisa', 27, 'Germany', 40000.00);

2. To retrieve all the data from the ‘teachers’ table, run the following query:

SELECT * FROM teachers;

3. You can also retrieve data from specific columns. For example, to retrieve only the ‘name’ and ‘salary’ columns, run the following query:

SELECT name,salary FROM teachers;

Congratulations! You have successfully inserted and retrieved data from the PostgreSQL table.

Updating Data on PostgreSQL

If you need to update the data in the table, you can use the UPDATE statement.

1. Run the following query to update the ‘address’ column for the record where the ‘name’ is ‘Lisa’:

UPDATE teachers SET address = 'France' WHERE name = 'Lisa';

2. To verify that the data has been updated, run the following query:

SELECT * FROM teachers WHERE name = 'Lisa';

You should see that the ‘address’ column has been changed to ‘France’ for the record with the name ‘Lisa’.

Congratulations! You have successfully updated data in the PostgreSQL table.

Deleting Database and User

If you no longer need a database or user, you can delete them from PostgreSQL.

To delete the ‘appdb’ database:

1. Switch to the ‘postgres’ database:

connect postgres;

2. Run the following query to delete the ‘appdb’ database:

DROP DATABASE appdb;

3. To verify that the database has been deleted, run the following command:

l

The ‘appdb’ database should no longer be listed.

To delete the ‘appuser’ user:

1. Log in as the ‘postgres’ user:

sudo -u postgres psql

2. Run the following query to delete the ‘appuser’:

DROP USER appuser;

3. To verify that the user has been deleted, run the following command:

du

The ‘appuser’ should no longer be listed.

Congratulations! You have successfully deleted the database and user from PostgreSQL.

Conclusion

In this step-by-step guide, we have walked you through the process of installing PostgreSQL on AlmaLinux 9. We covered the steps to add the PostgreSQL repository, install PostgreSQL, configure basic authentication, create users and databases, create tables, insert and retrieve data, update and delete records. By following these instructions, you should now have a working PostgreSQL installation on your AlmaLinux 9 server.

PostgreSQL is a powerful database management system that provides reliability, flexibility, and performance. It is widely used by prominent brands such as Reddit, Skype, Instagram, and more. If you are looking for a reliable hosting provider for your PostgreSQL database, consider Shape.host. They offer Cloud VPS services with excellent performance and support.

Happy PostgreSQL installation and database management!