How to Install PostgreSQL and phpPgAdmin on Ubuntu 20.04

PostgreSQL is a free, open-source and one of the most advanced object-relational database management system. It is specially designed for mission-critical applications. Managing the PostgreSQL database from command-line is very difficult for any beginner user. PhpPgAdmin is a web-based database management interface for PostgreSQL. It provides an easy way to manage the PostgreSQL through the web interface.

In this tutorial, we will show you how to install PostgreSQL and PhpPgAdmin on Ubuntu 20.04 server.

Requirements

• A server running Ubuntu 20.04.
• A root password set up on your server.

Update the System

First, it is recommended to update your system's packages repository to the latest version. You can update them with the following command:

apt-get update -y

Once all the packages are updated, you can proceed to install PostgreSQL. 

Install PostgreSQL Server

By default, PostgreSQL server is available in the Ubuntu 20.04 default repository. You can install it by running the following command:

apt-get install postgresql -y 

After installing PostgreSQL, start the PostgreSQL service and enable it to start after system reboot.

systemctl start postgresql
systemctl enable postgresql

Next, verify the PostgreSQL status with the following command:

systemctl status postgresql

You should get the following output: 

  ● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2020-09-16 07:03:14 UTC; 33s ago
   Main PID: 2287 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4691)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Sep 16 07:03:14 ubuntu2004 systemd[1]: Starting PostgreSQL RDBMS...
Sep 16 07:03:14 ubuntu2004 systemd[1]: Finished PostgreSQL RDBMS.

Create a Database for PhpPgAdmin

Next, you will need to create a database and user for PhpPgAdmin.

First, log into the PostgreSQL with the following command:

su - postgres
psql

Once login, you should see the following output:

psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

Next, create a database and user with the following command:

postgres=# CREATE USER pgadmin WITH PASSWORD 'password';
postgres=# CREATE DATABASE pgadmindb;

Next, grant all the privileges to the pgadmindb with the following command:

postgres=# GRANT ALL PRIVILEGES ON DATABASE pgadmindb to pgadmin;

Next, exit from the PostgreSQL shell with the following command:

postgres=# \q
exit

Install PhpPgAdmin

By default, PhpPgAdmin is available in the Ubuntu 20.04 default repository. You can install it with the following command: 

apt-get install phppgadmin -y

By default, PhpPgAdmin is accessible only from the localhost. So you will need to configure it for external access. You can do it by editing the /etc/apache2/conf-available/phppgadmin.conf configuration file:

nano /etc/apache2/conf-available/phppgadmin.conf

Find the following line:

Require local 

And, replace it with the following line: 

Require all granted 

Save and close the file then check Apache for any configuration error with the following command:

apachectl configtest

You should get the following output:

Syntax OK

Next, restart the Apache service to apply the changes:

systemctl restart apache2

Access PhpPgAdmin

Now, open your web browser and access the PhpPgAdmin web interface using the URL http://your-server-ip/phppgadmin/. You should see the following screen:

Now, click on the Server => PostgreSQL. You should see the PhpPgAdmin login screen:

Provide your PhpPgAdmin username, password and click on the Login button. You should see the PhpPgAdmin dashboard in the following screen:

From here, you can run any query for PostgreSQL database server.

Conclusion 

Congratulations! you have successfully installed PostgreSQL and PhpPgAdmin on Ubuntu 20.04 server. You can now easily manage your PostgreSQL database from the web-based interface.