How to Install PostgreSQL on Rocky Linux 9

Introduction

PostgreSQL is an open-source Relational Database Management System (RDBMS) that supports complex data types, ACID-compliant transactions. The RDBMS is widely used in web applications, data warehousing, and geospatial data management. PostgreSQL supports Structured Query Language (SQL) for storing and retrieving data in tables.

This article explains how to install PostgreSQL on Rocky Linux 9 and how to enable authentication for database users.

Prerequisites

Before you begin:

• Deploy a Rocky Linux 9 Cloud Compute instance on Vultr.
• Access the instance using SSH.
• Create a non-root user with sudo privileges and switch to the user.

Install PostgreSQL

PostgreSQL is available in the default package repositories on Rocky Linux 9. Follow the steps below to install the latest PostgreSQL database server package using the dnf package manager.

1. Update the server's package information index.

   CONSOLE

   Copy

   $ sudo dnf update -y

   Explain Code

   Powered by Vultr AgentBeta

2. List all available PostgreSQL packages.

   CONSOLE

   Copy

   $ sudo dnf list postgresql-server

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   Available Packages
   postgresql-server.x86_64                                            13.14-1.el9_3                                             appstream

   Based on the above output, PostgreSQL 13 is the latest version available in the Rocky Linux 9 repository sources.

3. Install the PostgreSQL database server package.

   CONSOLE

   Copy

   $ sudo dnf install -y postgresql-server postgresql

   Explain Code

   Powered by Vultr AgentBeta

Manage the PostgreSQL System Service

PostgreSQL uses the postgresql system service to manage the database server runtime and processes on Rocky Linux 9. Follow the steps below to enable the PostgreSQL database server to automatically start at system boot and verify the service processes.

1. Enable the PostgreSQL service to automatically start at boot.

   CONSOLE

   Copy

   $ sudo systemctl enable postgresql

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

2. Initialize the PostgreSQL database server to enable the default postgres user.

   CONSOLE

   Copy

   $ sudo postgresql-setup --initdb

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   * Initializing database in '/var/lib/pgsql/data'
   * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

3. Start the PostgreSQL service.

   CONSOLE

   Copy

   $ sudo systemctl start postgresql

   Explain Code

   Powered by Vultr AgentBeta

4. View the PostgreSQL service status and verify that it's active.

   CONSOLE

   Copy

   $ sudo systemctl status postgresql

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   ● postgresql.service - PostgreSQL database server
        Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
        Active: active (running) since Sun 2024-08-18 13:17:29 UTC; 10s ago
       Process: 13643 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
      Main PID: 13645 (postmaster)
         Tasks: 8 (limit: 4632)
        Memory: 16.5M
           CPU: 42ms
        CGroup: /system.slice/postgresql.service
                ├─13645 /usr/bin/postmaster -D /var/lib/pgsql/data
                ├─13646 "postgres: logger "
                ├─13648 "postgres: checkpointer "
                ├─13649 "postgres: background writer "
                ├─13650 "postgres: walwriter "
                ├─13651 "postgres: autovacuum launcher "
                ├─13652 "postgres: stats collector "
                └─13653 "postgres: logical replication launcher "

Secure the PostgreSQL Server

PostgreSQL runs with a default postgres administrative database user account that requires no password. Follow the steps below to set up password authentication for the postgres user and create standard users on PostgreSQL.

1. Log in to the PostgreSQL database server as the postgres user.

   CONSOLE

   Copy

   $ sudo -u postgres psql

   Explain Code

   Powered by Vultr AgentBeta

2. Modify the postgres user to use a new strong password. Replace strong_password with your desired password.

   SQL

   Copy

   postgres=# ALTER USER postgres WITH PASSWORD 'strong_password';

   Explain Code

   Powered by Vultr AgentBeta

3. Create a sample db_admin database user and set a strong password for the user.

   SQL

   Copy

   postgres=# CREATE USER db_admin WITH PASSWORD 'strong-password';

   Explain Code

   Powered by Vultr AgentBeta

4. Exit the PostgreSQL console

   SQL

   Copy

   postgres=# \q

   Explain Code

   Powered by Vultr AgentBeta

5. Back up the main pg_hba.conf PostgreSQL configuration file.

   CONSOLE

   Copy

   $ sudo cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bak

   Explain Code

   Powered by Vultr AgentBeta

6. Open the pg_hba.conf file using a text editor like Nano.

   CONSOLE

   Copy

   $ sudo nano /var/lib/pgsql/data/pg_hba.conf

   Explain Code

   Powered by Vultr AgentBeta

7. Find the following configuration section within the file.

   INI

   Copy

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     peer

   Explain Code

   Powered by Vultr AgentBeta

8. Change the peer value to md5 to enable password authentication.

   INI

   Copy

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     md5

   Explain Code

   Powered by Vultr AgentBeta

   Save and close the file.

9. Restart the PostgreSQL service to apply the configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart postgresql

   Explain Code

   Powered by Vultr AgentBeta

Access PostgreSQL

You can access the PostgreSQL database server console using the psql client utility that ships with the database server package. Follow the steps below to access the PostgreSQL database server console and perform database operations.

1. Create a sample example-vultr database and assign the database to the db_admin user.

   CONSOLE

   Copy

   $ sudo -u postgres createdb example-vultr -O db_admin

   Explain Code

   Powered by Vultr AgentBeta

   Enter the postgres user password you set earlier when prompted.

2. Log in to the PostgreSQL database server as the db_admin user to test access to the example-vultr database.

   CONSOLE

   Copy

   $ sudo -u postgres psql -U db_admin -d example-vultr

   Explain Code

   Powered by Vultr AgentBeta

   Enter the db_admin user password you created earlier when prompted.

3. Create a sample shop table.

   SQL

   Copy

   example-vultr=> CREATE TABLE shop (
       id SERIAL PRIMARY KEY,
       name VARCHAR(50),
       location VARCHAR(100)
   );

   Explain Code

   Powered by Vultr AgentBeta

4. Insert sample data into the shop table.

   SQL

   Copy

   example-vultr=> INSERT INTO shop (name, location) VALUES ('The Gadget Store', '123 Tech Ave'), ('Book Haven', '456 Knowledge St');

   Explain Code

   Powered by Vultr AgentBeta

5. Query the shop table to view all available sample records.

   SQL

   Copy

   example-vultr=> SELECT * FROM shop;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

    id |       name       |     location     
   ----+------------------+------------------
     1 | The Gadget Store | 123 Tech Ave
     2 | Book Haven       | 456 Knowledge St
   (2 rows)

6. Exit the PostgreSQL database server console.

   SQL

   Copy

   example-vultr=> \q

   Explain Code

   Powered by Vultr AgentBeta

Conclusion

You have installed PostgreSQL on Rocky Linux 9 and enabled password authentication to allow users to securely access the database console. You can integrate PostgreSQL with graphical management tools such as PGAdmin and install specific application modules to use the database server with other applications. For more information and configuration options, visit the PostgreSQL documentation.