Introduction

PostgreSQL is one of the most effective free, open-source relational database management systems. It is widely used due to its robustness, security, and advanced features, thus making it a great choice for both developers and database administrators.

PostgreSQL provides a powerful alternative for database handling. If using CentOS 9 for hosting, it will be wiser if you install PostgreSQL to efficiently handle your databases. This tutorial covers how to install PostgreSQL on CentOS 9 and create roles with database configuration that securely use it.

If high-performance VPS hosting is the requirement, keep in mind for a reliable VPS server environment, VPSHosting.lk can host POSTGRES and so much more.

 

Step 1: Update the System

Before installing any software, make sure your CentOS 9 system is updated. Update your system with the following command:

sudo dnf update -y

This will refresh all packages and dependencies, which may be required for a smooth installation.

 

Step 2: Install PostgreSQL on CentOS 9

The latest version of PostgreSQL is not available in CentOS 9's default repositories. To install it, follow these steps:

1. Add the PostgreSQL repository:

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

2. Disable the default PostgreSQL module: 

sudo dnf -qy module disable postgresql

3. Install the PostgreSQL server and client: 

sudo dnf install -y postgresql15-server postgresql15

This will install PostgreSQL 15, one of the latest stable versions.

 

Step 3: Initializing the PostgreSQL Database

After installing PostgreSQL, you have to initialize the database before you can start the service. Follow this command:

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

Now, start and enable PostgreSQL to run on system startup:

sudo systemctl enable --now postgresql-15

Check the status to make sure it's running:

sudo systemctl status postgresql-15

 

Step 4: Configuring PostgreSQL for First-Time Use 

By default, PostgreSQL creates a user named postgres. To access the PostgreSQL shell, switch to this user:

sudo -i -u postgres
psql

You should now be inside the PostgreSQL command-line interface (psql). Type \q to quit.

 

Step 5: Creating a New Role in PostgreSQL

In PostgreSQL, a role is similar to a user account and you need one to be able to access databases. To create a new role, run the following command:

createuser --interactive

You will be asked for a username and whether the role should be a superuser. For more detailed control you can create a role with explicit privileges:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword'; ALTER ROLE myuser CREATEDB; 

Change myuser to a name of your choice as well as mypassword to a good password.

 

Step 6: Creating a New Database in PostgreSQL

Databases are where your data will live and each database should have an owner, which in this case will be a role. Let's create a new database:

createdb mydatabase

To create a database owned by a specific role:

CREATE DATABASE mydatabase OWNER myuser;

This will make myuser the owner of the database, so that user will have all privileges on the database.

 

Step 7: Connecting to the PostgreSQL Database

You can now connect to your new database using the command line with:

psql -U myuser -d mydatabase

You will now log in to the PostgreSQL shell, you can execute SQL commands like:

SELECT version();

 

Step 8: Configuring Remote Access to PostgreSQL 

By default, PostgreSQL allows only local connections. Allow PostgreSQL to remote connections:

 1. Open and edit postgresql.conf and modify the parameter, allowing external connections:

sudo nano /var/lib/pgsql/15/data/postgresql.conf

Find the line:

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*' 

2. Allow remote connection by editing pg_hba.conf: 

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

Insert the following content at the bottom:

host all all 0.0.0.0/0 md5

 3. Restart PostgreSQL in order to make the change in effect:

sudo systemctl restart postgresql-15

After that, PostgreSQL can receive connections remotely.

 

Step 9: Securing PostgreSQL on CentOS 9

Security is the most crucial thing to consider when working with a database. Here are some best practices:

• Change the default password of the postgres user:

ALTER USER postgres WITH PASSWORD 'newpassword';

• Disable remote access if not needed:

sudo nano /var/lib/pgsql/15/data/postgresql.conf

Modify listen_addresses = 'localhost'.

• Regular backups can be done using pg_dump:

pg_dump mydatabase > mydatabase_backup.sql

VPSHosting.lk can be utilized for a private server setup with enhanced security.

 

Step 10: How to Uninstall PostgreSQL (If required)

To uninstall PostgreSQL, use the following procedure:

sudo systemctl stop postgresql-15
sudo dnf remove -y postgresql15-server postgresql15
sudo rm -rf /var/lib/pgsql

This will fully remove PostgreSQL and its data.

 

Conclusion

Installing PostgreSQL on CentOS 9 was pretty straightforward, provided one follows the correct steps. This tutorial has covered it all in detail, from setting up the repository to creating roles and databases. PostgreSQL is a powerful database system that assures good performance and security for your applications if set right.

Proceed and use VPSHosting.lk for the quickest and secure environment to run PostgreSQL on CentOS 9.

 

Frequently Asked Questions

1. How do you find the installed version of PostgreSQL?

• With this command you will be able to see what version is:

psql --version

2. What port does PostgreSQL normally use, and how would you change that if you needed?

• The default listening port of the PostgreSQL server is 5432. You will need to adjust it in the postgresql.conf file by altering the port.

3. How do I change/reset a PostgreSQL user password?

ALTER USER myuser WITH PASSWORD 'newpassword';

4. Can more than one database come under a single role?

• One role can be an owner for more than one database.

5. How will you correctly secure PostgreSQL on CentOS 9?

• Use strong passwords.
• Limit remote access.
• Regularly update PostgreSQL
• Create Backups.