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

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 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

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

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.

Share this page:

Suggested articles

9 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?

By: Jason at: 2018-10-07 00:54:20

Same issue...

 

sudo apt-get install phppgadmin

Reading package lists... Done

Building dependency tree

Reading state information... Done

E: Unable to locate package phppgadmin

By: till at: 2018-10-07 09:02:03

The package is definitely there, just installed it on Ubuntu 18.04. Probably you don't have all Ubuntu repositories (restricted + universe + multiverse) enabled. The /etc/apt/sources.list file on your server should contain this:

 

# deb cdrom:[Ubuntu-Server 18.04 LTS _Bionic Beaver_ - Release amd64 (20180425.1)]/ bionic main restricted

 

#deb cdrom:[Ubuntu-Server 18.04 LTS _Bionic Beaver_ - Release amd64 (20180425.1)]/ bionic main restricted

 

# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to

# newer versions of the distribution.

deb http://de.archive.ubuntu.com/ubuntu/ bionic main restricted

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic main restricted

 

## Major bug fix updates produced after the final release of the

## distribution.

deb http://de.archive.ubuntu.com/ubuntu/ bionic-updates main restricted

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic-updates main restricted

 

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu

## team. Also, please note that software in universe WILL NOT receive any

## review or updates from the Ubuntu security team.

deb http://de.archive.ubuntu.com/ubuntu/ bionic universe

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic universe

deb http://de.archive.ubuntu.com/ubuntu/ bionic-updates universe

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic-updates universe

 

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu

## team, and may not be under a free licence. Please satisfy yourself as to

## your rights to use the software. Also, please note that software in

## multiverse WILL NOT receive any review or updates from the Ubuntu

## security team.

deb http://de.archive.ubuntu.com/ubuntu/ bionic multiverse

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic multiverse

deb http://de.archive.ubuntu.com/ubuntu/ bionic-updates multiverse

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic-updates multiverse

 

## N.B. software from this repository may not have been tested as

## extensively as that contained in the main release, although it includes

## newer versions of some applications which may provide useful features.

## Also, please note that software in backports WILL NOT receive any review

## or updates from the Ubuntu security team.

deb http://de.archive.ubuntu.com/ubuntu/ bionic-backports main restricted universe multiverse

# deb-src http://de.archive.ubuntu.com/ubuntu/ bionic-backports main restricted universe multiverse

 

## Uncomment the following two lines to add software from Canonical's

## 'partner' repository.

## This software is not part of Ubuntu, but is offered by Canonical and the

## respective vendors as a service to Ubuntu users.

# deb http://archive.canonical.com/ubuntu bionic partner

# deb-src http://archive.canonical.com/ubuntu bionic partner

 

deb http://security.ubuntu.com/ubuntu bionic-security main restricted

# deb-src http://security.ubuntu.com/ubuntu bionic-security main restricted

deb http://security.ubuntu.com/ubuntu bionic-security universe

# deb-src http://security.ubuntu.com/ubuntu bionic-security universe

deb http://security.ubuntu.com/ubuntu bionic-security multiverse

# deb-src http://security.ubuntu.com/ubuntu bionic-security multiverse

By: Jens at: 2018-11-12 10:19:26

Thanks for this. There is a typo in line

systemctl restart posqtgresql

By: till at: 2018-11-12 10:33:55

Thanks! Fixed the typo.