Linux

How to Install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS

How to Install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS

PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) that is released under a flexible BSD style license. PostgreSQL is perfect for large databases and has many powerful 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’s easy to create databases, create roles, and create tables in Postgres.

This tutorial will show the PostgreSQL installation and phpPgAdmin web-based administration interface on Ubuntu 20.04 LTS (Bionic Beaver). I will use a minimum Ubuntu server as a base for this setting.

Precondition

  • Ubuntu 20.04
  • Rooting privileges

What we will do?

  • Install PostgreSQL and phpPgAdmin
  • Create New PostgreSQL Users
  • Configure Apache2
  • UFW Firewall Settings
  • Testing

Step 1 – Install PostgreSQL, phpPgAdmin and All Dependencies

Before installing any package on an Ubuntu system, update and update all packages using the apt command below.

sudo apt update
sudo apt upgrade

And now we are ready to install the PostgreSQL, phpPgAdmin, and Apache2 packages.

PostgreSQL and PhpPgAdmin are available in the Ubuntu repository. So, you only need to install it with the apt command.

sudo apt -y install postgresql postgresql-contrib phppgadmin

After all installation is complete, start the PostgreSQL service and add it to the system boot.

systemctl start postgresql
systemctl enable postgresql

Next, check the PostgreSQL service using the following command.

systemctl status postgresql

As a result, the PostgreSQL service is up and running.

1

And all PostgreSQL and phpPgAdmin installation packages are complete.

Step 2 – Create a New PostgreSQL User

PostgreSQL uses roles for user authentication and authorization, just like Unix-Style permissions. By default, PostgreSQL creates a new user called “postgres” for basic authentication.

In this step, we will create a new PostgreSQL user who has privileges as a superuser, create a database, create roles, and log in. New users will be used to log in to PostgreSQL through ‘phpPgAdmin’, and to do that, we must enter the PostgreSQL shell as the default ‘postgres’ user.

Log in to the PostgreSQL shell using the command below.

sudo -i -u postgres psql

Next, create a new ‘hakase’ role with the password ‘hakasepasspgsql’ using the following request.

CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'hakasepasspgsql';

Now check all the users available on the PostgreSQL system.

\du

And you will get a new user ‘hakase’ in the list, now type ‘\ q’ to exit the PostgreSQL shell.

2

As a result, a new user named ‘hakase’ has been created, and it will be able to enter the PostgreSQL server via phpPgAdmin.

Step 3 – Configure Apache Web Server

In this step, we will configure the Apache configuration for phpPgAdmin, which is automatically generated during package installation.

Enter the ‘/ etc / apache2 / conf-available’ directory and edit the ‘phppgadmin.conf’ configuration file with vim by typing:

cd /etc/apache2/conf-available/
vim phppgadmin.conf

By default, phpPgAdmin can be accessed via the ‘phppgadmin’ URL path. For security reasons, we will change the default phppgadmin path URL by changing the ‘Alias’ option.

Change the default path URL ‘phppgadmin’ with your own path as below.

Alias /pgsqladminlogin /usr/share/phppgadmin

There are no comments outside the ‘#Local local’ line by adding # in front of the line and adding below the line that allows it from all so you can access it from your browser.

Require all granted

Save and close.

Next, test the Apache configuration and make sure there are no errors, then restart the Apache service.

apachectl configtest
systemctl restart apache2

As a result, the Apache configuration for phpPgAdmin has been completed.

Step 4: Setup UFW Firewall

For this guide, we will run PostgreSQL and Apache services under the UFW firewall.

Add ssh, http, and https ports to the ufw firewall using the following command.

for svc in ssh http https
do
ufw allow $svc
done

Next, start and activate the UFW firewall.

ufw enable

And you will be asked to ensure that you want to run the UFW firewall service. Type ‘y’ and press Enter to start the firewall.

3

And the UFW firewall is already active with the SSH protocol, http, and https on it.

Check the UFW firewall status using the command below.

ufw status numbered

Below are the results that you will get.
4

Step 5 – Testing

Before proceeding further, check PostgreSQL and Apache port services using the ss command below.

ss -plnt

And you will get PostgreSQL port ‘5432’ and Apache port ’80’ in LISTEN status, both services are already running and running.

5

Next, open your web browser and type the following server IP address with your custom phpPgAdmin path.

http://10.5.5.32/pgsqladminlogin/

And you will get the default phpPgAdmin page as below.

6

Now click on the ‘Server‘ tab and you will get the phpPgAdmin login page.

7

Type the PostgreSQL user and password created above and click the ‘Login‘ button.

And you will get this phpPgAdmin dashboard interface.

8

The PostgreSQL database installation with phpPgAdmin on Ubuntu 20.04 LTS has been completed successfully.

Conclusion

PostgreSQL is a sophisticated 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 the main front-end, which can be used to enter SQL queries directly or run them from a file. phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP that makes Postgres database administration easier.

Related posts

How to install Zulip chat server on Ubuntu 18.04

Linux

How to Install TeamViewer on Ubuntu 20.04

Linux

How to Install TensorFlow on CentOS

Linux

How to Make a Minecraft Server on Ubuntu 20.04

Linux

How to Install VirtualBox Guest Additions on Ubuntu 20.04

Linux

How to Install Android Studio on Ubuntu 20.04

Linux

How to Install DEB packages on Ubuntu & Linux Mint

Linux

How to Make a Tar Gz File

Linux

How to Install Xrdp Server (Remote Desktop) on Ubuntu 20.04

Linux