How to Install PostgreSQL and phpPgAdmin on Ubuntu 18.04

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

Set a PostgreSQL password

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.

Configure Apache Web server

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.

Configure phpPgAdmin

Now restart the PostgreSQL and Apache2 services.

systemctl restart posqtgresql
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

Check postgres network

Now access phpPgAdmin with your browser http://yourip/phppgadmin/.

Login to phpPgAdmin

and then try login to with user 'postgres' and your password.

Login with postgres user to phpPgAdmin

After logging in, you will get this phpPgAdmin dashboard interface:

phpPgAdmin dashboard interface

The installation of PostgreSQL database with phpPgAdmin on ubuntu 18.04 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.

About Muhammad Arul

Muhammad Arul is a freelance system administrator and technical writer. He is working with Linux Environments for more than 5 years, an Open Source enthusiast and highly motivated on Linux installation and troubleshooting. Mostly working with RedHat/CentOS Linux and Ubuntu/Debian, Nginx and Apache web server, Proxmox, Zimbra Administration, and Website Optimization. Currently learning about OpenStack and Container Technology.

Share this page:

Suggested articles

5 Comment(s)

Add comment

Comments

By: Chell0h at: 2018-07-21 14:49:27

Hi,

Kindly correct the spelling of this line "systemctl restart posqtgresql" to read "systemctl restart postgresql" so that the code can work well.

By: Gabe at: 2018-07-21 14:56:33

Thanks, it worked on Kubuntu 18.04.

By: Agbonze Prince at: 2018-08-29 16:22:25

Thanks for this. Works like charm.. I truly appreciate

By: FiddleWalker at: 2018-09-03 23:01:49

Thank you, Muhammad Arul. Fantastically smooth-working implementation description!

By: akiragila at: 2018-09-09 08:57:22

i encountered this error: "unable to locate phppgadmin", when trying to install phppgadmin. Can you help me on this?