Tutorials

Install PostgreSQL on CentOS 7

Table of Contents

Introduction

PostgreSQL is an open source object-relational database system. It is a mature database system known for reliability, data integrity, and correctness.

CentOS 7 includes PostgreSQL 9.2 in its default repositories. This tutorial will demonstrate the steps involved in getting the current PostgreSQL version, 9.4 as of this writing, up and running on CentOS 7.

Requirements

  • CentOS 7
  • PostgreSQL 9.4
  • Internet Access

Add the Repository

A set of PostreSQL repositories are maintained at this site:

http://yum.postgresql.org/repopackages.php#pg94

Since our target distribution is CentOS 7, we'll be downloading and installing this specific rpm:

http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm

This can be done from a shell (as the root user):

sudo yum -y install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Successful installation will result in the appearance of a new repository:

/etc/yum.repos.d/pgdg-94-centos.repo

Installation

We'll now proceed to install:

sudo yum -y groupinstall "PostgreSQL Database Server 9.4 PGDG"

Congratulations! PostgreSQL 9.4 is now installed.

Post Install Configuration

Initialize PostgreSQL:

sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb

Start the PostgreSQL service:

sudo systemctl start postgresql-9.4.service

Set the PostgreSQL service to start on boot:

sudo systemctl enable postgresql-9.4.service

Verification and Examples

PostgreSQL utilizes local system user accounts for access control. Lets go ahead and verify we can connect to our local PostgreSQL instance.

First we will switch to the "postgres" user that was added when we installed PostgreSQL:

sudo su postgres -

Now we can connect to the database server running on localhost:

psql

To see the available commands in the psql shell, run:

help

or:

\h

Let's take a look at our currently configured user roles:

\du

Verify the version of PostgreSQL installed:

SELECT version();

which should result in something like this:

version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

Now we will go ahead and exit:

\q

You can see a list of databases by running:

psql -l

Configuration Changes

The configuration files for PostgreSQL on CentOS 7 are located in:

/var/lib/pgsql/9.4/data/

These files contains helpful comments regarding the configuration options available.

By default, PostgreSQL is operating through a socket on the localhost. In that configuration, the installation is secured against remote threats. If you do not need to access the database from a remote host, you can leave the default configuration. However, we often need to access a database from a remote host.

Configuring access is handled by editing a couple files. First we will tell PostgreSQL to start listening on our network interfaces. This is done by making a change in /var/lib/pgsql/9.4/data/postgresql.conf. Find this section:

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

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

and change it by uncommenting the listen_addresses line and changing localhost to *. Like this:

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

We will leave the port set to its default value of "5432". Make sure and save the change. Now we will edit /var/lib/pgsql/9.4/data/pg_hba.conf and tell PostgreSQL that we want to accept connections from a specific IP address or range. Find this section in the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident

and add a new host line with a specific IP address or range:

host    all             all             <Client IP address>/32        md5

Now we can restart PostgreSQL:

# sudo systemctl restart postgresql-9.4.service

and verify that we are now listening on port 5432:

# ss -l -n |grep 5432
u_str  LISTEN     0      128    /var/run/postgresql/.s.PGSQL.5432 7728992                 * 0
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 7728994                 * 0
tcp    LISTEN     0      128                    *:5432                  *:*
tcp    LISTEN     0      128                   :::5432                 :::*

We will also add a local firewall rule to allow the incoming connection on port 5432. For example:

sudo firewall-cmd --permanent --zone=trusted --add-source=<Client IP address>/32

sudo firewall-cmd --permanent --zone=trusted --add-port=5432/tcp

sudo firewall-cmd --reload

We are almost ready to go at this point, the only remaining issue is that the default postgres user has its password disabled by default. We can set one using psql like this:

sudo su postgres -

bash-4.2$ psql
psql (9.4.4)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
exit

Now we can connect from a remote system using a command-line client, or a GUI such as "pgAdmin3". Connecting from another system using psql looks like this:

$ psql -h <Server IP Address> -p 5432 -U postgres -W
Password for user postgres:
psql (9.4.4)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# \q

This is just a starting point for working with PostgreSQL. Enjoy!