How To Install Postgresql 9.3 And Phppg In Centos 6 t3u6c

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 by SK

Share this Article:

251

2

19

0

0

1

PostgreSQL is a powerful, open-source object-relational database system. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS, Solaris, Tru64), and Windows OS. In this handy tutorial, let us see how to install PostgreSQL on CentOS 6.5 server. This tutorial is same for other RPM based distros like RHEL/Scientific Linux 6.x and Fedora.

Install PostgreSQL First, add the PostgreSQL repository depending upon your server architecture. For 32bit: rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-6-i386/pgdg-centos93-9.3-1.noarch.rpm For 64bit: rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm For other distros, head over to the following link and install the relevant repository. http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

1/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

PostgreSQL 9.3 RPM Repository Update the repository list using command: yum update Now, Install postgresql with the following command: yum install postgresql93-server postgresql93-contrib Initialize postgresql database using following command: service postgresql-9.3 initdb Then, start postgresql service and make it to start automatically on every reboot. /etc/init.d/postgresql-9.3 start chkconfig postgresql-9.3 on Next, adjust iptables to access postgresql from outbound. vi /etc/sysconfig/iptables Add the following line: -A INPUT -m state --state NEW -m t -p t --dport 5432 -j ACCEPT Save and exit the file. Restart iptables service. service iptables restart

Access PostgreSQL command prompt The default database name and database are “postgres”. Switch to postgres to perform postgresql related operations: su - postgres To to postgresql, enter the command: psql http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

2/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

psql (9.3.4) Type "help" for help. postgres=# To exit from posgresql, type \q. Set “postgres” : to postgresql and set postgres with following command: postgres=# \ postgres Enter new : Enter it again: postgres=# \q To install PostgreSQL pack, enter the command in postgresql prompt: postgres=# CREATE EXTENSION pack; CREATE EXTENSION

Create New and Database For example, here I create a new called “senthil” with “centos” and database called “mydb”. Switch to postgres with command: su - postgres Create senthil. $ create senthil Create database: $ createdb mydb Set and Grant access to the database mydb for senthil: $ psql

http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

3/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

psql (9.3.4) Type "help" for help. postgres=# alter senthil with encrypted 'centos'; ALTER ROLE postgres=# grant all privileges on database mydb to senthil; GRANT postgres=#

Delete s and Databases To delete the database, switch to postgres : su - postgres Enter command: $ dropdb To delete a , enter the following command: $ drop <-name>

Configure PostgreSQL-MD5 Authentication By default, Posgresql uses ident authentication, so that the local system s can be granted access to databases own by them. If you want to set MD5 authentication to require s to enter s. Open up the /var/lib/pgsql/9.3/data/pg_hba.conf file: vi /var/lib/pgsql/9.3/data/pg_hba.conf Add or Modify the lines as shown below [...] # TYPE DATABASE



ADDRESS

METHOD

# "local" is for Unix domain socket connections only local

all

all

md5

# IPv4 local connections: http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

4/9

7/22/2014

host host

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

all all

all all

127.0.0.1/32 192.168.1.0/24

md5 md5

::1/128

md5

# IPv6 local connections: host

all

all

[...] Restart postgresql service to apply the changes: /etc/init.d/postgresql-9.3 restart

Configure PostgreSQL-Configure T/IP By default, T/IP connection is disabled, so that the s from another computers can’t access postgresql.

To

allow

to

connect

s

from

another

computers,

Edit

file/var/lib/pgsql/9.3/data/postgresql.conf: vi /var/lib/pgsql/9.3/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.3 restart

Manage PostgreSQL with phpPg phpPg is a web-based istration utility written in PHP for managing PosgreSQL. phpPg is available only in PostgreSQL RPM repository. If you didn’t add PostgreSQL repository, you should add EPEL repository. http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

5/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

Follow the below link to install EPEL repository on CentOS. Install EPEL repository on CentOS/RHEL/Scientific Linux 6 Update the repository using command: yum update Now, Install phpPg, enter the following command: yum install phpPg httpd phpPg is case sensitive. Use upper and lower cases as shown in the above command. By default, you can access phppg usinghttp://localhost/phpPg from your local system only. To access remotely, do the following. Open up the file /etc/httpd/conf.d/phpPg.conf: vi /etc/httpd/conf.d/phpPg.conf Find the line: deny from all And, change it to look like below. allow from all Start or Restart Apache service: service httpd start chkconfig httpd on

Configure phpPg open the file /etc/phpPg/config.inc.php: vi /etc/phpPg/config.inc.php Find the following line: http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

6/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

$conf['servers'][0]['host'] = ''; Change it as shown below: $conf['servers'][0]['host'] = 'localhost'; And find the line: $conf['extra__security'] = true; Change the value to false: $conf['extra__security'] = false; Find the line: $conf['owned_only'] = false; Set the value as true. $conf['owned_only'] = true; Save and close the file. Restart postgresql service and Apache services. /etc/init.d/postgresql-9.3 restart service httpd restart Now open your browser and navigate to http://ip-address/phpPg. You will be pleased when you see the following screen.

http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

7/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

with s that you’ve created earlier. I already have created a called “senthil” with “centos” before, so I with “senthil”.

http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

8/9

7/22/2014

How To Install PostgreSQL 9.3 And phpPg In CentOS 6.5 | Unixmen

This is how my phpPg dashboard looked.

That’s it. Now you’ll able to perform create, delete and alter databases graphically using phpPg easily. For questions please refer to our Q/A forum at :http://ask.unixmen.com/

http://www.unixmen.com/install-postgresql-9-3-phppg-centos-6-5/

9/9