Introduction

PostgreSQLis a powerful,open-source object-relational database system. It runs under all major operating systems,including Linux,UNIX (AIX,BSD,HP-UX,SGI IRIX,Mac OS,Solaris,Tru64),and Windows OS.

Postgresql 9.4 has been released last week with major enhancements,fixes,and features. Read what is new in Postgresql 9.4here.

In this handy tutorial,let us see how to install Postgresql 9.4 on CentOS 7/6.5/6.4 server.

Install Postgresql

Go to the Postgresql repositorydownload page,and add the Postgresql 9.4 repository depending upon your server architecture.

For CentOS 6.x 32bit:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-centos94-9.4-1.noarch.rpm

For CentOS 6.x 64bit:

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

For CentOS 7 64bit:

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

Update the repository list using command:

yum update

Now,Install postgresql with the following command:

yum install postgresql94-server postgresql94-contrib

Initialize postgresql database using following command:

On CentOS 6.x systems:

service postgresql-9.4 initdb

On CentOS 7 systems:

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

Then,start postgresql service and make it to start automatically on every reboot.

service postgresql-9.4 start chkconfig postgresql-9.4 on

systemctl enable postgresql-9.4 systemctl start postgresql-9.4

Adjust Iptables/Firewall

Next,adjust iptables to access postgresql from remote systems.

vi /etc/sysconfig/iptables

Add the following line:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT

Save and exit the file. Restart iptables service.

service iptables restart

firewall-cmd --permanent --add-port=5432/tcp firewall-cmd --permanent --add-port=80/tcp firewall-cmd --reload

Adjust SELinux

Run the following command to make Postgresql work if SELinux enabled on your system.

setsebool -P httpd_can_network_connect_db 1

You may not login to Postegresql if you didn’t run the above command.

Access Postgresql command prompt

The default database name and database user are“postgres”. Switch to postgres user to perform postgresql related operations:

su - postgres

To login to postgresql,enter the command:

psql

Sample Output:

psql (9.4.0)
Type "help" for help.

postgres=#

To exit from posgresql prompt,type\qfollowing byquitto return back to the Terminal.

Set “postgres” user password

Login to postgresql prompt,

su - postgres

psql

.. and set postgres password with following command:

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

To install Postgresql Adminpack,enter the command in postgresql prompt:

postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION

Create New User and Database

For example,let us create a new user called“senthil”with password“centos”,and database called“mydb”.

Switch to postgres user:

su - postgres

Create usersenthil.

$ createuser senthil

Create database:

$ createdb mydb

Now,login to the psql prompt,and set password and Grant access to the databasemydbforsenthil:

$ psql

psql (9.4.0)
Type "help" for help.

postgres=# alter user senthil with encrypted password 'centos';
ALTER ROLE

postgres=# grant all privileges on database mydb to senthil;
GRANT
postgres=#

Delete Users and Databases

To delete the database,switch to postgres user:

su - postgres

Enter command:

$ dropdb <database-name>

To delete a user,enter the following command:

$ dropuser <user-name>

Configure Postgresql-MD5 Authentication

MD5 authenticationrequires the client to supply an MD5-encrypted password for authentication. To do that,edit/var/lib/pgsql/9.4/data/pg_hba.conffile:

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

Add or Modify the lines as shown below

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

Restart postgresql service to apply the changes:

service postgresql-9.4 restart

systemctl restart postgresql-9.4

Configure Postgresql-Configure TCP/IP

By default,TCP/IP connection is disabled,so that the users from another computers can’t access postgresql. To allow to connect users from another computers,Edit file/var/lib/pgsql/9.4/data/postgresql.conf:

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

Find the lines:

[...]
#listen_addresses = 'localhost'
[...]
#port = 5432
[...]

Uncomment both lines,and set the IP address of your postgresql server or set‘*’to listen from all clients as shown below:

listen_addresses = '*'
port = 5432

Restart postgresql service to save changes:

/etc/init.d/postgresql-9.4 restart

Manage Postgresql with PHPPgAdmin

phpPgAdminis a web-based administration utility written in PHP for managing Posgresql.

PHPPgAdmin is available only in Postgresql RPM repository. If you didn’t add Postgresql repository,you should add EPEL repository.

Follow the below link to install EPEL repository on CentOS 6.x.

• Install EPEL repository on CentOS/RHEL/Scientific Linux 6

For CentOS 7,refer the following link.

• Install EPEL Repository on CentOS 7

Or,simply enter the following command:

yum install epel-release

Update the repository using command:

yum install PHPPgAdmin httpd

Note:PHPPgAdmin is case sensitive. Use upper and lower cases properly as shown in the above command.

By default,you can access PHPpgadmin usinghttp://localhost/PHPPgAdminfrom your local system only. To access remote systems,do the following.

Edit file/etc/httpd/conf.d/PHPPgAdmin.conf:

vi /etc/httpd/conf.d/PHPPgAdmin.conf

Make the changes as shown below in the bold letters.

[...]
Alias /PHPPgAdmin /usr/share/PHPPgAdmin

<Location /PHPPgAdmin>
 <IfModule mod_authz_core.c>
 # Apache 2.4
 Require all granted
 #Require host example.com
 </IfModule>
 <IfModule !mod_authz_core.c>
 # Apache 2.2
 Order deny,allow
 Allow from all
 # Allow from .example.com
 </IfModule>
</Location>

Start or Restart Apache service:

service httpd start chkconfig httpd on

systemctl enable httpd systemctl start httpd

Configure PHPPgAdmin

Edit file/etc/PHPPgAdmin/config.inc.PHP,and do the following changes. Most of these options are self-explanatory. Read them carefully to know why do you change these values.

vi /etc/PHPPgAdmin/config.inc.PHP

Find the following line:

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

Change it as shown below:

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

And find the line:

$conf['extra_login_security'] = true;

Change the value tofalse:

$conf['extra_login_security'] = false;

Find the line:

$conf['owned_only'] = false;

Set the value astrue.ru

$conf['owned_only'] = true;

Save and close the file. Restart postgresql service and Apache services.

service postgresql-9.4 restart service httpd restart

systemctl restart postgresql-9.4 systemctl restart httpd

Now open your browser and navigate tohttp://ip-address/PHPPgAdmin. You will see the following screen.

Login with users that you’ve created earlier. I already have created a user called“senthil”with password“centos”before,so I log in with user “senthil”.

You may get an error called:Login Failed.

This is because SELinux might restrict the users to log in to the Postgresql. Just enter the following command to get rid of this error.

setsebool -P httpd_can_network_connect_db 1

Now,you’ll be able to log in to the Dashboard without any problems.

This is how my PHPPgAdmin dashboard looked.

Log in with postgres user:

That’s it. Now you’ll able to create,delete and alter databases graphically using PHPPgAdmin easily.

Cheers!