How to Install PostgreSQL and phpPgAdmin on Ubuntu 18.04 LTS

How to Install PostgreSQL and phpPgAdmin on Ubuntu 18.04 LTS

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.

Prerequisites

• Ubuntu 18.04
• Root privileges

What we will do?

1. Install PostgreSQL and phpPgAdmin
2. Configure Postgres User
3. Configure Apache2
4. Configure phpPgAdmin
5. Testing

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:

psql

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.

\q

Run the command "exit" to leave the postgres user and become root again.

exit

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.

netstat -plntu

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.

Conclusion

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.