How to install and Configure PostgreSQL with phpPgAdmin on CentOs 7

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

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

Run the command below to login to PostgreSQL

psql (9.4.0)
Type "help" for help.

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

postgres=# \password postgres
postgres=# \q

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
        # Apache 2.2
        Order deny,allow
        Allow from all
        Allow from
        Allow from ::1
        # Allow from

# vi /etc/phpPgAdmin/

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               md5
host    all             all             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.

How to Install PostgreSQL on Debian 8

1. Introduction

PostgreSQL pronounced as “post-gress-Q-L” is a free and open source object-relational database management system. It is developed by a group of companies and some individuals. It supports almost all relational database features and offers a few unusual features that are normally absent in other RDBMS engines. Commonly supported objects include views, stored procedures, indexes, triggers and object-defined data types.

It is used by several web programming languages including PHP, and Python. These programming languages make is extremely easy to connect to a PostgreSQL database. It is also used for many content management scripts. It is very easy to store and retrieve information using SQL commands in PostgreSQL.

2. Advantages

  • It is a very powerful RDBMS system.
  • Cross Platform.
  • High stability and reliability.
  • It is highly programmable as well as extendible.
  • It has a strong third party support.
  • It supports a large number of data types.

3. Requirements

If you have a freshly installed debian 8 server with root access you are ready to install PostgreSQL 9.4.

In this article, We are going to install PostgreSQL and give you some introduction. So let’s start with the installation.

4. Installation

Here, We are going to install the PostgreSQL version. So we need to update the system with the latest updates.

Update the apt packages :

# apt-get update

You will see a message like the one below which means all the packages are been updated :

Reading package lists... Done

Now, Install PostgreSQL packages using apt-get :

# apt-get install postgresql-9.4 postgresql-client-9.4

At the end of the installation, you will get the information of configuration files, ports and further details. It will be like the one below.

Creating config file /etc/postgresql-common/createcluster.conf with new version

Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Setting up postgresql-9.4 (9.4.5-0+deb8u1) ...
Creating new cluster 9.4/main ...
  config /etc/postgresql/9.4/main
  data   /var/lib/postgresql/9.4/main
  locale C
  port   5432
update-alternatives: using /usr/share/postgresql/9.4/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for libc-bin (2.19-18+deb8u1) ...
Processing triggers for systemd (215-17+deb8u2) ...

Thus the installation is completed.

Now we can confirm the installation using some commands. They are given below :

# root@name-debian8:~# ps -ef | grep postgre

This will provide you an output like the one below :

We can also check its status by the following commands :

~# netstat -tnulp | grep 5432
tcp        0      0*               LISTEN      17148/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      17148/postgres 

Thus we can confirm that the PostgreSQL is properly installed and running. Now, We can check accessing the database.

5. Accessing the Database

By default, a user and a database named postgres will be created during the installation. Also, We can only access the database by switching to that user postgres.

Switch to the PostgreSQL user by following the commands :

# su - postgres

Then get into the PostgreSQL console using the command pgsql :

postgres@name-debian8:~$ psql
psql (9.4.5)
Type "help" for help.

postgres=# \q
postgres@name-debian8:~$ exit

Now, You can exit from psql console by using \q and then exit command to logout from PostgreSQL.

6. Creating New Roles and Database

A role is an user, group or both that can own database objects and have database privileges. This will adds a new role to a PostgreSQL database cluster. Let’s see how to create a role.

Log in as the user postgres :

# su - postgress

Then use the following command to create a new role :

createuser --interactive

It will be an interactive session and ask you for the name of the role and whether it should be a superuser.

postgres@name-debian8:~$ createuser --interactive
Enter name of role to add: testpsql
Shall the new role be a superuser? (y/n) y

Thus we have successfully created a role.

In the same postgres terminal. Create a database using the command below. But please note that the database name should be same as the role.

postgres@name-debian8:~$ createdb testpsql 

The new database testpsql is now created.

7. Connecting to PostgreSQL

We have created an user and database called testpsql. Now, log in as the user testpsql and connect to the database.

# su - testpsql
No directory, logging in with HOME=/
$ psql
psql (9.4.5)
Type "help" for help.


Thus we have successfully connected to the database.

8. Conclusion

Thus we have installed the PostgreSQL in debian. You can check my upcoming post to know more about the working of PostgreSQL.