‍ PostgreSQL is a powerful, open-source object-relational database system that has gained a strong reputation for reliability, feature robustness, and performance over its 30 years of active development. In this article, we will guide you through the step-by-step process of installing PostgreSQL on Rocky Linux 9, ensuring that you have a solid foundation for your database management needs.

Updating the System

Before we begin the installation process, it’s essential to update the system to ensure that we have the latest packages and security patches. To do this, open your terminal and run the following commands:

dnf update

Installing the PostgreSQL Repository

Next, we need to add the official PostgreSQL Repository for Rocky Linux 9. This repository will provide us with the necessary packages to install PostgreSQL. Execute the following command in your terminal:

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

Installing the PostgreSQL Server

Once the repository is added, we can proceed with installing the PostgreSQL server. At the time of writing this article, version 14.4 is the latest stable release. However, it’s always recommended to check the official PostgreSQL website for the most up-to-date version. To install the latest version, run the following command:

dnf install -y postgresql14-server

After executing the command, you will see the output displaying the packages that are being installed.

Output:

[root@vps ~]# dnf install -y postgresql14-server
Last metadata expiration check: 0:00:36 ago on Thu 28 Jul 2022 04:52:12 PM UTC.
Dependencies resolved.
=================================================================================================================================
Package Architecture Version Repository Size
=================================================================================================================================
Installing:
postgresql14-server x86_64 14.4-1PGDG.rhel9 pgdg14 5.8 M
Installing dependencies:
libicu x86_64 67.1-9.el9 baseos 9.6 M
lz4 x86_64 1.9.3-5.el9 baseos 58 k
postgresql14 x86_64 14.4-1PGDG.rhel9 pgdg14 1.4 M
postgresql14-libs x86_64 14.4-1PGDG.rhel9 pgdg14 281 k

Transaction Summary
=================================================================================================================================
Install 5 Packages

Creating a New PostgreSQL Database Cluster

Once the installation is complete, we need to initialize a database storage area on disk. To do this, use the following command:

/usr/pgsql-14/bin/postgresql-14-setup initdb

This command will set up the necessary directories and files for PostgreSQL to function correctly.

Starting and Enabling the PostgreSQL Service

With the PostgreSQL server installed and the database cluster initialized, we can now start and enable the PostgreSQL service. Use the following commands to achieve this:

systemctl start postgresql-14
systemctl enable postgresql-14

The first command starts the PostgreSQL service, while the second command enables it to start automatically at system boot.

To verify that the PostgreSQL service is running correctly, use the following command:

systemctl status postgresql-14

If everything is set up properly, you will see an output similar to the following:

Output:

[root@vps ~]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2022-07-28 16:53:54 UTC; 11s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 28389 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 28394 (postmaster)
Tasks: 8 (limit: 11129)
Memory: 16.4M
CPU: 75ms
CGroup: /system.slice/postgresql-14.service
├─28394 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─28395 "postgres: logger "
├─28397 "postgres: checkpointer "
├─28398 "postgres: background writer "
├─28399 "postgres: walwriter "
├─28400 "postgres: autovacuum launcher "
├─28401 "postgres: stats collector "
└─28402 "postgres: logical replication launcher "

PostgreSQL Roles

To manage PostgreSQL effectively, it’s crucial to understand the concept of roles. Roles can be thought of as users or groups with specific privileges and permissions within the database system. By default, PostgreSQL creates a superuser role called “postgres” during installation.

To access the PostgreSQL prompt and interact with the database system, we’ll switch to the “postgres” account. Open your terminal and run the following command:

sudo -i -u postgres

This command allows you to switch to the “postgres” user and grants you access to the PostgreSQL prompt using the “psql” utility.

Output:

[root@vps ~]# sudo -i -u postgres
[postgres@vps ~]$ psql
psql (14.6)
Type "help" for help.

To exit the PostgreSQL shell, simply type q.

Creating a PostgreSQL Role

Now that we are in the PostgreSQL prompt, we can create a new role. Roles can be used to control access to databases and define specific privileges for individual users or groups.

To create a new role, use the following command:

createuser --interactive

This command will prompt you to enter the name of the role you want to add. For example, let’s create a role named “Adam.”

Output:

[postgres@vps ~]$ createuser --interactive
Enter name of role to add: Adam
Shall the new role be a superuser? (y/n) y

Creating a PostgreSQL Database

With the role created, we can now proceed to create a database. A database is a container for storing data in a structured manner. To create a database, use the following command:

createdb db_name

Replace “db_name” with the desired name for your database.

Output:

[postgres@vps ~]$ createdb my_db

Opening a Postgres Prompt with the New Role

To manage roles and databases efficiently, it’s a good practice to create a separate user for each role. By doing so, you can ensure that each user has the appropriate permissions and can perform their tasks without interfering with other roles.

To add a new user and grant them access to the PostgreSQL prompt, follow these steps:

1. Switch back to the root user account by exiting the current user mode with the following command:

exit

2. Run the adduser command to add the new user to the system. For example, to add a user named “Adam,” execute the following command:

adduser Adam

3. Once the user is added, switch over to the newly created user account:

sudo -i -u Adam

4. Enter the PostgreSQL prompt with the new user and specify the database you want to connect to. For example, to connect to the “my_db” database, run the following command:

psql -d my_db

Output:

[root@vps ~]# sudo -i -u Adam
[Adam@vps ~]$ psql -d my_db
psql (14.6)
Type "help" for help.

Once you are logged in as the new user, you can check your current connection information by executing the command conninfo. This command provides details about the database you are connected to, the user you are logged in as, and the connection settings.

Output:

my_db=# \conninfo
You are connected to database "my_db" as user "Adam" via socket in "/var/run/postgresql" at port "5432".
my_db=#

Congratulations! You have successfully installed PostgreSQL on Rocky Linux 9. You are now ready to take advantage of its powerful features and build robust database applications.

We hope this guide has been helpful in getting you started with PostgreSQL. If you have any further questions or need assistance, feel free to reach out to our team at Shape.host. We provide reliable and scalable Cloud VPS hosting solutions, ensuring that your PostgreSQL databases are secure and performant. Visit us at Shape.host to learn more about our services.

Happy database management!

Christian Wells