Install/Configure PostgreSQL on CentOS 8

Spread the love

In my current organization, we have lot of postgres databases running on linux. So thought to learn the same by building my own CentOS 8 vm on VirtualBox.

Below are the steps required for installation and configuration of PostgreSQL server on CentOS 8:-

  1. Go to https://www.postgresql.org/download/, and choose Linux & ‘Red Hat/CentOS’ as distribution.

2. On next page, select appropriate version/platform/architecture

3. Based on the selection of step 2 details, we get appropriate Linux commands. Below are the commands for PostgreSQL 12 on CentOS 8:-

# Switch to super user
sudo -i

# Install the repository RPM:
dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
dnf -qy module disable postgresql

# Install PostgreSQL:
dnf -y install postgresql12-server

# Initialize the database and enable automatic start:
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

With above step 3 installation is complete. But I want to access this Postgres server from my host machine using pgAdmin/psql SQL Shell. So below are the steps required to make this happen:-

# Switch to postgres
su - postgres
# Connect to server locally
psql
# Change postgres account password
ALTER USER postgres WITH PASSWORD 'postgres';
# Exit psql
\q
# Exit back to sudo user
exit

# Add firewall rule
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
systemctl restart firewalld

# Update locatedb
updatedb

Now, we need to modify 2 config files to allow/listen connectivity for other machines.

# locate/modify file postgresql.conf, update listen_addresses to '*' from 'localhost'
locate postgresql.conf
vim /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'
# locate/modify pg_hba.conf, and add below line at the end to listen on all ips
locate pg_hba.conf
vim /var/lib/pgsql/12/data/pg_hba.conf
host all all 0.0.0.0/0 md5

With the above steps, we are done with required changes. Last part is to restart Postgresql-12 services.

systemctl restart postgresql-12.service

Now, let’s connect the above server using pgAdmin installed on my host machine. In case this does not work, I would reboot Linux VM once.

With this, we should be good to install/configure/start using postgres. In case of other linux os, only the installation part (first 3 steps) would differ.

I hope this has been helpful. Happy Coding 🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.