How to install PostgreSQL on CentOS 9 + create roles and databases

PostgreSQL is an open-source relational database management system (RDBMS) known for its capability to handle large amounts of data, making it a popular choice for large-scale applications.

It’s also compatible with various data types, programming languages, web stacks, and operating systems, including popular Linux distributions like CentOS.

In this tutorial, we will explain how to install PostgreSQL on CentOS 9. You will also learn basic postgres statements and commands for managing roles and databases.

Prerequisites for installing PostgreSQL

Before installing PostgreSQL, make sure your server meets the recommended hardware requirements:

• 1 GHz processor.
• At least 2 GB of RAM.
• Minimum 512 MB of storage space. 

Your system must also run CentOS 9 since the installation steps might differ otherwise.

If you don’t have a server, we recommend purchasing a Hostinger CentOS VPS hosting plan. The KVM 1 plan, starting at ₱299.00/month, provides 1 vCPU core, 4 GB RAM, and 50 GB of storage, which is more than suitable for hosting PostgreSQL.

Plus, you can easily install various Linux operating systems on Hostinger VPS without any commands – simply select an OS template. We actively update these templates to add support for the latest distros, including CentOS 9.

Hostinger VPS plans also streamline your PostgreSQL database management tasks. Our free weekly automatic backup and built-in malware scanner will help you maintain data integrity without having to spend any time on it.

If you need help managing your server, ask Kodee – our AI assistant. Just with a few simple prompts, you’ll get guidance on anything you want to do, commands included.

How to install and create a PostgreSQL database

Here are the steps to install PostgreSQL on CentOS. Before proceeding, connect to your server using an SSH client like PuTTY, Terminal, or Hostinger’s Browser terminal.

1. Download and install PostgreSQL

You can install the PostgreSQL package in two ways – via the official or local repository. In this tutorial, we will use the official repo since it updates more quickly than the local one. Here are the steps:

1. Add the PostgreSQL repository to your system using the following command:

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

2. Disable your system’s built-in PostgreSQL module to avoid installing the wrong version:

sudo dnf -qy module disable postgresql

3. Install the PostgreSQL server by running this command. If you want to set up another version, replace 17 with the corresponding number:

sudo dnf install -y postgresql17-server

4. Initialize the database to set up the necessary files and configuration for PostgreSQL:

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

5. Start and enable PostgreSQL during startup by running these commands subsequently:

sudo systemctl enable postgresql-17

sudo systemctl start postgresql-17

And that’s it! Before accessing the database, make sure PostgreSQL is running by checking its status using this command:

sudo systemctl status postgresql-17

2. Connect to the PostgreSQL shell

To manage your database, you must connect to the PostgreSQL or psql command-line shell. You can do so by switching to the postgres root user, which comes pre-configured during the installation:

sudo su postgres

Your shell prompt should change. Now, enter the PostgreSQL shell using this command:

psql

Once your command line changes into Postgres=#, you can manage your database. To quit the PostgreSQL prompt and return to your system’s main shell, enter the following:

\q

3. Create a new role

A role in PostgreSQL is identical to a user or group – an entity that you can use to manage your databases and access permissions in PostgreSQL. To set one up, use the following statement:

CREATE ROLE my_role;

Since we didn’t set a password, the above statement will create a new role without a login privilege. Think of this role as similar to groups, which are useful for managing privileges.

If you want to make a role with a login permission similar to a user, use the following command:

CREATE ROLE my_role WITH LOGIN PASSWORD 'my_password';

When creating a role, you can add the permissions afterward using the GRANT command. Alternatively, you can use the interactive setup to assign the privileges by answering questions.

To do so, quit your PostgreSQL shell and switch back to the postgres user. Then, run this command:

createuser --interactive

Simply answer the questions to set up a role based on your needs. To check all roles in your database, run the following command in the psql shell:

\du

