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
   

2. List all available PostgreSQL packages.

   console Copy

   $ sudo dnf list postgresql-server
   

   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
   

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
   

   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
   

   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
   

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

   console Copy

   $ sudo systemctl status postgresql
   

   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
   

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';
   

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';
   

4. Exit the PostgreSQL console

   sql Copy

   postgres=# \q
   

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
   

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

   console Copy

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

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
   

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
   

   Save and close the file.

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

   console Copy

   $ sudo systemctl restart postgresql
   

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
   

   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
   

   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)
   );
   

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');
   

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

   sql Copy

   example-vultr=> SELECT * FROM shop;
   

   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
   

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.