How to Install PostgreSQL on CentOS 7

PostgreSQL or Postgres is an open source relational database management system. It is a popular and has many advanced features like reliable transactions and concurrency without read locks. In this tutorial, you will learn how to install PostgreSQL on CentOS 7 using two different methods. The first method is to install from CentOS repositories and second is to install from the official PostgreSQL repositories.

Prerequisites#

Ensure that you are logged in with non-root user account with sudo privileges.

Install PostgreSQL on CentOS from CentOS Repositories#

To install PostgreSQL from the official CentOS repositories follow the below steps:

Install PostgreSQL with PostgreSQL contrib package for several additional features using below command:

sudo yum install postgresql-server postgresql-contrib

After that, you should initialize the PostgreSQL database using following command:

sudo postgresql-setup initdb

It should show output as given below:

Output
Initializing database ... OK

Once the installation is completed, You have to start and enable PostgreSQL service to start automatically on system boot. Run below command:

sudo systemctl start postgresql
sudo systemctl enable postgresql

You can verify installation using below command:

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

It will show output as given below:

Output
PostgreSQL 9.2.23 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

Install PostgreSQL from PostgreSQL Repositories#

Right now at the time of writing this article,the latest version of PostgreSQL is 11.1. You should check PostgreSQL Yum Repository page for new version before going with this step to install.

At first, you should enable PostgreSQL repository by installing .rpm file using below command:

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

Once the repository is enabled install the PostgreSQL server and PostgreSQL contrib package using following command:

sudo yum install postgresql11-server postgresql11-contrib

After that initialize the PostgreSQL database by typing:

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

Output
Initializing database ... OK

Now start the PostgreSQL service and enable it to start on boot type:

sudo systemctl start postgresql-11

sudo systemctl enable postgresql-11

Next you can verify the installation by making connection using psql tool:

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

Output
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)

PostgreSQL Roles and Authentication Methods#

A role can represent a database user or a group of database users. There are multiple authentication methods in PostgreSQL and commonly used are Trust, Ident, Password and Peer.

• Trust – This method used to connect without password using given criteria in pg_hba.conf file.
• Ident – This method is mainly used on TCP/IP connection. It is obtaining client’s operating system user name, etc. details.
• Password – A role can connect by providing a password.
• Peer – It’s same as Ident but it is only supported on local connections.

You can login to PostgreSQL but as postgres user first you need to switch to the postgres user and then you can access a PostgreSQL prompt using the psql utility:

sudo su - postgres

psql

Now here you can interact with your PostgreSQL instance. To get exit of PostgreSQL prompt type:

\q

Create PostgreSQL Role and Database#

You can use createuser command to create new roles from the command line. You can only create if you are superuser or have roles with CREATEROLE privileges. By using createdb method you can create a database in Postgres.

Use the below command to create a new role:

sudo su - postgres -c "createuser demouser"

To create a new database use the following command:

sudo su - postgres -c "createdb demodb"

Now, you should grant permission to user demouser for newly created demodb database. So run below command to connect PostgreSQL shell:

sudo -u postgres psql

Next execute following command which will grant permissions:

grant all privileges on database demodb to demouser;

Enabling Remote Access to PostgreSQL server#

By default, the PostgreSQL server listens only on the local interface 127.0.0.1. You should edit configuration file /etc/postgresql/10/main/postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section to enable remote access to your PostgreSQL server.

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

Update listen_addresses like given below:

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

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Save the file and restart the PostgreSQL service using systemctl command:

sudo service postgresql restart

Now confirm and verify the changes typing following in terminal:

ss -nlt | grep 5432

It will show output as below:

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

At last, update pg_hba.conf file to configure the server to accept remote connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

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

# The user demouser will be able access all databases from a trusted location (192.168.43.125) without a password
host    all             demouser            192.168.43.125            trust

Conclusion#

You have learned successfully how to install and configure PostgreSQL on your CentOS 7 server. If you would like to get more details you can visit PostgreSQL 10.4 Documentation.