How to Install PostgreSQL 9.4 And phpPgAdmin in CentOS
Installing PostgreSQL 9.4 And phpPgAdmin In CentOS 7
PostgreSQL is a dynamic, free object-relational database system which supports all the major operating systems. Installation of PostgreSQL 9.4 and phpPgAdmin in CentOS is explained in this article.
To Install PostgreSQL
Add the PostgreSQL repository by running the following command.
[root@linuxhelp1 ~]# rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
Retrieving http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
warning: /var/tmp/rpm-tmp.KkYHl6: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:pgdg-centos94-9.4-1 ################################# [100%]
Utilise the following command to update the repository
[root@linuxhelp1 ~]# yum update
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* extras: mirror.nbrc.ac.in
* updates: mirror.nbrc.ac.in
Resolving Dependencies
--> Running transaction check
---> Package httpd.x86_64 0:2.4.6-40.el7.centos.1 will be updated
---> Package httpd.x86_64 0:2.4.6-40.el7.centos.4 will be an update
.
.
.
Updated:
httpd.x86_64 0:2.4.6-40.el7.centos.4 httpd-tools.x86_64 0:2.4.6-40.el7.centos.4 pgdg-centos94.noarch 0:9.4-2
Complete!
Install postgresql with the following command
[root@linuxhelp1 ~]# yum install postgresql94-server postgresql-contrib -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* extras: mirror.nbrc.ac.in
* updates: mirror.nbrc.ac.in
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.x86_64 0:9.2.15-1.el7_2 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.15-1.el7_2 for package: postgresql-contrib-9.2.15-1.el7_2.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.15-1.el7_2 for package: postgresql-contrib-9.2.15-1.el7_2.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.15-1.el7_2.x86_64
.
.
.
Installed:
postgresql-contrib.x86_64 0:9.2.15-1.el7_2 postgresql94-server.x86_64 0:9.4.8-1PGDG.rhel7
Dependency Installed:
postgresql.x86_64 0:9.2.15-1.el7_2 postgresql-libs.x86_64 0:9.2.15-1.el7_2 postgresql94.x86_64 0:9.4.8-1PGDG.rhel7
postgresql94-libs.x86_64 0:9.4.8-1PGDG.rhel7 uuid.x86_64 0:1.6.2-26.el7
Complete!
Create the postgresql database by running the following command.
[root@linuxhelp1 ~]# /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK
Start postgresql service to start automatically on every reboot.
[root@linuxhelp1 ~]# systemctl enable postgresql-9.4.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.4.service to /usr/lib/systemd/system/postgresql-9.4.service.
[root@linuxhelp1 ~]# systemctl start postgresql-9.4.service
Allow some ports in IP tables as follows to access postgresql from remote systems.
[root@linuxhelp1 ~]# firewall-cmd --permanent --add-port=5432/tcp success [root@linuxhelp1 ~]# firewall-cmd --permanent --add-port=80/tcp success [root@linuxhelp1 ~]# firewall-cmd --reload Success
Adjust SELinux by execute the following command to make PostgreSQL work.
[root@linuxhelp1 ~]# setsebool -P httpd_can_network_connect_db 1
Login of PostegreSQL fails, if you didn’ t run the above command.
Use the following command to Switch the postgres user in order to perform postgresql related operations.
[root@linuxhelp1 ~]# su &ndash postgres
To login to postgresql, enter the command:
-bash-4.2$ psql
psql (9.2.15, server 9.4.8)
WARNING: psql version 9.2, server version 9.4.
Some psql features might not work.
Type " help" for help.
Set “ postgres” user password
postgres=# password postgres
Enter new password:
Enter it again:
Create New User and Database
For example, let us create a new user called “ linux” with password “ linuxc” , and database called “ database1” .
Now create user linux
postgres=# create user linux
CREATE ROLE
Create database:
postgres=# create database database1
CREATE DATABASE
set password and Grant access to the database database1 for linux:
postgres=# alter user linux with encrypted password ' linuxc'
ALTER ROLE
postgres=# grant all privileges on database database1 to linux
GRANT
Delete Users and Databases
To delete the database, switch to postgres user:
su - postgres
Enter command:
$ dropdb
To delete a user, enter the following command:
$ dropuser
To Configure PostgreSQL-MD5 Authentication
Open pg_hba.conf file and edit as follows.
[root@linuxhelp1 ~]# vim /var/lib/pgsql/9.4/data/pg_hba.conf
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all 192.168.5.0/24 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
host all all ::1/128 md5
Unfold the postgresql.conf file and edit the following lines to connect users from another computers.
[root@linuxhelp1 ~]# vim /var/lib/pgsql/9.4/data/postgresql.conf
listen_addresses = ' localhost'
port = 5432
max_connections = 100
Restart the services of postgresql to apply changes
[root@linuxhelp1 ~]# systemctl restart postgresql-9.4
To Manage PostgreSQL with phpPgAdmin
phpPgAdmin is a web-based administration utility that is programmed in PHP language. Add EPEL repository, before installing phpPgAdmin.
[root@linuxhelp1 ~]# yum install epel-release -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* extras: mirror.nbrc.ac.in
* updates: mirror.nbrc.ac.in
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-6 will be installed
--> Finished Dependency Resolution
.
.
.
Installed:
epel-release.noarch 0:7-6
Complete!
Run the following command to install phpPgAdmin.
[root@linuxhelp1 ~]# yum install phpPgAdmin -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* epel: mirror.rise.ph
* extras: mirror.nbrc.ac.in
* updates: mirror.nbrc.ac.in
Resolving Dependencies
--> Running transaction check
---> Package phpPgAdmin.noarch 0:5.1-2.rhel7 will be installed
--> Processing Dependency: php-pgsql > = 4.2 for package: phpPgAdmin-5.1-2.rhel7.noarch
--> Running transaction check
---> Package php-pgsql.x86_64 0:5.4.16-36.1.el7_2.1 will be installed
.
.
.
Installed:
phpPgAdmin.noarch 0:5.1-2.rhel7
Dependency Installed:
php-pgsql.x86_64 0:5.4.16-36.1.el7_2.1
Complete!
Open the phpPgAdmin.conf file and edit it as follows.
[root@linuxhelp1 ~]# vim /etc/httpd/conf.d/phpPgAdmin.conf
Alias /phpPgAdmin /usr/share/phpPgAdmin
# Apache 2.4
Require all granted
#Require host example.com
# Apache 2.2
Order deny,allow
Deny from all
Allow from 127.0.0.1
Allow from all
# Allow from .example.com
Start and enable the Apache services.
[root@linuxhelp1 ~]# systemctl enable httpd [root@linuxhelp1 ~]# systemctl start httpd
To Configure phpPgAdmin
Open the config.inc.php and make the following changes.
[root@linuxhelp1 ~]# vim /etc/phpPgAdmin/config.inc.php // use ' localhost' for TCP/IP connection on this computer $conf[' servers' ][0][' host' ] = ' localhost' // understand how to change PostgreSQL' s pg_hba.conf to enable // passworded local connections. $conf[' extra_login_security' ] = false // not in any way prevent your users from seeing other database by // other means. (e.g. Run ' SELECT * FROM pg_database' in the SQL area.) $conf[' owned_only' ] = true
Utilise the following command to restart the postgresql and apache services.
[root@linuxhelp1 ~]# systemctl restart postgresql-9.4.service [root@linuxhelp1 ~]# systemctl restart httpd
Open the browser and navigate to http://< IP_address> /phpPgAdmin
Login with user credentials.
If any error occurs while login, then run the following command.
[root@linuxhelp1 ~]# setsebool -P httpd_can_network_connect_db 1
phpPgAdmin dashboard appears.
Q
What happened to the database dump feature in phpPgAdmin?
A
You need to configure phpPgAdmin (in the config.inc.php file) to point to the location of the pg_dump and pg_dumpall utilities on your server. Once you have done that, the database export feature will appear.
Q
Why phpPgAdmin ask me to re-enter login information for every page I access?
A
You most certainly have a problem with your PHP sessions. Make sure:
your browser accept cookies if your session ID relies on them
PHP has write access to the path set in the parameter “session.save_path” of your php.ini
your browser accept cookies if your session ID relies on them
PHP has write access to the path set in the parameter “session.save_path” of your php.ini
Q
How do I change the sort ordering of textual data in PostgreSQL?
A
PostgreSQL sorts textual data according to the ordering that is defined by the current locale, which is selected during initdb.
Q
How does PostgreSQL use CPU resources?
A
The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS.
Q
How do I control connections from other hosts?
A
, PostgreSQL only allows connections from the local machine using Unix domain sockets or TCP/IP connections. Other machines will not be able to connect unless you modify listen_addresses in the postgresql.conf file, enable host-based authentication by modifying the $PGDATA/pg_hba.conf file, and restart.