If you want to enter the postgres shell using that role, you must also create a corresponding new Linux user in your system. Here’s the command:

sudo adduser new-user

Connecting to psql as the new role also requires a database. If the user doesn’t have one, you can use the default postgres database. The command might look like this:

sudo -u new-user psql -d postgres 

4. Make a new database

To create a new PostgreSQL database, log in to the psql shell as a user with the necessary permissions.

Now, create a new database by running the following command.

CREATE DATABASE db_name;

List all databases to verify that you have successfully created a new one. Here’s the command:

\l

Now, connect to the new database by running the following command. Replace db_name with the actual name:

\c db_name

Once connected, you can check information about the current database by entering this:

\conninfo

5. Add and remove a database table

RDBMS, like PostgreSQL, organizes data into tables consisting of rows and columns. For more efficient management, you can group related tables into a category called schema.

For example, you can organize tables about employee data and payroll invoices into a human resource (HR) schema. To create a schema, use the following statement:

CREATE SCHEMA schema_name;

To create a table within the schema, simply add the schema name at the beginning of your statement like so:

CREATE TABLE schema_name.table_name (

    column1 data_type constraints,

    column2 data_type constraints,

    ...

);

When creating a table, list the columns separated by a comma. Each column should contain a data type to specify values to insert and a constraint to define the data criteria.

For example, you can set the data type to integers, characters, or boolean values. Meanwhile, you can set a constraint to permit only non-empty values or unique data that other columns don’t have.

Here’s a statement example that creates an employee data table inside the HR schema, containing four columns about IDs, first and last names, as well as hiring date:

CREATE TABLE hr.employees (

    employee_id SERIAL PRIMARY KEY,

    first_name VARCHAR(100) NOT NULL, 

    last_name VARCHAR(100) NOT NULL,

    hire_date DATE NOT NULL  

);

To check the table, enter the following in your psql command-line shell:

\d table-name

If you want to check tables belonging to another schema, use this command instead:

\dt schema-name.table

6. Insert, update, and delete data in a table

After setting up a table and columns, you can start populating them with data. To do so, specify the table and columns you want to insert the data into, then list the values like so:

INSERT INTO hr.employees (first_name, last_name, hire_date)

VALUES

    ('John', 'Doe', '2024-01-15'),

    ('Jane', 'Smith', '2023-11-10');

In the example, we insert values into employees table’s first name, last name, and hiring date column. The first row will have data about John Doe, while the second contains details about Jane Smith.

To check the value of a specific row, you can use a particular condition as a filter. For example, the following will look up an employee using their ID:

SELECT * FROM employees

WHERE employee_id = 1;

You can also use conditions to update a specific row’s value. For example, this statement will change Jane Smith to Jane Doe:

UPDATE employees

SET last_name = 'Doe'

WHERE employee_id = 2;

Moreover, conditions are useful for deleting data. For instance, we will delete data about an employee with the ID of 1:

DELETE FROM employees

WHERE employee_id = 1;

Deleting a row will completely remove the entry from your table. Doing so won’t affect neighboring data unless they have specific constraints, like the foreign key relationship.

Conclusion

In this article, we have explained how to install PostgreSQL on CentOS 9. Before setting it up, make sure you have a server that meets the minimum system requirements, like Hostinger’s KVM 1 VPS plan.

Once you have the system, install PostgreSQL using the following steps:

1. Download the latest PostgreSQL package and enable its service using systemctl. 
2. Connect to the PostgreSQL shell by switching to the postgres account and entering psql.
3. Create a new role in your database and Linux system with login permission.
4. Make a new database using a role with sufficient permission, like postgres.
5. Add a table to organize data in your database by entering the CREATE statement. 
6. Populate and manage data in your database tables using statements like INSERT and UPDATE.

We hope this article helped you set up a PostgreSQL database in your CentOS 9 system. If you have any questions or issues, leave us a comment below.

FAQ about installing and creating PostgreSQL database