PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) released under a flexible BSD-style license. PostgreSQL is well suited for large databases and has many advanced features.
PostgreSQL is available for many operating systems including Linux, FreeBSD, Solaris, and Microsoft Windows. PhpPgAdmin is a PHP-based web application for managing PostgreSQL databases. With Phppgadmin, it is easy to create a database, create a role and create tables in Postgres.
This tutorial will show the installation of PostgreSQL and its web-based administration interface phpPgAdmin on Ubuntu 18.04 LTS (Bionic Beaver). I will use the Ubuntu minimal server as a basis for this setup.
- Ubuntu 18.04
- Root privileges
What we will do?
- Install PostgreSQL and phpPgAdmin
- Configure Postgres User
- Configure Apache2
- Configure phpPgAdmin
Step 1 – Install PostgreSQL, phpPgAdmin and All Dependencies
Before installing any packages on the Ubuntu system, update all available repositories.
sudo apt update
And now we’re ready for installing PostgreSQL, phpPgAdmin, and Apache2 packages.
PostgreSQL and PhpPgAdmin are available in the Ubuntu repository. So you just need to install them with the apt command.
sudo apt -y install postgresql postgresql-contrib phppgadmin
The above command will automatically install all packages needed by PostgreSQL and phpPgAdmin, like Apache2, PHP etc.
Step 2 – Configure Postgres User
PostgreSQL uses role for user authentication and authorization, it just like Unix-Style permissions. By default, PostgreSQL creates a new user called “postgres” for basic authentication. To use PostgreSQL, you need to login to the “postgres” account, you can do that by typing:
su - postgres
Now you can access the PostgreSQL prompt with the command:
And then change the password for postgres role by typing:
\password postgres TYPE THE POSTGRES PASSWORD
Then enter \q to leave the psql command line.
Run the command “exit” to leave the postgres user and become root again.
Step 3 – Configure Apache Web Server
You need to configure Apache virtual host configuration for phpPgAdmin.
Goto the ‘/etc/apache2/conf-available’ directory and edit the configuration file ‘phppgadmin.conf’ with vim by typing:
cd /etc/apache2/conf-available/ vim phppgadmin.conf
Comment out the line ‘#Require local’ by adding a # in front of the line and add below the line allow from all so that you can access from your browser.
Require all granted
Save and exit.
Step 4 – Configure phpPgAdmin
Go to the ‘/etc/phppgadmin’ directory and edit the configuration file ‘config.inc.php’ by typing :
cd /etc/phppgadmin/ vim config.inc.php
Find the line ‘$conf[‘extra_login_security’] = true;’ and change the value to ‘false’ so you can login to phpPgAdmin with user postgres.
$conf['extra_login_security'] = false;
Save and exit.
Now restart the PostgreSQL and Apache2 services.
systemctl restart postgresql systemctl restart apache2
Step 5 – Testing Postgres
By default, PostgreSQL is running on port ‘5432’, and the Apache2 running on the default HTTP port ’80’.
Check using netstat command.
Now access phpPgAdmin with your browser http://yourip/phppgadmin/.
and then try login to with user ‘postgres’ and your password.
After logging in, you will get this phpPgAdmin dashboard interface:
Installation of the PostgreSQL database with phpPgAdmin on Ubuntu 18.04 LTS has been completed successfully.
PostgreSQL is an advanced object-relational database management system (ORDBMS). It is Open Source and has a large and active community. PostgreSQL provides the psql command line program as primary front-end, which can be used to enter SQL queries directly or execute them from a file. phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP that makes the administration of Postgres databases easier.