How to Install PostgreSQL on CentOS 7


PostgreSQL, commonly referred to as Postgres, is a powerful open-source relational database management system (RDBMS). It's known for its reliability, robustness, and extensibility, making it a popular choice for many developers and organizations. If you're looking to install PostgreSQL on CentOS 7, this step-by-step guide will walk you through the process.

Step 1: Update System Packages

Before installing any new software, it's essential to ensure that your system packages are up to date. You can do this by running the following commands :

sudo yum update

Step 2: Enable PostgreSQL Repository

PostgreSQL isn't included in the default CentOS repositories, so you need to enable the PostgreSQL repository to install the latest version. Create a new repository file using the following command:

sudo vi /etc/yum.repos.d/pgdg.repo

Now, paste the following configuration into the file :

name=PostgreSQL 13 for RHEL/CentOS $releasever - $basearch

Step 3: Install PostgreSQL

Once the repository is enabled, you can install PostgreSQL by running the following command :

sudo yum install postgresql13-server postgresql13-contrib

This command will install both the PostgreSQL server and additional contrib packages, which provide additional features and extensions.

Step 4: Initialize PostgreSQL Database

After installing PostgreSQL, you need to initialize the database. Run the following command :

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

This command initializes the PostgreSQL database cluster.

Once the database cluster has been initialized, there will be a file called postgresql.conf in the data folder, which is the main configuration file for PostgreSQL. We will change two parameters in this file. Using vi or your favorite text editor, open the file for editing.

sudo vi /var/lib/pgsql/13/data/postgresql.conf

And change the following lines:

Change #listen_addresses = 'localhost' to listen_addresses = '*'
Change #port = 5432 to port = 5432

The first parameter specifies which IP address the database server will listen to. As a security measure, out-of-box Postgres installations only allow local host connections. Changing this to '*' means Postgres will listen for traffic from any source. The second parameter has been enabled by taking off the comment marker (#); it specifies the default port for Postgres.

Save and exit the file.

Next, we will edit pg_hba.conf, which is PostgreSQL's Host Based Access (HBA) configuration file. It specifies which hosts and IP ranges can connect to the database server. Each entry specifies whether the connection can be made locally or remotely (host), which database it can connect to, which user it can connect as, which IP block the request can come from, and what authentication mode should be used. Any connection requests not matching with any of these entries would be denied.

Open pg_hba.conf for editing.

sudo vi /var/lib/pgsql/13/data/pg_hba.conf

Scroll to the bottom of the file, and add this line:

host		all             all             your_web_server_ip/32          md5

This line tells PostgreSQL to accept database connections coming only from IP address your_web_server_ip using a standard md5 checksum for password authentication. The connection can be made against any database as any user.

Save and exit the file.

Step 5: Start and Enable PostgreSQL Service

Now, start the PostgreSQL service and enable it to start automatically on boot :

sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

Verify PostgreSQL Service Status:

sudo systemctl status postgresql-13

The output should indicate that the service is "active (running)". If it's not running, start it with :

sudo systemctl start postgresql-13

If you after making changes to its configuration files of Postgresql, you have to reload Postgresql

sudo systemctl reload postgresql-13

Configure PostgreSQL to run automatically at startup on CentOS 7:

sudo systemctl is-enabled postgresql-13.service

If the output is not enabled, activate the service with :

sudo systemctl enable postgresql-13.service

Step 6: Access PostgreSQL Prompt

Once PostgreSQL is installed and running, you can access the PostgreSQL prompt by switching to the postgres user and running the psql command :

sudo su - postgres

Step 7: Setting a Password for the PostgreSQL User

By default, PostgreSQL doesn't set a password for the postgres user. To set a password, you can use the following command from within the PostgreSQL prompt :

\password postgres

Step 8: Configuring PostgreSQL

PostgreSQL configuration files are located in the /var/lib/pgsql/13/data directory. You may need to adjust the configuration according to your requirements. Common configuration changes include adjusting memory settings, connection settings, and authentication methods.

Step 9: Updating the Database Server Firewall

We also need to allow Postgres database traffic to pass though the firewall. CentOS 7 implements a dynamic firewall through the firewalld daemon; the service doesn't need to restart for changes to take effect. The firewalld service should start automatically at system boot time, but it's always good to check.

sudo firewall-cmd --state

The default state should be running, but if it is not running start it with :

sudo systemctl start firewalld

Next, add the rules for port 5432. This is the port for PostgreSQL database traffic.

sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp

Then reload the firewall.

sudo firewall-cmd --reload

Command to view the ports registered in your firewall on CentOS 7

sudo firewall-cmd --list-all

Step 10: Testing PostgreSQL Installation

Please connect to Postgres with this command :

psql -U postgres -h xx.xx.xx.xx

The script connects to the PostgreSQL database server with the username 'postgres' and the host address 'xx.xx.xx.xx' using the psql command

To ensure that PostgreSQL is installed correctly and running smoothly, you can create a test database and perform basic operations. Here's an example :

\c testdb;
INSERT INTO users (name) VALUES ('John'), ('Alice'), ('Bob');
SELECT * FROM users;


By following these steps, you should have successfully installed PostgreSQL on your CentOS 7 system. PostgreSQL is now ready to use for your projects and applications, providing a reliable and powerful database management solution. Remember to consult the PostgreSQL documentation for more advanced configuration options and features.