PostgreSQL (also called postgres) is a very famous, powerful, open-source relational database system that has been around for almost 30 years. It has a strong reputation for reliability, performance & is feature robust. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.
Recommended Read: How to setup SSH login without password
In this tutorial, we will learn to install PostgreSQL on CentOS & RHEL systems. So let’s start,
Install PostgreSQL on CentOS/RHEL
There are two methods using which we can install Postgresql on CentOS/RHEL systems,
1- Using the default CentOS repositories,
2- Using the official PostgreSQL repositories
1- Install Postgresql using default repositories
This method is quite simple, we can just run the following command to install postgresql on the server,
# yum install postgresql-server postgresql-contrib
Once the database has been started, we need to initialize the database before we can start using it,
# postgresql-setup initdb
Now we can start the database & also enable it to start at boot time,
# systemctl start postgresql
# systemctl enable postgresql
So our database is now up & ready to be used. The only downside to installing postgresql using this method is that we might not get the latest version as the default Centos or RHEL repositories don’t always maintain the latest versions.
So to install the desired version we must use the second method.
2- Install Postgresql using Official Postgresql repositories
Using this method, we will install a stable version 11 of PostgreSQL Install the official repository with the following command,
# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
When prompted, press ‘y’ to install. Next, we can run the yum install command to install postgresql,
# yum install postgresql11-server postgresql11
Next, we will initialize the DB,
# /usr/pgsql-11/bin/postgresql-11-setup initdb
Now we will start the database service & also enable it to start at boot time,
# systemctl start postgresql-11
# systemctl enable postgresql-11
PostgreSQL configuration
Set the Admin user password
To change the database admin user password, first switch to postgres user,
# su – postgres
Next, execute the following command from the terminal,
$ psql -c “alter user postgres with password ‘NEWPASSWORDhere'”
Create a new user & a new database
To create a new database, run the following command,
$ createuser new_user
$ createdb new_db -O new_user
$ grant all privileges on database new_db to new_user
Connect to a database
To connect to a database, use the following command,
$ psql -U new_user -h localhost -d new_db
Connecting to the database from a remote system
The above command will only work from the localhost but if we want to access the database from a remote host, we need to make some changes as by default we can’t connect to the database from the remote system.
To allow access from remote systems, open the following file,
# vi /var/lib/pgsql/11/data/postgresql.conf
& look for the following line,
listen_addresses =
& change to
listen_addresses = ‘*’
Next, we will make changes on another file,
# vi /var/lib/pgsql/11/data/pg_hba.conf
& add the following lines,
# Accept from anywhere
host all all 0.0.0.0/0 md5
# Accept from trusted subnet
host all all 10.10.0.0/16 md5
We need to restart the database server to implement the changes.
# systemctl restart postgresql-11
We now end this tutorial, please also see our tutorial on some of the important Postgresql commands that you should know. Please feel free to send in any questions or queries using the comment box below.
