How to Install PostgreSQL Database Server on CentOS 7

Author 2 min read

May 29, 2024 9:44 PM

Introduction

PostgreSQL is arguably one of the most advanced open source databases in current times. It is also still under active development by open source developers around the world. Some core features provided by PostgreSQL include the following:

Highly extensibility as users may define their own functions and access methods.
Support for SQL such as the join statements.
Database validity check through referential integrity.
Flexible API to allow for development support in various languages such as PHP, Python, and C++.
Multi-version concurrency support solves the issue of read blocking caused by other users writing on the same database which results to a delay in data access.
The write ahead logging enables restore points to be created in case of an event the database crashes before data is written to it.

With that explained, letâ€™s dive straight into the basics.

Before you Start

To accomplish this task, you must set up the following:

A VPS or dedicated server running CentOS 7.
A non-root user with sudo privileges.

Steps

Update your VPS system packages and install the dependencies:

$ sudo yum update && sudo yum upgrade

Install PostgreSQL package on your VPS and its additional feature package which is necessary for functions such as cryptography, log manipulation, auto encryption of password data types, e.t.c.

$ sudo yum -y install postgresql && sudo yum -y install postgresql-contrib

Once installation has completed, initialize the database and enable automatic startup on boot.

$ postgresql-setup initdb
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

To verify the installation, check the version by running the following command

$ postgres --version

The expected output should be as below.

postgres (PostgreSQL) 9.2.24

Special Note: The output displaying the version may vary. Updates may have been made hence a different version.

Change the default postgres linux user password.

$ sudo passwd postgres

Enter your strong password and confirm it. Change the postgres database user password who performs administrative database tasks. Ensure you replace the password with your own strong password.

$ sudo su - postgres -c "psql"

postgres=# ALTERUSER postgres WITHPASSWORD'password';

Secure Local Access

Special Note: The set password above will be used to connect via a network. When connecting locally, it will authenticate using peer authentication method. Therefore you have to secure this.

To be able to secure the local PostgreSQL database connection, edit the pg_hba file. With your favorite terminal editor, open the file:

$ sudo vim /var/lib/pgsql/data/pg_hba.conf

If you have set numbers on vim editor, on line 80, change the peer authentication method to md5 on local, IPv4 and IPv6 (optional) methods to allow for authentication with md5 hashing algorithm. It should look similar to the one below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
#"local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Exit while saving changes and reload the postgresql service to accommodate the changes

$ sudo systemctl reload postgresql

Create a new database

You can change the name of the database to whatever you want.

$ sudo su - postgres -c "psql"

postgres=# CREATEDATABASEtestdb;

Create a new role.

$ sudo su - postgres -c "psql"

postgres=# CREATEROLE testuser WITHPASSWORD'your_password' NOLOGIN

Special Note: The clauses following theWITH are all optional, you may decide to create a new role by using the following command in the postgres user prompt:

$ createuser --interactive

Conclusion

We have successfully installed PostgreSQL on CentOS 7 VPS and created a new role. With that being done, you can go ahead and start to play about with the database to explore its features you have created; add new tables with columns, add rows, edit and delete them and so on. You can also take a look at How to Manage PostgreSQL Database on CentOS 7.