CentrioHost Blog

1. Introduction

PostgreSQL is an powerful open-source relational database management system that provides an implementation of the SQL querying language. phpPgAdmin is web-based client written in php for accessing and managing PostgreSQL databases. This article will help you to install PostgreSQL with phpPgAdmin on centos 7.

2. Requirements

Before installing it make sure you have Apache installed on server.

3. Install PostgreSQL

In order to install the latest version of PostgreSQL we need to download postgresql repository in our system. So you may run the following command:

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

Then you have to install this rpm repository using:

# rpm -i pgdg-redhat94-9.4-1.noarch.rpm

Now install PostgreSQL using yum:

# yum install postgresql94-server postgresql94-contrib

You have to initialise the database after the installation is completed using the below command:

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

Start the PostgreSQL service and make it to start automatically at every reboot.

# systemctl start postgresql-9.4
# systemctl enable postgresql-9.4

3.1 Login to postgresql

During the installation a user ‘postgres’ will be created by default. Switch to user postgres:

# su - postgres
-bash-4.2$ 

Run the command below to login to PostgreSQL

#psql
psql (9.4.0)
Type "help" for help.
postgres=#

You can change the default password of postgres using following command:

postgres=# password postgres
postgres=# q
bash-4.2$

Create a user and database in PostgreSQL command line and itself. Also grant access to the database for the created user.

-bash-4.2$ createuser alice
-bash-4.2$ createdb db
-bash-4.2$ psql
postgres=# alter user alice with encrypted password 'alice123';
postgres=# grant all privileges on database db to alice;
postgres=# q
-bash-4.2$ exit

4. Install phpPgAdmin

Install phpPgAdmin using the command:

# yum install phpPgAdmin

5. Configure phpPgAdmin

Edit the file /etc/httpd/conf.d/phpPgAdmin.conf and make changes accordingly.

Alias /phpPgAdmin /usr/share/phpPgAdmin


    
        # Apache 2.4
        Require all granted
        #Require host example.com
    
    
        # Apache 2.2
        Order deny,allow
        Allow from all
        Allow from 127.0.0.1
        Allow from ::1
        # Allow from .example.com
    


# vi /etc/phpPgAdmin/config.inc.php

Also edit the php file as follows:

$conf['servers'][0]['host'] = 'localhost';

$conf['servers'][0]['port'] = 5432;

$conf['owned_only'] = true;

6. Configure PostgreSQL

Configure PostgreSQL-MD5 Authentication by editing the file /var/lib/pgsql/9.4/data/pg_hba.conf

# vi /var/lib/pgsql/9.4/data/pg_hba.conf

# 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
host    all             all             138.201.3.0/24          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Configure TCP/IP

# vi /var/lib/pgsql/9.4/data/postgresql.conf

Edit the file and make following changes 
[...]
listen_addresses = 'localhost'
[...]
port = 5432
[...]

Save the file and restart the services:

# systemctl restart postgresql-9.4

# systemctl restart httpd

Now open your browser and navigate to http://ip-address/phpPgAdmin.