Hi everybody! Our today's article is on PostgreSQL with phpPgAdmin installation setup on CentOS 7. PostgreSQL is one of the major and important open-source relational database management systems that have helped to shape the world of application development with advanced, SQL-compliant. The main advantage of using PostgreSQL is that it requires very minimum maintenance efforts because of its stability and the applications based on PostgreSQL have low cost of ownership in comparison with other database management systems. Its designed to be extensible in a way that you can define your own data types, index types, functional languages, etc.
Managing databases using individual SQL statements is a difficult task , so in this article we will also show you one of the best and most popular graphical user interface for managing a PostgreSQL database, that is phpPgAdmin.
PhpPgAdmin is a web-based GUI application that makes it simple for administering your PostgreSQL databases. phpPgAdmin will let you add, remove and manage databases, tables, and entries; run specific SQL queries, backup the database, search and import record, and much more.
Before starting the installation of PotgreSQL and phpPgAdmin make sure that you have root access on your CentOS server and your are connected to the internet for downloading the packages.
After login to your server, run the command below to update your centos 7 server with the latest patches.
# yum update
If you going to set up PostgreSQL and phpPgAdmin on a production environment with firewall and SELinux enabled, then make sure to allow the following default ports that will be used for postgreSQL and apache.
# firewall-cmd --permanent --add-port=5432/tcp # firewall-cmd --permanent --add-port=80/tcp # firewall-cmd --reload
To allow in SELinux run the below command.
# setsebool -P httpd_can_network_connect_db 1
By default Centos 7 comes with PostgreSQL Version 9.2.1 that can be installed by using the simple yum command while the current latest PostgreSQL Version is 9.4.5. So, in this tutorial, we will be installing the latest version of PostgreSQL by using the PostgreSQL Yum Repository.
Installing PostgreSQL Repository
To get the latest yum repository for the latest PostgreSQL package open the PostgreSQL Download Page or copy the link and run the below wget command.
# wget http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
After downloading the rpm repository we have to install this rpm repository first before starting the PostgreSQL installation by using the below command.
# rpm -i pgdg-redhat94-9.4-1.noarch.rpm # yum install postgresql94-server postgresql94-contrib
After running the above command there will be number of the following package will be installed including few dependencies. To proceed the installation process press the "Y" key to continue as shown.
Dependencies Resolved ======================================================================================== Package Arch Version Repository Size ======================================================================================== Installing: postgresql94-contrib x86_64 9.4.5-1PGDG.rhel7 pgdg94 610 k postgresql94-server x86_64 9.4.5-1PGDG.rhel7 pgdg94 3.8 M Installing for dependencies: libxslt x86_64 1.1.28-5.el7 base 242 k postgresql94 x86_64 9.4.5-1PGDG.rhel7 pgdg94 1.0 M postgresql94-libs x86_64 9.4.5-1PGDG.rhel7 pgdg94 209 k Transaction Summary ======================================================================================= Install 2 Packages (+3 Dependent packages) Total download size: 5.9 M Installed size: 25 M Is this ok [y/d/N]: y
Once the installation is complete, run the below command to initialize the database.
# /usr/pgsql-9.4/bin/postgresql94-setup initdb Initializing database ... OK
Starting Database Service
To start the PostgreSQL service and to configure it for auto-enable at boot up run the following commands and then check the status, it should be up and enabled.
# systemctl start postgresql-9.4 # systemctl enable postgresql-9.4
Using PostgreSQL Command line
During the installation process, a new user was created by default with name "postgres" that will be used for administering PostgreSQL databases.
Let's switch user to the PostgreSQL user and connect to the PostgreSQL command-line interface for managing your database.
# su - postgres -bash-4.2$ psql psql (9.4.5) Type "help" for help.
you can get more help on using the PostgreSQL database by typing help command as shown in the image.
Run the following command to update the default password of postgres user.
postgres=# \password postgres Enter new password:***** Enter it again:*****
Now we will create a new user and database using the PostgreSQL command line. To do let's run the below commands.
[root@centos-7 ~]# su - postgres Last login: Sat Oct 10 19:26:10 BST 2015 on pts/1 -bash-4.2$ createuser kashif -bash-4.2$ createdb testdb -bash-4.2$ psql postgres=# alter user kashif with encrypted password 'kashif123'; ALTER ROLE postgres=# grant all privileges on database testdb to kashif; GRANT
To list all the databases created on your system use the "\list" or "\l" command and to connect to a database use "\c db_name" as shown below.
In this section, we are now going to set up a Web-based PostgreSQL administration tool. To do so first we have to install its packages that can done by using the below yum command.
# yum install phpPgAdmin httpd
After running this command you will see a number of dependencies that will be required for installing the phpPgadmin and apache web server. So, to proceed forward choose the "Y" key to accept the changes and to complete the installation setup.
Dependencies Resolved ======================================================================================= Package Arch Version Repository Size ======================================================================================= Installing: httpd x86_64 2.4.6-31.el7.centos.1 updates 2.7 M phpPgAdmin noarch 5.1-2.rhel7 pgdg94 658 k Installing for dependencies: apr x86_64 1.4.8-3.el7 base 103 k apr-util x86_64 1.5.2-6.el7 base 92 k httpd-tools x86_64 2.4.6-31.el7.centos.1 updates 79 k libzip x86_64 0.10.1-8.el7 base 48 k mailcap noarch 2.1.41-2.el7 base 31 k php x86_64 5.4.16-36.el7_1 updates 1.4 M php-cli x86_64 5.4.16-36.el7_1 updates 2.7 M php-common x86_64 5.4.16-36.el7_1 updates 563 k php-pdo x86_64 5.4.16-36.el7_1 updates 97 k php-pgsql x86_64 5.4.16-36.el7_1 updates 84 k Transaction Summary ======================================================================================= Install 2 Packages (+10 Dependent packages) Total download size: 8.5 M Installed size: 30 M Is this ok [y/d/N]:y
After installing the required packages, we will configure the phpPgAdmin with required parameters to allow access from the remote location as by default it will be only accessible through localhost.
# vim /etc/httpd/conf.d/phpPgAdmin.conf
Now Open the below configuration file using any editor and read it carefully before making any changes to it. Most of the parameters and this file are well explained and configured, but we only need to update some the following parameters.
# vim /var/lib/pgsql/9.4/data/pg_hba.conf
# vim /var/lib/pgsql/9.4/data/postgresql.conf
# vim /etc/phpPgAdmin/config.inc.php
// Hostname or IP address for server. Use '' for UNIX domain socket. // use 'localhost' for TCP/IP connection on this computer $conf['servers']['host'] = 'localhost'; // Database port on server (5432 is the PostgreSQL default) $conf['servers']['port'] = 5432; $conf['owned_only'] = true;
Save the changes and then restart both the services of PostgreSQL and Apache.
# systemctl restart postgresql-9.4 # systemctl restart httpd
phpPgAdmin Web Console
Let's open the below URL to access the phpPgAdmin console as shown below.
To login into the PostgreSQL simply click on the top left icon as shown and provide your credentials as created earlier.
Upon successful login, you will get access to create and manage your databases from phpPgAdmin console.
At the end of this article, you learned about the installation and configuration of PostgreSQL with phpPgAdmin on CentOS 7. Still, this was the first step in the world of PostgreSQL as there are a lot of features upon them you have to work on as it has a lot of awesome features like point in time recovery, tablespaces, asynchronous replication, Multi-Version Concurrency Control (MVCC), and write-ahead logging for fault tolerance. So, we hope you find this article much helpful for you to start database administration with PostgreSQL.