Read in 6 minutes

last updated 

How to Install PostgreSQL on CentOS 7

PostgreSQL or Postgres is an open source general-purpose object-relational database management system with many advanced features which allows you to create complex web applications.

In this tutorial we will show you two different methods on how to install PostgreSQL on your CentOS 7 machine. The first method will walk you through the steps necessary to install PostgreSQL v9.2.23 from the CentOS repositories while the second one will show you how to install the latest version of PostgreSQL from the official PostgreSQL repositories.

If your application doesn’t require the latest version, we recommend using the first method and install PostgreSQL from the CentOS repositories.

We will also explore the fundamentals of PostgreSQL database administration.

Prerequisites

Before continuing with this tutorial, make sure you are logged in as a user with sudo privileges.

Install PostgreSQL from the CentOS repositories

At the time of writing this article, the latest version of PostgreSQL available from the CentOS repositories is PostgreSQL version 9.2.23.

To install PostgreSQL on your CentOS server follow the steps below:

  1. Installing PostgreSQL

    To install the PostgreSQL server along with the PostgreSQL contrib package which provides several additional features for the PostgreSQL database simply type:

    sudo yum install postgresql-server postgresql-contrib
  2. Initializing Database

    Initialize the PostgreSQL database with the following command:

    sudo postgresql-setup initdb
    Initializing database ... OK
  3. Starting PostgreSQL

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

    sudo systemctl start postgresql
    sudo systemctl enable postgresql
  4. Verifying PostgreSQL Installation

    To verify the installation we will try to connect to the PostgreSQL database server using the psql tool and print the server version:

    sudo -u postgres psql -c "SELECT version();"
    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)

Psql is an interactive command line utility which allows us to interact with the PostgreSQL server.

Install PostgreSQL from the PostgreSQL repositories

At the time of writing this article, the latest version of PostgreSQL available from the official PostgreSQL repositories is PostgreSQL version 10.4. Before continuing with the next step you should visit the PostgreSQL Yum Repository page and check if there is a new version available.

Follow the steps below to install the latest PostgreSQL version on your CentOS server :

  1. Enabling PostgreSQL repository

    To enable the PostgreSQL repository simply install the repository rpm file:

    sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
  2. Installing PostgreSQL

    Once the repository is enabled install the PostgreSQL server and PostgreSQL contrib packages with:

    sudo yum install postgresql10-server postgresql10-contrib
  3. Initializing Database

    To initialize the PostgreSQL database type:

    sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
    Initializing database ... OK
  4. Starting PostgreSQL

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

    sudo systemctl start postgresql-10
    sudo systemctl enable postgresql-10
  5. Verifying PostgreSQL Installation

    To verify the installation we will try to connect to the PostgreSQL database server using the psql tool and print the server version:

    sudo -u postgres /usr/pgsql-10/bin/psql -c "SELECT version();"
    PostgreSQL 10.4 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

Database access permissions within PostgreSQL are handled with the concept of roles. A role can represent a database user or a group of database users.

PostgreSQL supports multiple authentication methods. The most commonly-used methods are:

  • Trust - With this method the role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
  • Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256 md5 and password (clear-text).
  • Ident - This method is only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
  • Peer - Same as Ident but it is only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. By default, for local connections PostgreSQL is set to use the peer authentication method.

The postgres user is created automatically when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as the postgres user first you need to switch to the postgres user and then access PostgreSQL prompt using the psql utility:

sudo su - postgres
psql

From here you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell, type:

\q

You can also access the PostgreSQL prompt without switching users using the sudo command:

sudo -u postgres psql

The postgres user is typically used only from the local host and it is recommended not to set the password for this user.

If you installed PostgreSQL version 10 from the official PostgreSQL repositories, you’ll need to use the full path to the psql binary which is /usr/pgsql-10/bin/psql.

Creating PostgreSQL Role and Database

Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example we will create a new role named jonh a database named johndb and grant privileges on the database.

  1. Connect to the PostgreSQL Shell

    sudo -u postgres psql
  2. Create a new PostgreSQL Role

    The following command will create a new role named john:

    create role john;
  3. Create a new PostgreSQL Database

    Create a new database named johndb using the createdb command:

    create database johndb;
  4. Grant privileges

    To grant permissions to the john user on the database we created in the previous step, run the following query:

    grant all privileges on database johndb to john;

Enable remote access to PostgreSQL server

By default the PostgreSQL server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section.

sudo vim /var/lib/pgsql/data/postgresql.conf

If you are running PostgreSQL version 10, the path to the file is /var/lib/pgsql/10/data/postgresql.conf.

/var/lib/pgsql/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

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

save the file and restart the PostgreSQL service with:

sudo systemctl restart postgresql

If you are running PostgreSQL version 10, restart the PostgreSQL service with systemctl restart postgresql-10.

Verify the changes with the ss utility:

ss -nlt | grep 5432
LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).

The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Below are some examples showing different use cases:

/var/lib/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

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

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

If you are running PostgreSQL version 10, the full path to the file is /var/lib/pgsql/10/data/pg_hba.conf.

Conclusion

You have learned how to install and configure PostgreSQL on your CentOS 7 server.

You can consult the PostgreSQL Documentation for more information on this topic.