Introduction

PostgreSQL is a powerful open-source relational database management system (RDBMS). You can use PostgreSQL to store, manage, and retrieve data for various applications. PostgreSQL offers high performance, reliability, and advanced features, making it a popular choice for web applications, data warehousing, and complex queries. It is cross-platform, running on multiple operating systems like Windows, macOS, and Linux. PostgreSQL integrates well with other technologies such as Python, Java, and Ruby, providing a comprehensive solution for data management.

This guide shows you how to install PostgreSQL on a Rocky Linux 9 server.

Prerequisites

Before you begin:

• Deploy a Rocky Linux 9 server. We recommend a Digital Ocean VPS server.
• Create a non-root user account with sudo privileges. Read our guide on How to Create a Non-Root Sudo User on Rocky Linux 9

Install PostgreSQL

In this section, you will update your package list and install PostgreSQL. Keeping your package list updated ensures you get the latest available packages.

1. SSH to your server and update your package list.

   CONSOLE

   $ sudo dnf update -y
   

   This command refreshes your package list to get the most recent version of PostgreSQL.

2. Install PostgreSQL.

   CONSOLE

   $ sudo dnf install postgresql-server postgresql-contrib -y
   

   This command downloads and installs PostgreSQL on your system.

3. Initialize the PostgreSQL database.

   CONSOLE

   $ sudo postgresql-setup --initdb
   

   This command initializes the PostgreSQL database.

   Output:

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

Start and Manage PostgreSQL Service

In this section, you will start the PostgreSQL service and manage it using systemctl. This includes enabling PostgreSQL to start at boot, stopping, and restarting PostgreSQL.

1. Start the PostgreSQL service.

   CONSOLE

   $ sudo systemctl start postgresql
   

2. Enable PostgreSQL to start at boot.

   CONSOLE

   $ sudo systemctl enable postgresql
   

3. Stop PostgreSQL service.

   CONSOLE

   $ sudo systemctl stop postgresql
   

4. Restart PostgreSQL service.

   CONSOLE

   $ sudo systemctl restart postgresql
   

Review the PostgreSQL Directory Structure

In this section, you will learn about the PostgreSQL package directory structure and the location of various configuration files.

1. PostgreSQL configuration file.

   /var/lib/pgsql/data/postgresql.conf
   

2. PostgreSQL data directory.

   /var/lib/pgsql/data/
   

3. PostgreSQL log files.

   /var/lib/pgsql/data/pg_log/
   

Create a Sample Database and Table

In this section, you will create a sample database named company and a table named products with three fields: product_id, product_name, and retail_price. You will also insert records into the table and query the table to confirm the records.

1. Log in to the PostgreSQL shell.

   CONSOLE

   $ sudo -u postgres psql
   

   This command logs you in to the PostgreSQL shell as the postgres user.

2. Create a sample company database.

   postgresql

   postgres=# CREATE DATABASE company;
   

   Output:

   CREATE DATABASE
   

3. Connect to the sample database.

   postgresql

   postgres=# \c company;
   

   Output:

   You are now connected to database "company" as user "postgres".
   

4. Create a sample table.

   postgresql

   company=# CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(255) NOT NULL,
       retail_price DECIMAL(10, 2) NOT NULL
   );
   

   Output:

   CREATE TABLE
   

5. Insert records into the products table.

   postgresql

   company=# INSERT INTO products (product_name, retail_price) VALUES
   ('KEY HOLDER', 1.99),
   ('RED BIRO PEN', 0.99),
   ('3LTR COOKING OIL', 5.99);
   

   Output:

   postgresql

   INSERT 0 3
   

6. Query the table to confirm the records.

   postgresql

   company=# SELECT 
       product_id,
       product_name,
       retail_price
   FROM products;
   

   You should see the records you inserted in the table.

   Output:

   postgresql

    product_id |   product_name   | retail_price
   ------------+------------------+--------------
             1 | KEY HOLDER       |         1.99
             2 | RED BIRO PEN     |         0.99
             3 | 3LTR COOKING OIL |         5.99
   (3 rows)
   

Conclusion

You have installed PostgreSQL on your Rocky Linux 9 server. This guide covered updating your package list, installing PostgreSQL, managing the PostgreSQL service, creating a sample database and table, and understanding the PostgreSQL package directory structure. PostgreSQL is now ready to store and manage your data. You can start configuring your database, creating tables, and performing data operations. Consider setting up user accounts, securing your PostgreSQL installation, and optimizing performance to enhance your database capabilities.

Further Readings

• How to Back Up PostgreSQL Database