How to Install PostgreSQL on CentOS 7

How to Install PostgreSQL on CentOS 7
How to Install PostgreSQL on CentOS 7

Installing PostgreSQL on CentOS 7

PostgreSQL or Postgres is a popular relational database management system. PostgreSQL is also an opensource and general-purpose database system which provides an implementation of SQL querying language. It has many advanced features like reliable transactions and concurrency without read locks. In this tutorial, you are going to learn how to install PostgreSQL on CentOS 7 with basic database administration.

Here we are going to show two methods to install PostgreSQL on CentOS and they are:

  • Install PostgresSQL on CentOS from CentOS repository.
  • Install Latest PostgreSQL 11.1 on CentOS from PostgreSQL repository

Prerequisites

Before you start to install PostgreSQL on CentOS 7. You must have the root user account credentials of your system.

Install PostgreSQL on CentOS from CentOS repository

To install PostgreSQL on CentOS run following command.

Now run following command to install PostgreSQL with -contrib package which adds additional features and functionalities to PostgreSQL:

sudo yum install postgresql postgresql-contrib

Then initialize PostgreSQL database running following command:

sudo postgresql-setup initdb

To start and enable PostgreSQL service after boot run following commands:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Finally check the Postgres version and confirm installation run following psql command:

sudo -u postgres psql -c "SELECT version();"

By using psql utility you can interact with the PostgreSQL database easily.

Install Latest PostgreSQL 11.1 on CentOS from PostgreSQL repository

To install latest PostgreSQL on CentOS which is currently 11.1.1 (at the time of writing this article) run following command.

First enable PostgreSQL repository using following command:

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

Now run following command to install PostgreSQL with -contrib package which adds additional features and functionalities to PostgreSQL:

sudo yum install postgresql11-server postgresql11-contrib

Then initialize PostgreSQL database running following command:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

To start and enable PostgreSQL service after boot run following commands:

sudo systemctl start postgresql-11
sudo systemctl enable postgresql-11

Finally check the Postgres version and confirm installation run following psql command:

sudo -u postgres /usr/pgsql-11/bin/psql -c "SELECT version();"

The output should be:

PostgreSQL 11.1.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

By using psql utility you can interact with the PostgreSQL database easily.

PostgreSQL Roles and Databases

Both user and group are presented as term role in PostgreSQL for handling database access permissions. Ident, Trust, Password and Peer are different methods for authentication in PostgreSQL. Ident method mainly used on TCP/IP connection while Trust method used to connect without password using given criteria in pg_hba.conf file.

You can log in to PostgreSQL using below command:

sudo su - postgres
psql

To exit from here type following in the terminal:

\q

Create PostgreSQL Database and Role

By using createdb method you can create a database in Postgres and by using createuser method you can create a new role.

To create new database called test_db run following command:

sudo su - postgres -c "createdb test_db"

Now create a new role called test_user run below command:

sudo su - postgres -c "createuser test_user"

Now you should grant permission to the user test_user for the newly created database test_db you should execute a query in PostgreSQL Shell to so run following command:

sudo -u postgres psql

Execute the following query to grant permission:

grant all privileges on database test_db to test_user;

Open PostgreSQL Prompt with New Role

To open the PostgreSQl prompt with new role you should have the same Linux user available as PostgreSQL user and database.

To create new Linux user in CentOS named test_user if the matching user is not available by running following command:

sudo adduser test_user

After creating matching user account on the Linux system, you can connect to the database running following command:

sudo -u sammy psql

Enabling Remote Access to PostgreSQL server

Use the following steps to enable remote access to the PostgreSQL server.

Edit /etc/postgresql/10/main/postgresql.conf file and place listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION SECTION to do so run following command:

sudo nano /etc/postgresql/10/main/postgresql.conf

Update listen_addresses like given below:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     

Now restart the PostgreSQL service with systemctl command:

sudo systemctl restart postgresql

Now confirm and verify the changes typing following in terminal:

ss -nlt | grep 5432

The output should be:

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

Finally update pg_hba.conf file to configure server remote connections using below examples:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user test_user will be able access all databases from all locations using a md5 password
host    all             test_user            0.0.0.0/0                md5

# The user test_user will be able access only the test_db from all locations using a md5 password
host    test_db          test_user            0.0.0.0/0                md5

# The user test_user will be able access all databases from a trusted location (192.168.43.106) without a password
host    all             test_user            192.168.1.134            trust

Conclusion

You have successfully learned how to install PostgreSQL on CentOS 7. If you have any queries please don’t forget to comment below.

LEAVE A REPLY

Please enter your comment!
Please enter your name here