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:-
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 🙂