Introduction

PostgreSQL is 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.
It provides very efficient way to work with databases on PostgreSQL like, create database, table, alter database, export/import database etc.

Install PostgreSQL

1 . Go to the PostgreSQL repository download page : http://yum.postgresql.org/repopackages.php  and add the PostgreSQL repository depending upon your server architecture.

For CentOS 6.x 64bit:

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

PostgreSQL 9.5 Release : rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-1.noarch.rpm

For CentOS 7 64bit:

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

PostgreSQL 9.5 Release : rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-1.noarch.rpm

2. Update the repository list using command:

yum update

3.  Install postgresql with the following command:For PostgreSQL 9.4
yum install postgresql94-server postgresql94-contribFor PostgreSQL 9.5
yum install postgresql95-server postgresql95-contrib

4. Initialize postgresql database using following command:

On CentOS 6.x systems:

For PostgreSQL 9.4
service postgresql-9.4 initdb

For PostgreSQL 9.5
service postgresql-9.5 initdb

On CentOS 7 systems:

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

For PostgreSQL 9.5
/usr/pgsql-9.5/bin/postgresql95-setup initdb

5. Start postgresql service and make it to start automatically on every reboot.

On CentOS 6.x systems:

For PostgreSQL 9.4

service postgresql-9.4 start
chkconfig postgresql-9.4 on

For PostgreSQL 9.5
service postgresql-9.5 start
chkconfig postgresql-9.5 on

On CentOS 7 systems:

For PostgreSQL 9.4
systemctl start postgresql-9.4
systemctl enable postgresql-9.4

For PostgreSQL 9.5
systemctl start postgresql-9.5
systemctl enable postgresql-9.5

Access PostgreSQL command prompt

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

1. Login to postgresql prompt,enter the command:

su – postgres
psql

2 . Set postgres password with following command:

Sample Output:

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

3 . To exit from posgresql prompt, type \q following by quit to return back to the Terminal.

Create New User and Database

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

Switch to postgres user:
$ su – postgres

Create user e2enetworks.
$ createuser  e2enetworks

Create database:
$ createdb mydb

Now, login to the psql prompt, and set password and Grant access to the database mydb for e2enetworks:

$ psqlpsql (9.4.0)
Type “help” for help.postgres=# alter user e2enetworks with encrypted password ‘centos’;
ALTER ROLEpostgres=# grant all privileges on database mydb to e2enetworks;
GRANT
postgres=#

Manage PostgreSQL with phpPgAdmin

phpPgAdmin is a web-based administration utility written in PHP for managing PosgreSQL.

1.Enter the following command:

yum install epel-release
yum update

2.Now, Install phpPgAdmin, enter the following command:

yum install phpPgAdmin

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

By default, you can access phppgadmin using http://localhost/phpPgAdmin from your local system only. To access remote systems, do the next step given below.

3. Edit file /etc/httpd/conf.d/phpPgAdmin.conf:

vim /etc/httpd/conf.d/phpPgAdmin.conf

Make the changes as shown below :

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>

4 . Start or Restart Apache service:

On CentOS 6.x systems:
service httpd start
chkconfig httpd on

On CentOS 7 systems:
systemctl enable httpd
systemctl start httpd

Configure phpPgAdmin :

1. 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.

vim /etc/phpPgAdmin/config.inc.php

Make the changes as shown below :$conf[‘servers’][0][‘host’] = ‘localhost’;
$conf[‘extra_login_security’] =false;
$conf[‘owned_only’] =true;

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

Now open your browser and navigate to http://ip-address/phpPgAdmin
You will see the phpPgAdmin screen.

SOurce : https://www.e2enetworks.com/help/installing-postgresql-and-phppgadmin-in-centos/

Posted on: August 29, 2020, by : Julian's | 39 views