How To Install PostgreSQL On Centos 7

In this blog post , I have explained how to install PostgreSQL on Centos 7.

What Is PostgreSQL?

PostgreSQL is an open source and powerful object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender.

How To Install PostgreSQL On Centos 7

Installing From Default Centos 7 Repository

PostgreSQL can be installed using the default Ubuntu repository.

But it may not have the latest version , but we will try to install postgreSQL from the centos 7 repository.

Login to the Server , make sure you have sudo or root privileges to install packages.

Update the centos default software repository,

yum update

And install the postgresql packages using the below command.

sudo yum install postgresql-server postgresql-contrib

After the packages are installed ,

We need to initialize the database using the below command.

postgresql-setup initdb

After initializing the database , Start the postgresql service using the below command.

systemctl start postgresql

To check the status of postgresql service , Run the below command.

systemctl status postgresql

Enable the postgresql service to start automatically on system reboot.

systemctl enable postgresql

To check the version of postgresql installed on the system,

postgres --version

Be default , PostgreSQL runs on the port 5432 and listens to localhost only.To check ,

netstat -nltp |grep postgres

Next We will see how to install postgresql latest version from the official postgresql repository.

Before that , If you want to remove existing PostgreSQL (older version) installed on the system , Run the below command,

sudo yum remove postgresql-server postgresql-contrib

Installing PostgreSQL Using The Official PostgreSQL Repository

With this method , You can choose the specific version of postgresql from the official postgresql repository.

Now We will install the latest version (12) of PostgreSQL from the repository.

You can use the same procedure to install different version.

Install the Repository RPM.

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

Installing PostgreSQL from the repostory.

yum install postgresql12-server

After the packages are installed , You can initialize the database using the below command.

/usr/pgsql-12/bin/postgresql-12-setup initdb

Run this command to start the postgresql service ,

systemctl start postgresql-12

To check the status of the postgresql service,

systemctl status postgresql-12

Activate the postgresql service to automatically start on system reboot,

systemctl enable postgresql-12

We know that , We have installed PostgreSQL of version 12 , but still If you want to check the version , Use the below command.

/usr/pgsql-12/bin/postgres --version

Creating Users , Databases And Granting Permissions

By default , PostgreSQL creates a Role ‘postgres‘ , [Role is nothing but a user] and the database named postgres

The Role postgres , Will have admin privileges such as Creating Roles , databases , configuring replication , managing users and permissions etc.

This user is also called as Superuser

To login to postgresql shell as postgres user , Run the below commands.

sudo su - postgres

psql

We have now logged as postgres user into the postgres database.

To check the lists of available databases,

\l

To list all the available users,

\du

To connect to postgres database,

\c postgres

To list all the available tables in a database,

\dt

To create a Roles / Users in the PostgreSQL database,

Creating user without password,

create user username;

Create user with password,

create user username1 with password 'testpass123';

Create user with password expiry date. In the below example , The user wont be able to login after 12 Dec , 2020.

create user username2 with password 'testpass123' valid until '2020-12-12';

Lets list all the created users,

To drop or delete users from postgresql database,

drop user username

To create database ,

create database testdb;

To drop or delete a database,

drop database testdb;

To grant database access to a user,

grant ALL ON DATABASE testdb to username; 

As the user ‘username‘ was granted full permission to database testdb , he/she can make any changes on that particular database.

To remove / revoke database access for a user,

revoke ALL on DATABASE testdb FROM username;

Conclusion

We have successfully installed PostreSQL on Centos 7 and learnt to manage PostgreSQL databases and users and permissions.

Hope you find it helpful.Please check out my other articles.